Foreign Key to non-primary key

Question

I have a table which holds data  and one of those rows needs to exist in another table  So  I want a foreign key to maintain referential integrity   CREATE TABLE table1      ID INT NOT NULL IDENTITY 1 1  PRIMARY KEY     AnotherID INT NOT NULL     SomeData VARCHAR 100  NOT NULL    CREATE TABLE table2      ID INT NOT NULL IDENTITY 1 1  PRIMARY KEY     AnotherID INT NOT NULL     MoreData VARCHAR 30  NOT NULL      CONSTRAINT fk table2 table1 FOREIGN KEY  AnotherID  REFERENCES table1  AnotherID      However  as you can see  the table I foreign key to  the column isn t the PK  Is there a way to create this foreign key  or maybe a better way to maintain this referential integrity

User · Answer

Primary keys always need to be unique  foreign keys need to allow non-unique values if the table is a one-to-many relationship  It is perfectly fine to use a foreign key as the primary key if the table is connected by a one-to-one relationship  not a one-to-many relationship   A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table  it can also be defined to reference the columns of a UNIQUE constraint in another table

User · Answer

Necromancing   I assume when somebody lands here  he needs a foreign key to column in a table that contains non-unique keys   The problem is  that if you have that problem  the database-schema is denormalized    You re for example keeping rooms in a table  with a room-uid primary key  a DateFrom and a DateTo field  and another uid  here RM ApertureID to keep track of the same room  and a soft-delete field  like RM Status  where 99 means  deleted   and  lt   99 means  active     So when you create the first room  you insert RM UID and RM ApertureID as the same value as RM UID   Then  when you terminate the room to a date  and re-establish it with a new date range  RM UID is newid    and the RM ApertureID from the previous entry becomes the new RM ApertureID   So  if that s the case  RM ApertureID is a non-unique field  and so you can t set a foreign-key in another table    And there is no way to set a foreign key to a non-unique column index  e g  in T ZO REM AP Raum Reinigung  WHERE RM UID is actually RM ApertureID    But to prohibit invalid values  you need to set a foreign key  otherwise  data-garbage is the  result sooner rather than later      Now what you can do in this case  short of rewritting the entire application  is inserting a CHECK-constraint  with a scalar function checking the presence of the key   IF  EXISTS  SELECT   FROM sys check constraints WHERE object id   OBJECT ID N  dbo   Check RM ApertureIDisValid T ZO REM AP Raum Reinigung    AND parent object id   OBJECT ID N  dbo   T ZO REM AP Raum Reinigung     ALTER TABLE dbo T ZO REM AP Raum Reinigung DROP CONSTRAINT  Check RM ApertureIDisValid T ZO REM AP Raum Reinigung  GO   IF  EXISTS  SELECT   FROM sys objects WHERE object id   OBJECT ID N  dbo   fu Constaint ValidRmApertureId    AND type in  N FN   N IF   N TF   N FS   N FT    DROP FUNCTION  dbo   fu Constaint ValidRmApertureId  GO     CREATE FUNCTION  dbo   fu Constaint ValidRmApertureId         in RM ApertureID uniqueidentifier        in DatumVon AS datetime        in DatumBis AS datetime        in Status AS integer         RETURNS bit  AS  BEGIN        DECLARE  bNoCheckForThisCustomer AS bit      DECLARE  bIsInvalidValue AS bit      SET  bNoCheckForThisCustomer    false       SET  bIsInvalidValue    false        IF  in Status   99          RETURN  false         IF  in DatumVon  gt   in DatumBis      BEGIN          RETURN  true       END        IF  bNoCheckForThisCustomer    true          RETURN  bIsInvalidValue        IF NOT EXISTS                SELECT               T Raum RM UID               T Raum RM Status               T Raum RM DatumVon               T Raum RM DatumBis               T Raum RM ApertureID          FROM T Raum          WHERE  1 1           AND T Raum RM ApertureID    in RM ApertureID          AND  in DatumVon  gt   T Raum RM DatumVon          AND  in DatumBis  lt   T Raum RM DatumBis          AND T Raum RM Status  lt  gt  99                  SET  bIsInvalidValue    true  -- IF         RETURN  bIsInvalidValue  END     GO    IF  EXISTS  SELECT   FROM sys check constraints WHERE object id   OBJECT ID N  dbo   Check RM ApertureIDisValid T ZO REM AP Raum Reinigung    AND parent object id   OBJECT ID N  dbo   T ZO REM AP Raum Reinigung     ALTER TABLE dbo T ZO REM AP Raum Reinigung DROP CONSTRAINT  Check RM ApertureIDisValid T ZO REM AP Raum Reinigung  GO   -- ALTER TABLE dbo T AP Kontakte WITH CHECK ADD CONSTRAINT  Check RM ApertureIDisValid T ZO REM AP Raum Reinigung    ALTER TABLE dbo T ZO REM AP Raum Reinigung WITH NOCHECK ADD CONSTRAINT  Check RM ApertureIDisValid T ZO REM AP Raum Reinigung   CHECK         NOT                 dbo fu Constaint ValidRmApertureId ZO RMREM RM UID  ZO RMREM GueltigVon  ZO RMREM GueltigBis  ZO RMREM Status    1            GO   IF  EXISTS  SELECT   FROM sys check constraints WHERE object id   OBJECT ID N  dbo   Check RM ApertureIDisValid T ZO REM AP Raum Reinigung    AND parent object id   OBJECT ID N  dbo   T ZO REM AP Raum Reinigung      ALTER TABLE dbo T ZO REM AP Raum Reinigung CHECK CONSTRAINT  Check RM ApertureIDisValid T ZO REM AP Raum Reinigung   GO

User · Answer

As others have pointed out  ideally  the foreign key would be created as a reference to a primary key  usually an IDENTITY column    However  we don t live in an ideal world  and sometimes even a  small  change to a schema can have significant ripple effects to the application logic   Consider the case of a Customer table with a SSN column  and a dumb primary key   and a Claim table that also contains a SSN column  populated by business logic from the Customer data  but no FK exists    The design is flawed  but has been in use for several years  and three different applications have been built on the schema   It should be obvious that ripping out Claim SSN and putting in a real PK-FK relationship would be ideal  but would also be a significant overhaul   On the other hand  putting a UNIQUE constraint on Customer SSN  and adding a FK on Claim SSN  could provide referential integrity  with little or no impact on the applications   Don t get me wrong  I m all for normalization  but sometimes pragmatism wins over idealism   If a mediocre design can be helped with a band-aid  surgery might be avoided

User · Answer

If you really want to create a foreign key to a non-primary key  it MUST be a column that has a unique constraint on it   From Books Online      A FOREIGN KEY constraint does not have to be linked only to a PRIMARY   KEY constraint in another table  it can also be defined to reference   the columns of a UNIQUE constraint in another table    So in your case if you make AnotherID unique  it will be allowed  If you can t apply a unique constraint you re out of luck  but this really does make sense if you think about it   Although  as has been mentioned  if you have a perfectly good primary key as a candidate key  why not use that

[sql] Foreign Key to non-primary key

Examples related to sql

Examples related to sql-server