How to add relationships between tables in existing MySQL database?

Question

I have a existing MySQL database which has large number of tables.

But the problem is the relationships between the tables has not been added. How can I add relationships between tables in my existing MySQL database?

possible duplicate of [How to create relationships in mySQL](http://stackoverflow.com/questions/260441/how-to-create-relationships-in-mysql) — DrColossos, Sep 06 '11 at 18:07
Depends on how you're doing this. You probably need to run an constrain query. http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html — Jay, Sep 06 '11 at 18:07
@DrColossos: I think it is different because I don't want to re-create the database I want to give relationships in existing database. So I don't think it is duplicate of that. — Parth Bhatt, Sep 06 '11 at 18:12

score 2 · Accepted Answer · answered Sep 06 '11 at 18:09

2

something like this

ALTER TABLE `table1` ADD CONSTRAINT table1_id_refs FOREIGN KEY (`table2_id`) REFERENCES `table2` (`id`);

answered Sep 06 '11 at 18:09

hvgotcodes

118,147
33
203
236

score 1 · Answer 2 · answered Sep 06 '11 at 18:57

Others have given you the answer as to how implement a constraint in SQl code. but the biggest problme lies in the fact that since you didn't have this constraint already, you may not now have data integrity.

First you identify which records would not meet the constraint. Next you decide what to do about them and fix them, delete them or whatever you decide. Then you create the constraint once the data is clean.

If you already have bad data sometimes the best you can do is add a parent record for "unknown' and attach all the child records that do not have an existing parent to it. For instance if these were actual orders that had financial values attached to them you wouldn't want to delete (that messes up financial reporting) them and then you use the Unknown Customer.

There might be other cases where you can safely delete the information in the field or the whole record. Suppose you wwanted to add a link to a state table with an address table as the child table. Suppose right now it has state 107 and you don't have a state id of 107. Is it better to have an address that can't be used at all (you can't mail to state Unknown for instance) and thus delete the record or to leave the state field blank (because your users can contact the customer and get it filled in and you have a street address you don't want to lose.

Only you can decide for your particular app and what it is used for how to handle currently bad records. And since you have no constraints now, the chances that you have bad records are very high.

score 0 · Answer 3 · answered Sep 06 '11 at 18:08

From the docs The syntax for adding foreign keys constraints to existing tables is

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option

How to add relationships between tables in existing MySQL database?

3 Answers3