is Foreign key 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.
-
I don't think so... foreign keys don't automatically make indexes like Primary Keys do. They make excellent candidates for indexes, though.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
I think the question is in regard of wether the actual column of data is physical data... so that is obviously a yes. As for the index you are right. The foreign key constraints do NOT automatically create indexed. And they are more than excellent candidates to apply for indexes
. -
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.
SQL = Scarcely Qualifies as a Language
-
That's exactly why it's so fun to have 450+ K members. Thanks Carl for the assist.
-
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.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply