Sql-server – Create relationship between two tables with composite primary key

foreign keyprimary-keyrelational-theorysql-server-2012

I've created a table [t1] with two fields and a composite primary key, show in below:

create table t1(
    id int,
    name nchar(10),
    primary key(id,name)
);

I want to create a second table [t2] that has a relationship with [t1], but I get an error with the below create table script:

create table t2(
    id int references t1(id),
    name nchar(10) references t1(name),
    primary key(id,name)
);

This the error message:

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 't1' that match the referencing column list in the foreign key 'FK__t2__id__33D4B598'.

Best Answer

A multiple-column foreign key constraint must be declared at the table level, not on each column:

CREATE TABLE dbo.t1
(
    id int NOT NULL,
    name nchar(10) NOT NULL,

    CONSTRAINT PK_dbo_t1_id_name
    PRIMARY KEY CLUSTERED (id,name)
);

CREATE TABLE dbo.t2
(
    id int NOT NULL,
    name nchar(10) NOT NULL,

    CONSTRAINT PK_dbo_t2_id_name
    PRIMARY KEY CLUSTERED (id,name),

    CONSTRAINT FK_dbo_t2_t1_id_name
    FOREIGN KEY (id, name) 
    REFERENCES dbo.t1 (id, name)
);
Related Question