Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to add foreign key contraint to a column in MySql?

    • 0
    • 1
    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 421
    Comment on it

    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)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: