Programming in almost language

This is the site where you may share your knowledge and experience to eachother..

  • Categories

  • LinkedIn

  • Tweet Me

  • My footsteps

Add foreign key to existing table

Posted by Praveen Kumar on February 18, 2008

mysql> create table usermaster(user_id int not null,name varchar(10),password varchar(10),primary key(user_id))
    -> engine=INNODB;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table usermaster;
+————+——————————————————————————————————————————————————————————————————-+
| Table      | Create Table                                                                                                                                                                                          |
+————+——————————————————————————————————————————————————————————————————-+
| usermaster | CREATE TABLE `usermaster` (
  `user_id` int(11) NOT NULL,
  `name` varchar(10) default NULL,
  `password` varchar(10) default NULL,
  PRIMARY KEY  (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+————+——————————————————————————————————————————————————————————————————-+
1 row in set (0.00 sec)

mysql> create table userinfo(id int not null,age smallint, sex enum(‘M’,’F’),index(id),foreign key(id)
    -> references usermaster(user_id),primary key(id))engine=INNODB;

Query OK, 0 rows affected (0.02 sec)

lets assume you forget to add foreign key then you can add foreign key to existing table

alter table userinfo ADD Foreign key (id) REFERENCES usermaster(user_id);

mysql> show create table userinfo;
+———-+————————————————————————————————————————————————————————————————————————————————————————————————-+
| Table    | Create Table                                                                                                                                                                                                                                                                                    |
+———-+————————————————————————————————————————————————————————————————————————————————————————————————-+
| userinfo | CREATE TABLE `userinfo` (
  `id` int(11) NOT NULL,
  `age` smallint(6) default NULL,
  `sex` enum(‘M’,’F’) default NULL,
  PRIMARY KEY  (`id`),
  KEY `id` (`id`),
  CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`id`) REFERENCES `usermaster` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+———-+————————————————————————————————————————————————————————————————————————————————————————————————-+
1 row in set (0.01 sec)

mysql> select *from usermaster;
Empty set (0.01 sec)

mysql> select *from userinfo;
Empty set (0.00 sec)

mysql> insert userinfo values(1,23,’M’);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`foo/userinfo`, CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`id`) REFERENCES `usermaster` (`user_id`))
mysql> insert usermaster values(1,’PRAVEEN’,’praveen’);
Query OK, 1 row affected (0.00 sec)

mysql> insert userinfo values(1,23,’M’);
Query OK, 1 row affected (0.01 sec)

NOTE– FOREIGN KEY will not work with MyIsam engine

Default Storage Engine
If you use CREATE TABLE without specifying the ENGINE=… option, the server will use the default. The default storage engine is MyISAM. If you want to change the default to say InnoDB, you can use the configuration directive —default-storage-engine=InnoDB.

Something to be aware of is that if you create a table specifying an engine type that is not enabled, MySQL will automatically fall back to the default. From MySQL 4.1, a warning is issued.

Changing Existing Tables
You can change the storage format of an existing table.

ALTER TABLE t1 ENGINE=InnoDB;
ALTER TABLE t2 ENGINE=HEAP;
These commands make the server create the proper table structure, indexes, and copy all the data. Do note that not all storage engines support all column and index types, so conversion is not practical in all instances.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: