Jason Krs Jason Krs - 5 months ago 21
SQL Question

SQL Server table creation error : There are no primary or candidate keys in the referenced

A very basic script is driving me crazy. It says it cant find a referenced key. Not idea what it's all about. I'm am using SQL SERVER 2014 and this script is for the creation of my database tables. I'm trying to make the

id_TABLE_1
in the table
TABLE_2
reference the id of the table
TABLE_1
.

CREATE TABLE TABLE_1
(
id int identity,
email varchar(50) not null,

constraint PK_TABLE_1 primary key (id,email)
)
GO

CREATE TABLE TABLE_2
(
id int identity,
id_TABLE_1 int not null,

constraint PK_TABLE_2 primary key (id),
constraint FK_TABLE_2 foreign key (id_TABLE_1)
references TABLE_1(id) on delete cascade
)
GO


The error is :


Msg 1776, Level 16, State 0, Line 32

There are no primary or candidate keys in the referenced table 'TABLE_1' that match the referencing column list in the foreign key 'FK_TABLE_2'.

Msg 1750, Level 16, State 0, Line 32

Could not create constraint or index. See previous errors.


Can you help me here ?

Answer

As per comment, you are trying to reference an index that doesn't exist. The primary key on your OBJET_GEO table is a composite key containing two columns: id and email_utilisateur.

For this to compile, you can either alter your primary key to:

CONSTRAINT PK_OBJET_GEO PRIMARY KEY (id)

or create a new index on just the id column:

CREATE INDEX IX_OBJET_GEO_id ON OBJET_GEO (id);
Comments