Database

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?