MySQL Forums
Forum List  »  Newbie

manually add foreign key
Posted by: Manah Manah
Date: September 20, 2022 04:07AM

HI. i'm trying to add a foreign key to existing tables. i get error message. below is my tables and the error message.

how to fix this please?

mysql> show tables;
+--------------------+
| Tables_in_customer |
+--------------------+
| cust_table |
| prod_table |
| sales_table |
+--------------------+

mysql> desc cust_table;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| cust_id | int | NO | PRI | NULL | auto_increment |
| last_name | varchar(45) | YES | | NULL | |
| first_name | varchar(45) | YES | | NULL | |
| address | varchar(45) | YES | | NULL | |
| email | varchar(45) | YES | | NULL | |
+------------+-------------+------+-----+---------+----------------+

mysql> desc prod_table;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| prod_id | int | NO | PRI | NULL | auto_increment |
| prod_name | varchar(45) | YES | | NULL | |
| prod_details | varchar(45) | YES | | NULL | |
+--------------+-------------+------+-----+---------+----------------+

mysql> desc sales_table;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sales_id | int | NO | PRI | NULL | |
| prod_id | varchar(45) | YES | | NULL | |
| cust_id | varchar(45) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+


mysql> alter table sales_table add foreign key (cust_id) references cust_table(cust_id);
ERROR 3780 (HY000): Referencing column 'cust_id' and referenced column 'cust_id' in foreign key constraint 'sales_table_ibfk_1' are incompatible.

Options: ReplyQuote


Subject
Written By
Posted
manually add foreign key
September 20, 2022 04:07AM
September 20, 2022 08:32AM
September 20, 2022 11:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.