CODEWITHSUNDEEP
mysqlsql
Alex Solair
Alex Solair

Reputation: 1

MySQL Issues With Syntax - Foreign Keys

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)
);

Upvotes: 0

Views: 38

Answers (2)

Jim Macaulay
Jim Macaulay

Reputation: 5141

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) 
);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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)
);

Upvotes: 1

Related Questions