how to add a foreign key in mysql (connector with python)

Question

i have this part of a python script to create tables into a mysql db

#Product
TABLES['product'] = (
"CREATE TABLE product"
 "(prod_id smallint,"
  "name varchar(255),"
  "price int,"
  "constraint pk_prod_id primary key (prod_id)"
 ");")

#Sales
TABLES['sales'] = (
"CREATE TABLE sales"
 "(sales_id smallint,"
  "name varchar(255),"
  "quantity int,"
  "buy_id smallint,"
  "date DATE,"  
  "constraint pk_sales_id primary key (sales_id,name)"
 ");")
#Purchase
TABLES['purchase'] = (
"CREATE TABLE purchase"
 "(purchase_id smallint,"
  "name varchar(255),"
  "quantity int,"
  "sup_id smallint,"
  "date DATE,"  
  "constraint pk_purchase_id primary key (purchase_id,name)"
  ");")

# Adding foreign key
query =  'ALTER TABLE sales ADD foreign key(buy_id) references buyers(buy_id)'
cursor.execute(query)
query = 'ALTER TABLE purchase ADD foreign key(sup_id) references suppliers(sup_id)'
cursor.execute(query)

Until here it works ok, but here is the main problem.

query = 'ALTER TABLE sales ADD foreign key(name) references product(name)'
cursor.execute(query)
query = 'ALTER TABLE purchase ADD foreign key(name) references product (name)'
cursor.execute(query)

the error code is 1215, so can't add foreign keys

It works if i do this

query = ('ALTER TABLE sales ADD foreign key(prod_id) references product(prod_id)')
cursor.execute(query)
query = ('ALTER TABLE purchase ADD foreign key(prod_id) references product(prod_id)')
cursor.execute(query)

but i prefer to work with name and not with prod_id because it's a mess.

how can i solve this issues ? i tried with int type, but i don't like this solution, moreover i have to rewrite a lot of query.

nbk · Accepted Answer · 2020-05-04 01:16:22Z

1

Make the table product like this with KEY (name)

#Product
TABLES['product'] = (
"CREATE TABLE product"
 "(prod_id smallint,"
  "name varchar(255),"
  "price int,"
  "constraint pk_prod_id primary key (prod_id),"
  "KEY (name)"
 ");")

And you can without probems add a foreign key to product name

answered May 4, 2020 at 1:16

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

1 Comment

Ares Bertelli Over a year ago

it works, but i have to rewrite all the query, for istance : without adding the previous foreign key i could use this :

Collectives™ on Stack Overflow

how to add a foreign key in mysql (connector with python)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related