Hi,

I have one doubt. Is the foreign key column is really physical data?

The data in both tables is physical, the link between them is an object is SysObjects that makes sure that all the data in both table is valid (as long as the FK is always enabled... or revalidated when turned off).



The proof I could offer is this in my short amount of time : SELECT id, Name FROM dbo.SysObjects WHERE XType = 'F' ORDER BY Name



You can also check out the information_schema views for more options.

__________________
S.VinothkumaR
Behind me is infinite power,
Before me is Endless Possibility,
Around me is Boundless Opportunity,
Why should I fear!

I don't think so... foreign keys don't automatically make indexes like Primary Keys do. They make excellent candidates for indexes, though.

The foreign key constraints do NOT automatically create indexed. And they are more than excellent candidates to apply for indexes.

__________________
S.VinothkumaR
Behind me is infinite power,
Before me is Endless Possibility,
Around me is Boundless Opportunity,
Why should I fear!

For a column that is not physical, which is a computed column, a foreign key constraint cannot be created. Here is a test case:
Create Table Foo
(FooPkvarchar(255) not null
, primary key (FooPk)
)
go
create table Fum
(FumC1varchar(255) not null
,FumC2 varchar(255) not null
,FumC3 as ( FumC1 + '/' + FumC2)
)
alter table Fum
add constraint Foo_F_Fum foreign key (FumC3) references Foo

For SQL Server 2000, the error is 1769
Foreign key 'Foo_F_Fum' references invalid column 'FumC3' in referencing table 'Fum'.

For SQL Server 2005, the error is 1764
Computed Column 'FumC3' in table 'Fum' is invalid for use in 'FOREIGN KEY CONSTRAINT' because it is not persisted.

Creating an index on the computed column still results in the error on the attempted creation of a foreign key constraint.

__________________
thanx n regards
jeyaprakash.c

That's exactly why it's so fun to have 450+ K members.

__________________
S.VinothkumaR
Behind me is infinite power,
Before me is Endless Possibility,
Around me is Boundless Opportunity,
Why should I fear!

Just additional info... Foreign Keys must reference either a PK or a column with a UNIQUE index... that not-with-standing, you still can't reference a computed column as a Foreign Key...

CREATE TABLE Foo
(
FooPk VARCHAR(255) NOT NULL,
FumFum VARCHAR(512),
PRIMARY KEY (FooPk)
)
GO
CREATE TABLE Fum
(
FumC1 VARCHAR(255) NOT NULL,
FumC2 VARCHAR(255) NOT NULL,
FumC3 AS CAST(( FumC1 + '/' + FumC2) AS VARCHAR(512))
)
GO
CREATE UNIQUE INDEX IX_Fum_FumC3 ON Fum (FumC3)
GO
ALTER TABLE dbo.Foo
ADD CONSTRAINT FK_Foo_Fum
FOREIGN KEY (FumFum) REFERENCES dbo.Fum (FumC3)
GO

Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'Foo', because it does not exist in the system catalog.
Server: Msg 1784, Level 16, State 1, Line 1
Cannot create the foreign key 'FK_Foo_Fum' because the referenced column 'Fum.FumC3' is a computed column.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.