In many cases we need to create foreign key in a table. We can do this at the time of table creation and after the table creation by ALTER command.
1- At the time of table creation:
CREATE TABLE user_device (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
device_id varchar(45) NOT NULL,
device_type varchar(45) DEFAULT NULL,
PRIMARY KEY (id),
KEY user_detail_user_fk_idx (user_id),
CONSTRAINT user_detail_user_fk_idx FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2- After table creation: here I'm adding foreign key constraint to "user_id" column of "user_device" table. It references to "id" column of "user" table.
ALTER TABLE `user_device` ADD CONSTRAINT `user_detail_user_fk_idx` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
Hope this will help you :)
0 Comment(s)