locked
Primary key error RRS feed

  • Question

  • In Table A, i have two fields together as Primary Key. (Field1 and Field2)

    In Table B, I want to refer only Field1 as FK.  Is that possible?.  I keep getting some errors.  What are the other workarounds?.


    NSG12
    Tuesday, March 30, 2010 3:54 PM

Answers

  • In Table A, i have two fields together as Primary Key. (Field1 and Field2)

    In Table B, I want to refer only Field1 as FK.  Is that possible?.  I keep getting some errors.  What are the other workarounds?.

    You have to matchup the composite PRIMARY KEY with a corresponding composite FOREIGN KEY. This is the very basis of relational database theory. See demo following.

    As indicated above if you can create a UNIQUE KEY, and you can FK reference it instead of a PRIMARY KEY.

    Commonly accepted Database Design practice is to use INT IDENTITY(1,1) as SURROGATE PRIMARY KEY so there is no issue nor with composite FK referencing, neither with long, inefficient PK-s / FK-s. In such a design, you can designate the Natural Key as a UNIQUE KEY or just place a UNIQUE INDEX on the Natural Key column.

    There are some applications (minority) where the Natural Key maybe a better choice for PK than INT IDENTITY Surrogate Key.

    -- T-SQL composite PRIMARY KEY referencing by FOREIGN KEY
    USE tempdb;
    SELECT ProductID, ProductNumber, ProductName=Name
    INTO Product
    FROM AdventureWorks2008.Production.Product
    GO
    -- (504 row(s) affected)
    
    ALTER TABLE Product 
    ADD CONSTRAINT pkDouble PRIMARY KEY(ProductID, ProductNumber)
    GO
    -- Referencing only part of PK gives error
    CREATE TABLE ProductPhoto(
    ProductID int REFERENCES Product(ProductID),
    Photo varbinary(max));
    GO
    /*
    Msg 1776, Level 16, State 0, Line 2
    There are no primary or candidate keys in the referenced table 'Product' 
    that match the referencing column list in the foreign key 
    'FK__ProductPh__Produ__13F1F5EB'.
    Msg 1750, Level 16, State 0, Line 2
    Could not create constraint. See previous errors.
    */
    
    -- All parts of a composite Primary Key must be referenced
    CREATE TABLE ProductPhoto(
    ProductID int,
    ProductNumber nvarchar(25),
    Photo varbinary(max));
    GO
    
    ALTER TABLE [dbo].[ProductPhoto]  WITH CHECK ADD  
    CONSTRAINT [FK_ProductPhoto_Product] 
    FOREIGN KEY([ProductID], [ProductNumber])
    REFERENCES [dbo].[Product] ([ProductID], [ProductNumber])
    GO
    ALTER TABLE [dbo].[ProductPhoto] CHECK CONSTRAINT [FK_ProductPhoto_Product]
    GO
    
    DROP TABLE tempdb.dbo.ProductPhoto
    DROP TABLE tempdb.dbo.Product
    

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by NSG12 Monday, April 5, 2010 2:42 PM
    Sunday, April 4, 2010 12:30 PM

All replies

  • Can you post the error message/messages?
    Tuesday, March 30, 2010 3:56 PM
  • If Field1 is unique you can add UNIQUE constraint and then reference. If the column is not unique then you cannot do it.
    Plamen Ratchev
    • Proposed as answer by HunchbackMVP Tuesday, March 30, 2010 4:10 PM
    Tuesday, March 30, 2010 3:58 PM
  • In Table A, Field1 and Field2 together will be Primary Key.  So, In Table B, when i refer to Field1, the error is

    'The column in TableA do not match an existing primary key or unique constraint'


    NSG12
    Tuesday, March 30, 2010 4:03 PM
  • --Creating composit primary key 
    Create table #T( fieldid1 int not null, fieldid2 int not null, name varchar(20))
    go
    alter table #T 
    add constraint abc primary key (fieldid1,fieldid2)
    
    
    --Creating foreign key only on one column of primary key
    Create table #T1( id int,Fieldid1 INT)
    go
    alter table #T1
    add constraint fk_ foreign key (Fieldid1) references #T(fieldid1)

    Hello NSG12,

     please have the code, maybe you need to modify according to your requirements.

    Thanks

    Tuesday, March 30, 2010 4:11 PM
  • In Table A, Field1 and Field2 together will be Primary Key.  So, In Table B, when i refer to Field1, the error is

    'The column in TableA do not match an existing primary key or unique constraint'


    NSG12


    In order to reference a column from the parent side in a foreign key constraint, you will need one of this conditions to be true:

    - It is the primary key of the referenced table.

    - There is a unique constraint by that column.

    - There is a unique index by that column.

    In case the foreign key constraint is multi-columns, then expand previous conditions to be multi-columns too.

    AMB

    Tuesday, March 30, 2010 4:13 PM
  • Shez211, I tried your code, it gives the same error.  Did you not get that error?


    NSG12
    Tuesday, March 30, 2010 4:33 PM
  • Hello NSG12,

    Sorry i could not test the code because i don't have SQL Server at the time. Please modify your query as Hunchback mentioned above.

    Thanks

    Tuesday, March 30, 2010 5:13 PM
  • In Table A, i have two fields together as Primary Key. (Field1 and Field2)

    In Table B, I want to refer only Field1 as FK.  Is that possible?.  I keep getting some errors.  What are the other workarounds?.

    You have to matchup the composite PRIMARY KEY with a corresponding composite FOREIGN KEY. This is the very basis of relational database theory. See demo following.

    As indicated above if you can create a UNIQUE KEY, and you can FK reference it instead of a PRIMARY KEY.

    Commonly accepted Database Design practice is to use INT IDENTITY(1,1) as SURROGATE PRIMARY KEY so there is no issue nor with composite FK referencing, neither with long, inefficient PK-s / FK-s. In such a design, you can designate the Natural Key as a UNIQUE KEY or just place a UNIQUE INDEX on the Natural Key column.

    There are some applications (minority) where the Natural Key maybe a better choice for PK than INT IDENTITY Surrogate Key.

    -- T-SQL composite PRIMARY KEY referencing by FOREIGN KEY
    USE tempdb;
    SELECT ProductID, ProductNumber, ProductName=Name
    INTO Product
    FROM AdventureWorks2008.Production.Product
    GO
    -- (504 row(s) affected)
    
    ALTER TABLE Product 
    ADD CONSTRAINT pkDouble PRIMARY KEY(ProductID, ProductNumber)
    GO
    -- Referencing only part of PK gives error
    CREATE TABLE ProductPhoto(
    ProductID int REFERENCES Product(ProductID),
    Photo varbinary(max));
    GO
    /*
    Msg 1776, Level 16, State 0, Line 2
    There are no primary or candidate keys in the referenced table 'Product' 
    that match the referencing column list in the foreign key 
    'FK__ProductPh__Produ__13F1F5EB'.
    Msg 1750, Level 16, State 0, Line 2
    Could not create constraint. See previous errors.
    */
    
    -- All parts of a composite Primary Key must be referenced
    CREATE TABLE ProductPhoto(
    ProductID int,
    ProductNumber nvarchar(25),
    Photo varbinary(max));
    GO
    
    ALTER TABLE [dbo].[ProductPhoto]  WITH CHECK ADD  
    CONSTRAINT [FK_ProductPhoto_Product] 
    FOREIGN KEY([ProductID], [ProductNumber])
    REFERENCES [dbo].[Product] ([ProductID], [ProductNumber])
    GO
    ALTER TABLE [dbo].[ProductPhoto] CHECK CONSTRAINT [FK_ProductPhoto_Product]
    GO
    
    DROP TABLE tempdb.dbo.ProductPhoto
    DROP TABLE tempdb.dbo.Product
    

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Marked as answer by NSG12 Monday, April 5, 2010 2:42 PM
    Sunday, April 4, 2010 12:30 PM