How to add a foreign key in an existing table?
I have three tables:
1. User (id, name, birthdate) - PRIMARY KEY ('id')
2. Contact ( id, contact, verified ) - PRIMARY KEY ('id')
3. UserContact ( User_id, Contact_id ) - No Primary Key or Foriegn Key
Problem is that I did not care of primary key or foreign key while creating UserContact table. Therefore I am getting following error while inserting record in UserContact table in zend framework.
A table must have a primary key, but none was found
Now I want to create a MySql script that alter UserContact table and make User_id and Contact_id as foreign keys from User and Contact tables. How to write this script. I am using phpMyAdmin.
Add foreign keys -
ALTER TABLE UserContact ADD CONSTRAINT FK_UserContact_User FOREIGN KEY (user_id) REFERENCES user(id); ALTER TABLE UserContact ADD CONSTRAINT FK_UserContact_Contact FOREIGN KEY (contact_id) REFERENCES user(id);
Add primary key -
ALTER TABLE UserContact ADD PRIMARY KEY (User_id, Contact_id);
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]