0

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'.

  • 2
    Also -- though it doesn't solve your problem -- the second statement contains an error: the column phone doesn't exist. – Colin 't Hart Dec 8 '14 at 12:29
4

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)
);
  • +1 Also he is doing primary key(id,phone) on t2 but t2 has no phone column. – Tulains Córdova Dec 8 '14 at 15:14
  • the primary key is(id,name). if changed the "phone" to "name" it has the same error. – Tofiq Ahmad Dec 9 '14 at 5:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.