error in SQL syntax create table

Question

I was trying to feed the following commands to MySQL CLI with , which seems to be good, however when I added one more table, it complained there was an error in syntax.

Here are the commands

CREATE TABLE IF NOT EXISTS User(
        uid INT,
        name VARCHAR(64) UNIQUE,
        birthday date,
        PRIMARY KEY(uid)
) ENGINE = InnoDB ;

CREATE TABLE IF NOT EXISTS UserEmail(
        uid INT,
        email VARCHAR(64),
        PRIMARY KEY(uid, email),
        FOREIGN KEY(uid) REFERENCES User(uid)
);

However if I wanted to add one more table, it said there's an syntax error near the ')' at the line where PRIMARY KEY(uid) lies.

CREATE TABLE IF NOT EXISTS User(
        uid INT,
        name VARCHAR(64) UNIQUE,
        birthday date,
        PRIMARY KEY(uid)
) ENGINE = InnoDB ;

CREATE TABLE IF NOT EXISTS UserEmail(
        uid INT,
        email VARCHAR(64),
        PRIMARY KEY(uid, email),
        FOREIGN KEY(uid) REFERENCES User(uid)
);

CREATE TABLE friendship(
        invite_uid  INT,
        accept_uid  INT,
        start_date  DATE,
        PRIMARY KEY(invite_uid, accept_uid),
        FOREIGN KEY(invite_uid) REFERENCES User(uid),
        FOREIGN KEY(accept_uid) REFERENCES User(uid),
);

Not sure where went wrong since the error was not complained in the newly added command.

--UPDATE--

Well that was fixed, but there's one more problem. Adding the following table throws

cannot create table "estore.contains" (errorno: 150)

CREATE TABLE contains(
        uid INT,
        wid INT,
        pid INT,
        PRIMARY KEY(uid, wid, pid),
        FOREIGN KEY(uid) REFERENCES User(uid),
        FOREIGN KEY(wid) REFERENCES Wishlist(wid),          
        FOREIGN KEY(pid) REFERENCES Product(pid)        
);

--UPDATE 2--

Full Tables that I want to add

CREATE TABLE IF NOT EXISTS User(
        uid INT,
        name VARCHAR(64) UNIQUE,
        birthday date,
        PRIMARY KEY(uid)
) ENGINE = InnoDB ;

CREATE TABLE IF NOT EXISTS UserEmail(
        uid INT,
        email VARCHAR(64),
        PRIMARY KEY(uid, email),
        FOREIGN KEY(uid) REFERENCES User(uid)
);

CREATE TABLE friendship(
        invite_uid  INT,
        accept_uid  INT,
        start_date  DATE,
        PRIMARY KEY(invite_uid, accept_uid),
        FOREIGN KEY(invite_uid) REFERENCES User(uid),
        FOREIGN KEY(accept_uid) REFERENCES User(uid)
);  

CREATE TABLE Seller(
        sid INT,
        name VARCHAR(64),
        PRIMARY KEY(sid)
);

CREATE TABLE Product(
        pid INT,
        sid INT,
        name VARCHAR(64),
        description TEXT,
        price DOUBLE,
        PRIMARY KEY(pid),
        FOREIGN KEY(sid) REFERENCES Seller(sid)
);

CREATE TABLE buy(
        uid INT,
        pid INT,
        time DATE,
        PRIMARY KEY(uid, pid),
        FOREIGN KEY(uid) REFERENCES User(uid),
        FOREIGN KEY(pid) REFERENCES Product(pid)
);

CREATE TABLE Wishlist(
        uid INT,
        wid INT,
        start_time DATE,
        end_time DATE,
        PRIMARY KEY(uid,wid),
        FOREIGN KEY(uid) REFERENCES User(uid)
);

CREATE TABLE conntains(
        uid INT,
        wid INT,
        pid INT,
        PRIMARY KEY(uid, wid, pid),
        FOREIGN KEY(uid) REFERENCES User(uid),
        FOREIGN KEY(wid) REFERENCES Wishlist(wid),          
        FOREIGN KEY(pid) REFERENCES Product(pid)        
)ENGINE = InnoDB;

Anyone could help? Thx

Please add Table Structure for Wishlist and Product. Also use ENGINE = InnoDB for all of your tables As foreign key will not be created when table is non-innodb (Some server have default myisam engine instead of innodb Also It may not throw error for using myisam or not using engine statement ) — kuldeep.kamboj
– kuldeep.kamboj, Commented Oct 31, 2013 at 9:32

MusicLovingIndianGirl · Accepted Answer · 2013-10-31 09:07:28Z

3

Check the last line of your code

FOREIGN KEY(accept_uid) REFERENCES User(uid),

Remove the comma at the end.

answered Oct 31, 2013 at 9:07

MusicLovingIndianGirl

5,9459 gold badges38 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pascalz · Accepted Answer · 2013-10-31 09:06:41Z

2

remove , a end of line

FOREIGN KEY(accept_uid) REFERENCES User(uid),
--------------------------------------------^

answered Oct 31, 2013 at 9:06

Pascalz

2,3581 gold badge24 silver badges25 bronze badges

Comments

Kevin Bowersox · Accepted Answer · 2013-10-31 09:08:54Z

2

There is an extra comma on the third table.

CREATE TABLE IF NOT EXISTS User(
        uid INT,
        name VARCHAR(64) UNIQUE,
        birthday date,
        PRIMARY KEY(uid)
) ENGINE = InnoDB ;

CREATE TABLE IF NOT EXISTS UserEmail(
        uid INT,
        email VARCHAR(64),
        PRIMARY KEY(uid, email),
        FOREIGN KEY(uid) REFERENCES User(uid)
);

CREATE TABLE friendship(
        invite_uid  INT,
        accept_uid  INT,
        start_date  DATE,
        PRIMARY KEY(invite_uid, accept_uid),
        FOREIGN KEY(invite_uid) REFERENCES User(uid),
        FOREIGN KEY(accept_uid) REFERENCES User(uid), <---- Extra comma
);

answered Oct 31, 2013 at 9:08

Kevin Bowersox

94.6k19 gold badges164 silver badges198 bronze badges

Comments

Sathish D · Accepted Answer · 2013-10-31 09:59:34Z

0

FOREIGN KEY(accept_uid) REFERENCES User(uid),<-- Comma should be removed

Try this:

CREATE TABLE friendship(
        invite_uid  INT,
        accept_uid  INT,
        start_date  DATE,
        PRIMARY KEY(invite_uid, accept_uid),
        FOREIGN KEY(invite_uid) REFERENCES User(uid),
        FOREIGN KEY(accept_uid) REFERENCES User(uid)
);

answered Oct 31, 2013 at 9:59

Sathish D

5,04433 silver badges46 bronze badges

Comments

MusicLovingIndianGirl · Accepted Answer · 2013-10-31 09:26:33Z

0

@Daniel, you cannot use the name CONTAINS for a table since its an SQL keyword. Please create the table with some other name.

answered Oct 31, 2013 at 9:26

MusicLovingIndianGirl

5,9459 gold badges38 silver badges65 bronze badges

3 Comments

Daniel Over a year ago

No I guess it isn't the cause, changing it to another name doesn't solve the problem. I think it's related to FK, but not sure exactly how

MusicLovingIndianGirl Over a year ago

Daniel, you can use only 1 Primary key for a table. That should be causing the issue for you.

Daniel Over a year ago

but what if I have to use a combined primary key. I have updated my question to make the context clearer.As you can see, the referenced Product(wid) is part of its primary key combination (wid,uid)

Daniel · Accepted Answer · 2013-10-31 09:50:18Z

0

Fortunately I figured the second problem out, the foreign key was not made right, I need to use

        FOREIGN KEY(uid, wid) REFERENCES Wishlist(uid, wid),

to refer the the primary key combination in Wishlist

answered Oct 31, 2013 at 9:50

Daniel

1,5746 gold badges24 silver badges45 bronze badges

Collectives™ on Stack Overflow

error in SQL syntax create table

6 Answers 6

Comments

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related