Following is an example of creating a database and then adding to it a foreign key constraint
mysql> create database dbdemo;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE vendors(
-> vdr_id int not null auto_increment primary key,
-> vdr_name varchar(255)
-> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
CREATE TABLE categories(
-> cat_id int not null auto_increment primary key,
-> cat_name varchar(255) not null,
-> cat_description text
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE products(
-> prd_id int not null auto_increment primary key,
-> prd_name varchar(355) not null,
-> prd_price decimal,
-> cat_id int not null,
-> FOREIGN KEY fk_cat(cat_id)
-> REFERENCES categories(cat_id)
-> ON UPDATE CASCADE
-> ON DELETE RESTRICT
-> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE products
-> ADD COLUMN vdr_id int not null AFTER cat_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE products
-> ADD FOREIGN KEY fk_vendor(vdr_id)
-> REFERENCES vendors(vdr_id)
-> ON DELETE NO ACTION
-> ON UPDATE CASCADE;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> describe City;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| CityName | varchar(30) | YES | | NULL | |
| From_There | varchar(20) | NO | PRI | | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from City;
+--------------------+-------------------+
| CityName | From_There |
+--------------------+-------------------+
| Madrid , Spain | Mike JR |
| Sydney, Australia | Jill Engelstein |
| San Francisco, USA | John Smith |
| Hyderabad, India | Sudhir Srinivasan |
+--------------------+-------------------+
4 rows in set (0.00 sec)
mysql> describe people;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| email | varchar(20) | NO | PRI | | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from people;
+-------------------+-------------------+
| name | email |
+-------------------+-------------------+
| Jill Engelstein | [email protected] |
| John Smith | [email protected] |
| Mike JR | [email protected] |
| Sudhir Srinivasan | [email protected] |
+-------------------+-------------------+
4 rows in set (0.00 sec)
I am trying to create a foreign key:
mysql> alter table people
-> add foreign key fk_name(name)
-> references City(From_There)
-> on delete no action
-> on update cascade;
ERROR 1215 (HY000): Cannot add foreign key constraint
What is wrong?