Posted by Erland Sommarskog on 11/22/07 22:41

Fritz Franz (fritzfranz24@hotmail.com) writes:
> I have a table A with fields id,startdate and other fields. id and
> startdate are in the primary key.
> In the table B I want to introduce a Foreign key to field id of table A.
>
> Is this possible? If yes, which kind of key I have to build in table A?

If I understand this correctly, you have this:

CREATE TABLE A(id int NOT NULL,
startdate datetime NOT NULL,
otherfield varchar(89) NULL,
PRIMARY KEY (id, startdate))

Now you want to create a table B with a column id, and you want to add a
check that the values in B.id corresponds to a value of id that also in in
A. You cannot do this with DRI (Declarative Referential Integrity), but
you would have to use a trigger. A foreign key must refer to all columns
of the primary key in the other table; it cannot be a partial key.

I don't know about your data model, but I would hold it as likely that
either you have an incorrect design, or you have a misconception of what
you want to do. I have ran into the situation that I wanted a partial
FK myself, but it's a very rare scenario.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Upsize from mdb to SQL Express 2005
• Prev in forum: Re: Finding the user who do update in Query Analyser?
• Thread view: Re: foreign keys - on which kind of keys do the base on?

[Reply to this message]