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

1
  • 2
    Also -- though it doesn't solve your problem -- the second statement contains an error: the column phone doesn't exist. Dec 8, 2014 at 12:29

1 Answer 1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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