How to add a foreign key in an existing table?


Problem :

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.

Thanks



Solution :

ALTER TABLE Syntax

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);

Mysql Tutorials

Mysql Howto..

How to reinstate auto increment during importation of CSV file into MySql while retaining pervious numbering?

How to store & process currencies in mysql / zend framework

How to find all upper case strings in a MySQL table?

How to wait for MySQL To Update in VB.NET?

MySQL how to group values not specified in the WHERE clause as “others”?

how to get accurate or (near accurate) distance between two places? Mysql/PHP

How to insert utf-8 mb4 character(emoji in ios5) in mysql?

How to update serialize data in MySQL

How to implement a nested comment system?

Two Queries on same table showing same row with different values?

How to making new table in mysql using php when we have add more field button in form

How to Compare Huge Array to Database (with PHP or SQL)

how insert data from multi select in mysql [closed]

how to reverse mysql table

How to integrate free or reduced products in mysql scheme?

EF4.1/MVC3 Database First: How to remove password from connection string

how to migrate all of the wordpress content from one host to another?

MySQL - How to execute a transaction bit by bit, rather than all at one (like a loop)

How to Save an Append or Delete Query in MySQL

How to perform MySQL 'SUM' but to '0' decimal places?

how to increase performance of mysql query?

MYSQL – how to remove characters from the middle of a string

How do I see high-precision query times in mysql command line?

PHP & MySQL: how to narrow down the tag search result?

How to synchronize flow of action in java web application using struts?

How to Use Mathematic Equations as Filters in SQLAlchemy

In MySQL, how to JOIN two very large tables which both have columns in the WHERE condition?

Mysql how to copy data from one field to other field in same table

How to check MySQL cache lifetime?

On hover show image from data attribute from google image search