MySQL Issues With Syntax - Foreign Keys

Question

Hey just wondering if someone could potentially help me with figure out how i could fix my MySQL work.

The issue appears to be within the 3rd table (tblRoster) as all other tables execute just fine on their own, i am unsure if it is an issue with the Foreign key formatting or something else, sorry i am still pretty new to this

CREATE TABLE tblStaff(
    StaffID INT(10) NOT NULL,
    FirstName VARCHAR(20),
    LastName VARCHAR(25),
    TeamLeader BOOLEAN,
    HourlyRate DECIMAL(2,2),
    PRIMARY KEY(StaffID)
);


CREATE TABLE tblEvent(
    EventID INT(10) NOT NULL,
    EDate DATE,
    ETime TIME,
    PRIMARY KEY(EventID)
);

CREATE TABLE tblRoster(
    RosterID INT(10) NOT NULL,
    Station VARCHAR(15),
    FOREIGN KEY(tblStaff.StaffID),
    FOREIGN KEY(tblEvent.EventID),
    PRIMARY KEY(RosterID)
);

Please Search Properly before posting any question. This question is already asked by someone [https://stackoverflow.com/questions/48772/how-do-i-create-a-foreign-key-in-sql-server/48778] — Creator, May 03 '20 at 12:46

score 1 · Answer 1 · answered May 03 '20 at 12:42

The foreign key declarations needs a references clause. And the columns need to be declared:

CREATE TABLE tblRoster (
    RosterID INT(10) NOT NULL,
    Station VARCHAR(15),
    StaffID INT,
    EventID INT,
    FOREIGN KEY (StaffID) REFERENCES tblStaff(StaffID),
    FOREIGN KEY (EventID) REFERENCES tblEvent(EventID),
    PRIMARY KEY(RosterID)
);

score 0 · Answer 2 · edited May 16 '20 at 23:08

0

You have defile staffid and eventid as well in the create statement when you are referencing the foreign key. And roosterid should be your primary key

CREATE TABLE tblRoster(
RosterID INT(10) NOT NULL,
Station VARCHAR(15),
StaffID INT,
EventID INT,
FOREIGN KEY (StaffID) REFERENCES tblStaff(StaffID),
FOREIGN KEY (EventID) REFERENCES tblEvent(EventID),
PRIMARY KEY(RosterID) 
);

edited May 16 '20 at 23:08

Dharman

30,962
25
85
135

answered May 03 '20 at 12:46

Jim Macaulay

4,709
4
28
53

that is not working in mysql workbench and gives an error – nbk May 03 '20 at 13:11
My bad forgot to provide, reference table. Try now, edited the query – Jim Macaulay May 03 '20 at 13:14
the INT(10) gives a deprecation warning, but now your answer is at least correct – nbk May 03 '20 at 13:36
Integer is default data type, i have provided length by mistake. Rectified the query, try now – Jim Macaulay May 03 '20 at 13:41

MySQL Issues With Syntax - Foreign Keys

2 Answers2