Home > Back-end >  multiple one-to-one relationship mysql
multiple one-to-one relationship mysql

Time:04-19

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));

I copied the below query but I am getting error and don't know why.

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));

it says:

[42000][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 'order(id int auto_increment, cart_id int, user_id int, payment_method_id int, sh' at line 1

What is it I cannot see? I only changed Id into id.

CodePudding user response:

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));
  • Related