none
Foreign key references a two column primary key

    Question

  • Hi,

    Im using SQL Server 2005.

    I have created a Persons table with a two column primary key.

    Now, I want to reference that primary key from a Persons_fk table.

    I have this code:

    CREATE TABLE Persons
    (
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
    )
    
    CREATE TABLE Persons_fk
    (
    O_Id int NOT NULL PRIMARY KEY,
    OrderNo int NOT NULL,
    P_Id [varchar](255) FOREIGN KEY REFERENCES Persons(pk_PersonID)
    )

    But when I'm trying to create the Persons_fk table, i get this error:

    "External key FK__Persons_fk__P_Id__07F6335A is making reference to an invalid column pk_PersonID in table Persons. Constraint cannot be created"

    How can i solve this?

    Thanks


    pyram

    Thursday, June 14, 2012 1:17 PM

Answers

  • If ID and LastName are PK columns in the Reference table, the referencing table must also include both columns (P_ID and LastName). Also, why one table has P_ID as int and the other has it as P_ID varchar(255)?

    Please fix the definition of the second table. Also, where did you see a person with 255 characters for the first or last name? 20/30 should be enough.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by pyram07 Thursday, June 14, 2012 2:11 PM
    Thursday, June 14, 2012 1:21 PM
    Moderator
  • You need to include in the child table as many columns as the ones in the key you are referencing from the parent table, as Naomi suggested.

    CREATE TABLE Persons_fk
    (
    O_Id int NOT NULL PRIMARY KEY,
    OrderNo int NOT NULL,
    LastName varchar(255) not null,
    P_Id int not null,
    constraint fk_Persons_fk_Persons FOREIGN KEY (P_Id, LastName) REFERENCES Persons(P_Id, LastName)
    );


    AMB

    Some guidelines for posting questions...



    Thursday, June 14, 2012 1:45 PM
    Moderator
  • Here is a sample from one of my tables:

    ALTER TABLE [dbo].[i_invent]  WITH NOCHECK ADD  CONSTRAINT [FK_i_invent_items] FOREIGN KEY([department], [category], [item])
    REFERENCES [dbo].[items] ([department], [category], [item])
    

    So, your Persons_fk table needs to have two columns (P_ID, LastName) and they will be the foreign key referencing the same keys in the parent table.

    You can not have 1 column referencing 2 columns.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 14, 2012 1:47 PM
    Moderator
  • > There is absolutely no workaround to have a foreign key with one column referencing to a primary key of two columns?

    You need the same columns, number and data types, that you are referencing. There is a workaround, if you do not want to carry the natural key when it includes multiple columns, and it is using a surrogate key (i.e. column with identity property and unique constraint).

    Example:

    create table parent (
    sk int not null identity(1, 1) constraint uq_parent_sk unique clustered,
    c1 int not null,
    c2 char(5) not null,
    c3 date not null,
    ...,
    constraint pk_parent primary key (c1, c2, c3)
    );

    create table child (
    col1 int not null,
    col2 varchar(50),
    col3 date,
    parent_sk int not null,
    constraint fk_child_parent foreigh key (parent_sk) references parent (sk),
    constraint pk_child primary key (col1, parent_sk)
    );

    One cons that you will find with this approach is that you will not be able to filter on the child side by any column participating in the parent natural key unless you join both tables.

    Here is a good book to learn about database design and implementation.

    Pro SQL Server 2008 Relational Database Design and Implementation
    http://www.amazon.com/Server-Relational-Database-Implementation-Experts/dp/143020866X/ref=sr_1_1?ie=UTF8&qid=1339684475&sr=8-1


    AMB

    Some guidelines for posting questions...

    • Marked as answer by pyram07 Thursday, June 14, 2012 6:17 PM
    Thursday, June 14, 2012 2:36 PM
    Moderator
  • You might want to read the definition of DRI and what a keyu is. You can reference any unique constraint. Let's assume that the person_is is really an identifier and therefore unique, you can write this

    CREATE TABLE Persons
    (person_id INTEGER NOT NULL PRIMARY KEY,
     last_name VARCHAR(25) NOT NULL,
     UNIQUE (person_id, last_name),
     ..);

    CREATE TABLE Orders
    (order_nbr INTEGER NOT NULL PRIMARY KEY,
     person_id VARCHAR(25) 
           REFERENCES Persons(person_id),
     ..); 

    Now look up how DRI actions work. 


     

     



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by pyram07 Thursday, June 14, 2012 6:17 PM
    Thursday, June 14, 2012 2:40 PM

All replies

  • If ID and LastName are PK columns in the Reference table, the referencing table must also include both columns (P_ID and LastName). Also, why one table has P_ID as int and the other has it as P_ID varchar(255)?

    Please fix the definition of the second table. Also, where did you see a person with 255 characters for the first or last name? 20/30 should be enough.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by pyram07 Thursday, June 14, 2012 2:11 PM
    Thursday, June 14, 2012 1:21 PM
    Moderator
  • If ID and LastName are PK columns in the Reference table, the referencing table must also include both columns (P_ID and LastName). Also, why one table has P_ID as int and the other has it as P_ID varchar(255)?

    Please fix the definition of the second table. Also, where did you see a person with 255 characters for the first or last name? 20/30 should be enough.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    I did some modifications:

    CREATE TABLE Persons_fk
    (
    O_Id int NOT NULL PRIMARY KEY,
    OrderNo int NOT NULL,
    fk7 varchar(255) FOREIGN KEY REFERENCES Persons(P_Id, LastName)
    )
    

    I have renamed the foreign key to fk7. I have declared varchar to the foreign key, because in the first table the primary key is made of a combinations of an int and and varchar column. This is the first time I have done this, so Im thinking the foreign key must handle the two values. So that's why i put varchar. I dont know if its correct.

    The 255 for varchar is just a test number.

    When executing this new code Im getting this error:

    "The FOREIGN KEY column constraint has more than one key specified, table Persons_fk"


    pyram

    Thursday, June 14, 2012 1:41 PM
  • You need to include in the child table as many columns as the ones in the key you are referencing from the parent table, as Naomi suggested.

    CREATE TABLE Persons_fk
    (
    O_Id int NOT NULL PRIMARY KEY,
    OrderNo int NOT NULL,
    LastName varchar(255) not null,
    P_Id int not null,
    constraint fk_Persons_fk_Persons FOREIGN KEY (P_Id, LastName) REFERENCES Persons(P_Id, LastName)
    );


    AMB

    Some guidelines for posting questions...



    Thursday, June 14, 2012 1:45 PM
    Moderator
  • Here is a sample from one of my tables:

    ALTER TABLE [dbo].[i_invent]  WITH NOCHECK ADD  CONSTRAINT [FK_i_invent_items] FOREIGN KEY([department], [category], [item])
    REFERENCES [dbo].[items] ([department], [category], [item])
    

    So, your Persons_fk table needs to have two columns (P_ID, LastName) and they will be the foreign key referencing the same keys in the parent table.

    You can not have 1 column referencing 2 columns.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 14, 2012 1:47 PM
    Moderator
  • Here is a sample from one of my tables:

    ALTER TABLE [dbo].[i_invent]  WITH NOCHECK ADD  CONSTRAINT [FK_i_invent_items] FOREIGN KEY([department], [category], [item])
    REFERENCES [dbo].[items] ([department], [category], [item])

    So, your Persons_fk table needs to have two columns (P_ID, LastName) and they will be the foreign key referencing the same keys in the parent table.

    You can not have 1 column referencing 2 columns.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    There is absolutely no workaround to have a foreign key with one column referencing to a primary key of two columns?

    pyram

    Thursday, June 14, 2012 2:03 PM
  • As far as I know, not in SQL Server - no workaround.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 14, 2012 2:05 PM
    Moderator
  • As far as I know, not in SQL Server - no workaround.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Ok. Thanks!

    pyram

    Thursday, June 14, 2012 2:22 PM
  • You need to include in the child table as many columns as the ones in the key you are referencing from the parent table, as Naomi suggested.

    CREATE TABLE Persons_fk
    (
    O_Id int NOT NULL PRIMARY KEY,
    OrderNo int NOT NULL,
    LastName varchar(255) not null,
    P_Id int not null,
    constraint fk_Persons_fk_Persons FOREIGN KEY (P_Id, LastName) REFERENCES Persons(P_Id, LastName)
    );


    AMB

    Some guidelines for posting questions...



    Thanks!

    pyram

    Thursday, June 14, 2012 2:22 PM
  • > There is absolutely no workaround to have a foreign key with one column referencing to a primary key of two columns?

    You need the same columns, number and data types, that you are referencing. There is a workaround, if you do not want to carry the natural key when it includes multiple columns, and it is using a surrogate key (i.e. column with identity property and unique constraint).

    Example:

    create table parent (
    sk int not null identity(1, 1) constraint uq_parent_sk unique clustered,
    c1 int not null,
    c2 char(5) not null,
    c3 date not null,
    ...,
    constraint pk_parent primary key (c1, c2, c3)
    );

    create table child (
    col1 int not null,
    col2 varchar(50),
    col3 date,
    parent_sk int not null,
    constraint fk_child_parent foreigh key (parent_sk) references parent (sk),
    constraint pk_child primary key (col1, parent_sk)
    );

    One cons that you will find with this approach is that you will not be able to filter on the child side by any column participating in the parent natural key unless you join both tables.

    Here is a good book to learn about database design and implementation.

    Pro SQL Server 2008 Relational Database Design and Implementation
    http://www.amazon.com/Server-Relational-Database-Implementation-Experts/dp/143020866X/ref=sr_1_1?ie=UTF8&qid=1339684475&sr=8-1


    AMB

    Some guidelines for posting questions...

    • Marked as answer by pyram07 Thursday, June 14, 2012 6:17 PM
    Thursday, June 14, 2012 2:36 PM
    Moderator
  • You might want to read the definition of DRI and what a keyu is. You can reference any unique constraint. Let's assume that the person_is is really an identifier and therefore unique, you can write this

    CREATE TABLE Persons
    (person_id INTEGER NOT NULL PRIMARY KEY,
     last_name VARCHAR(25) NOT NULL,
     UNIQUE (person_id, last_name),
     ..);

    CREATE TABLE Orders
    (order_nbr INTEGER NOT NULL PRIMARY KEY,
     person_id VARCHAR(25) 
           REFERENCES Persons(person_id),
     ..); 

    Now look up how DRI actions work. 


     

     



    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Marked as answer by pyram07 Thursday, June 14, 2012 6:17 PM
    Thursday, June 14, 2012 2:40 PM
  • > There is absolutely no workaround to have a foreign key with one column referencing to a primary key of two columns?

    You need the same columns, number and data types, that you are referencing. There is a workaround, if you do not want to carry the natural key when it includes multiple columns, and it is using a surrogate key (i.e. column with identity property and unique constraint).

    Example:

    create table parent (
    sk int not null identity(1, 1) constraint uq_parent_sk unique clustered,
    c1 int not null,
    c2 char(5) not null,
    c3 date not null,
    ...,
    constraint pk_parent primary key (c1, c2, c3)
    );

    create table child (
    col1 int not null,
    col2 varchar(50),
    col3 date,
    parent_sk int not null,
    constraint fk_child_parent foreigh key (parent_sk) references parent (sk),
    constraint pk_child primary key (col1, parent_sk)
    );

    One cons that you will find with this approach is that you will not be able to filter on the child side by any column participating in the parent natural key unless you join both tables.

    Here is a good book to learn about database design and implementation.

    Pro SQL Server 2008 Relational Database Design and Implementation
    http://www.amazon.com/Server-Relational-Database-Implementation-Experts/dp/143020866X/ref=sr_1_1?ie=UTF8&qid=1339684475&sr=8-1


    AMB

    Some guidelines for posting questions...

    In this line on the child table:

    constraint fk_child_parent foreigh key (parent_sk) references parent (sk)

    How does this line "references" the 3 columns which conform the primary key?


    pyram

    Thursday, June 14, 2012 4:06 PM
  • Alejandro re-defined the schema to use one column PK/FK (identity surrogate key). Using surrogate keys have many advantages and disadvantages, I suggest to read answers (and links from the answers, especially my few last answers with links) on this quiz question:

    http://beyondrelational.com/quiz/sqlserver/general/2010/questions/sqlserver-quiz-general-2010-madhu-k-nair-surrogate-key-vs-natural-key.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, June 14, 2012 4:10 PM
    Moderator
  • Thank you all for the replies

    pyram

    Thursday, June 14, 2012 6:18 PM