Weird syntax error in MySQL Workbench 6.3

My code reads as follows:

CREATE DATABASE IF NOT EXISTS store;

CREATE TABLE IF NOT EXISTS customer (

customer_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

first_name VARCHAR(20) NOT NULL,

middle_name VARCHAR(20),

last_name VARCHAR(40) NOT NULL,

email VARCHAR(60) NOT NULL,

usr_pword CHAR(10) NOT NULL,

reg_date DATE,

PRIMARY KEY (customer_id),

FOREIGN KEY (rental_id) REFERENCES (rental_id),

UNIQUE (email)

);

CREATE TABLE IF NOT EXISTS rental (

rental_id INT UNSIGNED NOT NULL AUTO_INCREMENT,

date_out DATE NOT NULL,

date_in DATE NOT NULL,

cost INTEGER NOT NULL,

PRIMARY KEY (rental_id),

FOREIGN KEY (customer_id) REFERENCES (customer_id),

FOREIGN KEY (game_id) REFERENCES (game_id)

);

CREATE TABLE IF NOT EXISTS game (

game_id INT UNSIGNED NOT NULL  AUTO_INCREMENT,

game_name VARCHAR(60),

game_publisher VARCHAR(20),

PRIMARY KEY (game_id),

FOREIGN KEY (rental_id) REFERENCES (rental_id)

);

and i get the error: Error code 1064 You have an error in your SQL syntax... near 'rental_id UNIQUE (email));'

Answers


This line has invalid syntax for declaring a foreign key:

FOREIGN KEY (rental_id) REFERENCES (rental_id),

You have to declare the referenced table in any foreign key definition. Then after the table name, declare the referenced column(s) in that table inside parentheses. Example:

FOREIGN KEY (rental_id) REFERENCES `ReferencedTable` (rental_id),

You should learn to read SQL syntax documentation, because it holds the answer to this question and others. For example, http://dev.mysql.com/doc/refman/5.7/en/create-table.html shows the syntax for a foreign key reference:

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

Notice how tbl_name follows the REFERENCES keyword, then after that a parenthesized list of columns.

Checking syntax reference documentation should be your first method of resolving syntax errors like the one you got.


Need Your Help

Div to cover remainder of page only renders to browser height, not content

html css angularjs

I have a design from the UX which must be implemented... Despite me believing we should use a model, I've been told to code what I've been given.