CODEWITHSUNDEEP
mysqlforeign-keysinnodb
giozh
giozh

Reputation: 10068

declare two foreign key that are also primary key

i'm facing a strange problem by declaring two foreign key on a table, that are also a primary key in my table. This is my sql code:

CREATE TABLE IF NOT EXISTS guest (
    id_guest varchar(50) NOT NULL,
    PRIMARY KEY(id_guest)
) ENGINE=InnoDb DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS product (
    id_product varchar(100) NOT NULL,
    PRIMARY KEY(id_product)
) ENGINE=InnoDb DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS product_guest_resale (
    id_guest varchar(50) NOT NULL,
    id_product varchar(100) NOT NULL,
    amount int(100) NOT NULL, 
    PRIMARY KEY (id_guest, id_product),
    FOREIGN KEY id_guest REFERENCES guest(id_guest),
    FOREIGN KEY id_product REFERENCES product(id_product)
) ENGINE=InnoDb DEFAULT CHARSET=latin1;

when i execute my code, i obtain this error:

#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 'REFERENCES guest(id_guest),
FOREIGN KEY id_product REFERENCES product(' at line 6

what's wrong?

Upvotes: 1

Views: 49

Answers (1)

lp_
lp_

Reputation: 1178

You miss the parenthesis:

CREATE TABLE IF NOT EXISTS product_guest_resale (
    id_guest varchar(50) NOT NULL,
    id_product varchar(100) NOT NULL,
    amount int(100) NOT NULL, 
    PRIMARY KEY (id_guest, id_product),
    FOREIGN KEY (id_guest) REFERENCES guest(id_guest),
    FOREIGN KEY (id_product) REFERENCES product(id_product)
) ENGINE=InnoDb DEFAULT CHARSET=latin1;

Upvotes: 1

Related Questions