Foreign Key Used in Composite Primary Key

Question

Is it possible to use a composite foreign key as a piece of a table's composite primary key?

For instance, let's say I have two tables:

CREATE TABLE DB.dbo.Partners
(
    CONSTRAINT pk_Partners_Id
    PRIMARY KEY (Name, City, State, Country, PostalCode),
    
    Name                VARCHAR(100)    NOT NULL,
    Address1            VARCHAR(100),   
    Address2            VARCHAR(100),   
    Address3            VARCHAR(100),   
    City                VARCHAR(150)    NOT NULL,   
    State               CHAR(2)         NOT NULL,   
    Country             CHAR(2)         NOT NULL,   
    PostalCode          VARCHAR(16)     NOT NULL,   
    Phone               VARCHAR(20),    
    Fax                 VARCHAR(20),    
    Email               VARCHAR(256)
)

... and then in a second table, I would like to reference the foreign key in the second table's primary key:

CREATE TABLE DB.dbo.PartnerContacts
(
    CONSTRAINT pk_PartnerContacts_Id
    PRIMARY KEY (fk_PartnerContacts_PartnerId, FirstName, LastName, PhoneNumber, Email),
                        
    CONSTRAINT fk_PartnerContacts_PartnerId
    FOREIGN KEY REFERENCES Partners(Name, City, State, Country, PostalCode),

    FirstName           VARCHAR(75)     NOT NULL,   
    MiddleName          VARCHAR(75),    
    LastName            VARCHAR(75)     NOT NULL,   
    PhoneNumber         VARCHAR(20)     NOT NULL,   
    MobileNumber        VARCHAR(20),    
    FaxNumber           VARCHAR(20),    
    Email               VARCHAR(256)    NOT NULL,   
    MailTo              VARCHAR(100),   
    Address1            VARCHAR(100),   
    Address2            VARCHAR(100),   
    Address3            VARCHAR(100),   
    City                VARCHAR(150),   
    State               CHAR(2),    
    Country             CHAR(2),    
    PostalCode          VARCHAR(16)
)

Is there any way that I can do that? Yes, it might be easier to just simply use IDENTITY columns in these tables but if I can define an actual relationship without an IDENTITY I would like to do that.

EDIT:

I wanted to provide the final, working SQL. Thanks to everyone who answered!

CREATE TABLE DB.dbo.Partners
(
    CONSTRAINT pk_Partners_Id
    PRIMARY KEY (Name, City, State, Country, PostalCode),
    
    Id                  INT             NOT NULL   UNIQUE   IDENTITY(1, 1),
    Name                VARCHAR(100)    NOT NULL,
    Address1            VARCHAR(100),   
    Address2            VARCHAR(100),   
    Address3            VARCHAR(100),   
    City                VARCHAR(150)    NOT NULL,   
    State               CHAR(2)         NOT NULL,   
    Country             CHAR(2)         NOT NULL,   
    PostalCode          VARCHAR(16)     NOT NULL,   
    Phone               VARCHAR(20),    
    Fax                 VARCHAR(20),    
    Email               VARCHAR(256)
)

CREATE TABLE DB.dbo.PartnerContacts
(
    CONSTRAINT pk_PartnerContacts_Id
    PRIMARY KEY
    (PartnerId, FirstName, LastName, PhoneNumber, Email),
                        
    PartnerId           INT             NOT NULL CONSTRAINT fk_PartnerContacts_PartnerId FOREIGN KEY    REFERENCES Partners(Id),
    FirstName           VARCHAR(75)     NOT NULL,
    MiddleName          VARCHAR(75),    
    LastName            VARCHAR(75)     NOT NULL,   
    PhoneNumber         VARCHAR(20)     NOT NULL,   
    MobileNumber        VARCHAR(20),    
    FaxNumber           VARCHAR(20),    
    Email               VARCHAR(256)    NOT NULL,
    MailTo              VARCHAR(100),   
    Address1            VARCHAR(100),   
    Address2            VARCHAR(100),   
    Address3            VARCHAR(100),   
    City                VARCHAR(150),   
    State               CHAR(2),    
    Country             CHAR(2),    
    PostalCode          VARCHAR(16)
)

Is it giving you an error when you try that? If so what's the error? — Dylan Smith, Oct 04 '11 at 16:20
Dylan: Yes: "Number of referencing columns in foreign key differs from number of referenced columns, table 'DB.dbo.PartnerContacts'" and "Msg 1911, Level 16, State 1, Line 34 Column name 'fk_PartnerContacts_PartnerId' does not exist in the target table or view." — Vince Fedorchak, Oct 04 '11 at 16:25
Name, City, State, Country, PostalCode is not something I would rely on as being unique so it makes a bad choice as a primary key. — HLGEM, Oct 04 '11 at 19:46
You said, "if I can define an actual relationship without an IDENTITY I would like to do that". You were shown that you indeed can. Yet you chose to use an IDENTITY in your solution. Confusing. — onedaywhen, Oct 05 '11 at 09:01

score 8 · Accepted Answer · answered Oct 04 '11 at 16:59

You probably need to specify the columns that are supposed to match.

CONSTRAINT fk_PartnerContacts_PartnerId
FOREIGN KEY         (columns that correspond to referenced columns) 
 REFERENCES Partners (Name, City, State, Country, PostalCode),

So you need to provide the five column names whose values are supposed to match the values of {Name, City, State, Country, PostalCode} in the table "Partners". i'm pretty sure youcan't do that with your current structure. You won't be able to match "Name". I think you're looking for something along these lines.

CREATE TABLE DB.dbo.PartnerContacts (
-- Start with columns that identify "Partner".
    partner_name VARCHAR(100) NOT NULL,
    partner_city VARCHAR(150) NOT NULL,
    partner_state CHAR(2) NOT NULL,
    partner_country CHAR(2) NOT NULL,
    partner_postcode VARCHAR(16) NOT NULL,
    CONSTRAINT fk_PartnerContacts_PartnerId
        FOREIGN KEY (partner_name, partner_city, partner_state, partner_country, partner_postcode) 
        REFERENCES Partners (Name, City, State, Country, PostalCode),
    FirstName    VARCHAR(75) NOT NULL,
    MiddleName   VARCHAR(75),
    LastName     VARCHAR(75) NOT NULL,
    PhoneNumber  VARCHAR(20) NOT NULL,
    MobileNumber VARCHAR(20),
    FaxNumber    VARCHAR(20),
    Email        VARCHAR(256) NOT NULL,
    MailTo       VARCHAR(100),
    Address1     VARCHAR(100),
    Address2     VARCHAR(100),
    Address3     VARCHAR(100),
    City         VARCHAR(150),
    State        CHAR(2),
    Country      CHAR(2),
    PostalCode   VARCHAR(16),
    CONSTRAINT pk_PartnerContacts_Id
    PRIMARY KEY (partner_name, partner_city, partner_state, partner_country, partner_postcode, 
                 FirstName, LastName, PhoneNumber, Email)
);

Ah Okay, it sounds like I need to use an IDENTITY column in the Partners table and reference it in the PartnerContacts table. Thanks :) — Vince Fedorchak, Oct 04 '11 at 17:04
Probably, but make sure IDENTITY identifies a partner, not a row. See http://stackoverflow.com/questions/7639637/sql-database-design-best-practice-addresses/7641542#7641542 — Mike Sherrill 'Cat Recall', Oct 04 '11 at 17:30

score 4 · Answer 2 · answered Oct 04 '11 at 16:23

4

Yes it is possible and is generally considered best DB design practice, but practically, an ID column is just easier to deal with. Think of join tables, their primary key is a composite of two foreign keys. There is no difference to using multiple foreign keys as part of a composite primary key.

answered Oct 04 '11 at 16:23

Watermark Studios

1,173
1
10
24

Okay, that's good news... can you supply a syntax example of what this would look like? – Vince Fedorchak Oct 04 '11 at 16:26

score 0 · Answer 3 · answered Feb 22 '17 at 16:03

Yes, that is definitely possible. We do have instances where we have a composite foreign key that is a part of the composite primary key of other table.

Let's simplify the use case little bit for the below example.

Say we have a table test1 having a composite primary key (A, B)

Now we can have a table say test2 having primary key (P, Q, R) where in (P,Q) of test2 references (A,B) of test1.

I ran the following script in the MySql database and it works just fine.

CREATE TABLE `test1` (
`A` INT NOT NULL,
`B` VARCHAR(2) NOT NULL,
`C` DATETIME NULL,
`D` VARCHAR(45) NULL,
PRIMARY KEY (`A`, `B`));

CREATE TABLE `test2` (
`P` INT NOT NULL,
`Q` VARCHAR(2) NOT NULL,
`R` INT NOT NULL,
`S` DATETIME NULL,
`T` VARCHAR(8) NULL,
PRIMARY KEY (`P`, `Q`, `R`),
INDEX `PQ_idx` (`P`,`Q` ASC),
CONSTRAINT `PQ`
  FOREIGN KEY (`P`, `Q`)
  REFERENCES `test1` (`A`,`B`)
  ON DELETE CASCADE
  ON UPDATE CASCADE);

In the above mentioned case, the database is expecting the combination of (A,B) to be unique and it is, being a primary key in test1 table.

But if you try to do something like following, the script would fail. The database would not let you create the test2 table.

CREATE TABLE `test2` (
`P` INT NOT NULL,
`Q` VARCHAR(2) NULL,
`R` DATETIME NULL,
`S` VARCHAR(8) NULL,
`T` VARCHAR(45) NULL,
  INDEX `P_idx` (`P` ASC),
  INDEX `Q_idx` (`Q` ASC),
  CONSTRAINT `P`
    FOREIGN KEY (`P`)
    REFERENCES `test1` (`A`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Q`
    FOREIGN KEY (`Q`)
    REFERENCES `test1` (`B`)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

In the above mentioned case database would expect the column A to be unique individually and the same follows for column B. It does not matter if combination of (A,B) is unique.

Foreign Key Used in Composite Primary Key

3 Answers3