CODEWITHSUNDEEP
mysqlreferencekey
Thibaud.P
Thibaud.P

Reputation: 29

ERROR 1064 (42000) MySQL PRIMARY KEY REFERENCES

I never used MySQL before but I don't have choice this time. I've an error I don't understand. I try to create a table : 

CREATE TABLE proprietaire(
    num_cpt INT REFERENCES compte(num_cpt) PRIMARY KEY,
    num_client INT REFERENCES client(num_client),
    num_commercant INT REFERENCES commercant(num_commercant)
);

I can't see my mistake. Could you please help me with this ?

Thank You

Upvotes: 0

Views: 295

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

In MySQL, to enforce foreign key references, you cannot put the references as a modifier on the column. You need a separate constraint for them:

CREATE TABLE proprietaire (
    num_cpt INT  PRIMARY KEY,
    num_client INT,
    num_commercant INT,
    CONSTRAINT fk_num_cpt FOREIGN KEY REFERENCES compte(num_cpt),
    CONSTRAINT fk_num_client FOREIGN KEY REFERENCES client(num_client),
    CONSTRAINT fk_num_commercant FOREIGN KEY REFERENCES commercant(num_commercant)
);

However, your specific problem was the REFERENCES before the PRIMARY KEY constraint.

As a note: in most English-language databases, your use of num would be id. num sounds like a count or "number of" something, whereas id is short for identifier.

Upvotes: 1

Related Questions