table of foreign key attributes

Question

question

shirts asked Oct 16, '19

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.

mysql table foreign-key

10 |1200

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

Answer 1 · 2019-10-17T20:56:21Z

Oleg answered Oct 17, '19

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

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

question