CODEWITHSUNDEEP
mysqlsqlmysql-error-1064
Ed_
Ed_

Reputation: 69

FOREIGN KEY constraint help Mysql

I'm reviewing mysql and I am working on composing foreign keys when creating tables. However when I create the foreign key I receive a syntax error. 

CREATE TABLE IF NOT EXISTS staff (
    staff_id INT(5) NOT NULL,
    staff_first_name VARCHAR(20) NOT NULL,
    staff_last_name VARCHAR(20) NOT NULL,
    staff_phone_number VARCHAR(15) NOT NULL,
    staff_email_address VARCHAR(30) NOT NULL,
    CONSTRAINT staff_id_pk PRIMARY KEY (staff_id)
    CONSTRAINT staff_id_fk FOREIGN KEY (staff_id)
    REFERENCES computer_staff (staff_id)
);

Can anyone see what I've done wrong here?

EDIT:

The error I receive reports as:

19:35:55 CONSTRAINT staff_id_fk FOREIGN KEY (staff_id), REFERENCES computer_staff (staff_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 'CONSTRAINT staff_id_fk FOREIGN KEY (staff_id), REFERENCES computer_staff (staff_' at line 1 0.00025 sec

UPDATE: Error Code: 1215. Cannot add foreign key constraint 0.110 sec

How does one resolve error code 1215??

Upvotes: 1

Views: 782

Answers (2)

ishfaq khattak
ishfaq khattak

Reputation: 186

Try this code

CREATE TABLE IF NOT EXISTS staff (
staff_id INT(5) NOT NULL,
staff_first_name VARCHAR(20) NOT NULL,
staff_last_name VARCHAR(20) NOT NULL,
staff_phone_number VARCHAR(15) NOT NULL,
staff_email_address VARCHAR(30) NOT NULL,
Foregn_Key int,
CONSTRAINT staff_id_pk PRIMARY KEY (staff_id),
CONSTRAINT staff_id_fk FOREIGN KEY (Foregn_Key)
REFERENCES computer_staff (Foregn_Key)
);

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

You are missing comma:

CREATE TABLE IF NOT EXISTS staff (
    staff_id INT(5) NOT NULL,
    staff_first_name VARCHAR(20) NOT NULL,
    staff_last_name VARCHAR(20) NOT NULL,
    staff_phone_number VARCHAR(15) NOT NULL,
    staff_email_address VARCHAR(30) NOT NULL,
    CONSTRAINT staff_id_pk PRIMARY KEY (staff_id), -- here
    CONSTRAINT staff_id_fk FOREIGN KEY (staff_id)
    REFERENCES computer_staff (staff_id)
);

Upvotes: 2

Related Questions