1

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

jarlh
  • 42,561
  • 8
  • 45
  • 63
Sytze
  • 39
  • 10

2 Answers2

2

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?

jarlh
  • 42,561
  • 8
  • 45
  • 63
  • Thanks for your answer! We already altered the statement to avoid a self-referencing foreign key; that wasn't supposed to be like that. However, even by altering the statement it still doesn't work... rackSize and rackType form a composite key: they're both primary keys. Therefore, we want the primary (composite) key of rackSize and rackType to reference to another column named rackSort. Is this possible? – Sytze Oct 28 '15 at 09:16
  • Can't you add both table definitions to the question? No, two columns can never be a FK referencing only 1 column. – jarlh Oct 28 '15 at 09:33
1

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

This may help you:

Add Composite Foreign Key

Community
  • 1
  • 1
Muhammad Muazzam
  • 2,810
  • 6
  • 33
  • 62