CODEWITHSUNDEEP
sqlms-access
Sytze
Sytze

Reputation: 39

MS Access SQL Query ADD Foreign Key

We're having trouble with adding a foreign key constraint with SQL in MS Access. We're trying to add a composite key as a foreign key. Underneath you'll find our SQL-statement:

ALTER TABLE ARTICLE (
ADD CONSTRAINT rackSort_FK
FOREIGN KEY (rackSort) 
REFERENCES ARTICLE(rackSize, rackType)
);

Do you see any mistakes? Why doesn't this work? Access shows the following error:

Syntax error in ALTER TABLE statement.

Thanks in advance! Greets, Sytze & Tom

Upvotes: 1

Views: 2057

Answers (2)

Muhammad Muazzam
Muhammad Muazzam

Reputation: 2800

You have to add same no. of columns as foreign keys.

This may help you:

Add Composite Foreign Key

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Close, just remove parentheses.

ALTER TABLE ARTICLE 
ADD CONSTRAINT rackSort_FK
FOREIGN KEY (rackSort) 
REFERENCES ARTICLE(rackSize, rackType)
;

But the number of columns must be the same and match! (rackSort) is just one, but (rackSize, rackType) are two columns... You have to change that!!!

Also consider Andre451's comment above, do you really want to create a self-referencing foreign key?

Upvotes: 2

Related Questions