Sunday, July 28, 2013

Syntax error when making a foreign key in MySQL

In DERBY, I create a table which have reference on another table with following query:
CREATE TABLE authority (
 username VARCHAR(10) NOT NULL,
 authority VARCHAR(10) NOT NULL,
 foreign key(username) references users(username)
);

DERBY have no problem with this query but in MySQL there is a syntax error in foreign key. If this going to execute in MySQL, changes need to be done as follow:
CREATE TABLE authority (
 username VARCHAR(10) NOT NULL,
 authority VARCHAR(10) NOT NULL,
 constraint fk_username foreign key(username) references users(username)
);

No comments: