question

shirts avatar image
shirts asked

table of foreign key attributes

im trying to have table that will represent a connection table
the attributes are foreign key.
it gives an error.

for example:

CREATE TABLE Person(
ID INT PRIMARY KEY,
Name VARCHAR(100));

CREATE TABLE Accessory(
ID INT PRIMARY KEY,
Name VARCHAR(100));

CREATE TABLE PersonAccessory(
PersonID INT NOT NULL ,
AccessoryID INT NOT NULL,
FOREIGN KEY PersonID REFERENCES Person(ID),
FOREIGN KEY AccessoryID REFERENCES Accessory(ID));

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY REFERENCES Person(ID), AccessoryID FOREIGN KEY REFERENCES Accessory(' at line 4

thanks.

mysqltableforeign-key
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered

It looks like you are missing parentheses to wrap the column name after the FOREIGN KEY is spelled out, that is all. In other words, this should do it:

CREATE TABLE PersonAccessory(
    PersonID INT NOT NULL ,
    AccessoryID INT NOT NULL,
    FOREIGN KEY (PersonID) REFERENCES Person(ID),
    FOREIGN KEY (AccessoryID) REFERENCES Accessory(ID)
);

Also, your bridge table named PersonAccessory ended up without the primary key. Was it by design? If not then you might want to consider a composite primary key, i.e

primary key (PersonID, AccessoryID)

as a part of the table definition.Hope this helps.

Oleg

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.