multiple one-to-one relationship mysql

April 18, 2022

is it possible to create table with multiple one-to-one relationships?
I am tring to obtain it via below query but get SQL error.

Tables are:
Order:

id
cart id (1:1)
user id (1:1)
payment_method_id
shipping_method_id
total price

User

id
email
phone
first name
last name
address
post code
city
password NULL

Cart:

id
cookie
cartItem_id(1:many)
grandTotal

I would like to create table Order with two columns having one-to-one association.

create table order
( id int auto_increment
, cart_id int
, user_id int
, payment_method_id int
, shipping_method_id int
, total_price int
, primary key(user_id)
, primary key(cart_id));

>Solution :

Firstly, You can’t have more than one Primary key on a Table.
Secondly, To have a relation you need to use Foreign Key

Try below Query:

create table order
( id int auto_increment
  , cart_id int
  , user_id int
  , payment_method_id int
  , shipping_method_id int
  , total_price int
  , primary key(id)
  , FOREIGN KEY (user_id) REFERENCES User(Id)
  , FOREIGN KEY (cart_id) REFERENCES Cart(Id));