Why am I getting jest errors when creating this table?-CodePudding

I'm trying to use primary key for more than one place; however, in my table I get jest error about multiple primary key defined-get.

Below is my code:

create table orders (
 order_id int AUTO_INCREMENT, 
 ordered_from int, 
 PRIMARY KEY (order_id), 
 FOREIGN KEY (ordered_from) REFERENCES restaurants(restaurants_id), 
 delivery_man int, 
 PRIMARY KEY (order_id), 
 FOREIGN KEY (delivery_man) REFERENCES delivers(deliver_id), 
 user_num int, 
 PRIMARY KEY (order_id), 
 FOREIGN KEY (user_num) REFERENCES users(user_id)
);

Please help me out. What would be the right Syntax?

I'm trying to use the id from all of my tables in one table of the order.

In orders table, Im trying to put the user, delivery man, and the restaurant.

Thanks.

CodePudding user response：

create table orders (
 order_id int AUTO_INCREMENT, 
 ordered_from int, 
 PRIMARY KEY (order_id), 
 FOREIGN KEY (ordered_from) REFERENCES restaurants(restaurants_id), 
 delivery_man int, 
 PRIMARY KEY (order_id), 
 FOREIGN KEY (delivery_man) REFERENCES delivers(deliver_id), 
 user_num int, 
 PRIMARY KEY (order_id), 
 FOREIGN KEY (user_num) REFERENCES users(user_id)
)

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The restaurants_id, deliver_id and user_id needs to be primary key in the restaurants, delivers and users tables respectively.

Change the above declaration syntax as mentioned below

CREATE TABLE orders (
 order_id int AUTO_INCREMENT, 
 ordered_from int,
 delivery_man int, 
 user_num int, 
 PRIMARY KEY (order_id), 
 FOREIGN KEY (ordered_from) REFERENCES restaurants(restaurants_id), 
 FOREIGN KEY (delivery_man) REFERENCES delivers(deliver_id), 
 FOREIGN KEY (user_num) REFERENCES users(user_id)
);