Add foreign key to existing tables

[AMT India]

I am using mysql 4.2. I have a big database with lots of data. Now there is no foreign key relationship between the tables. But every one has a primary key. Can I alter these tables to bring foreign key without affecting the data? Thanx in advance....

[pbmods]

Heya, AMT.

Are these tables MyISAM or InnoDB?

If you're not sure, try issuing a [code=mysql]SHOW CREATE TABLE `tableName`\G[/code].

Look for ENGINE=MyISAM or ENGINE=InnoDB.

[AMT India]

Heya, AMT.

Are these tables MyISAM or InnoDB?

If you're not sure, try issuing a [code=mysql]SHOW CREATE TABLE `tableName`\G[/code].

Look for ENGINE=MyISAM or ENGINE=InnoDB.

Some of them are MyISAM and some are InnoDB...what I have to do?

[pbmods]

Heya, AMT.

Only InnoDB tables properly support foreign key constraints right now. You can change the storage engine for any table by executing this command:
[code=mysql]
ALTER TABLE `tableName` ENGINE=InnoDB;
[/code]

MyISAM is faster, especially for large data sets, than InnoDB. The trade-off is that MyISAM does not support row locking, transactions nor foreign key constraints.

Check out this document for more information on setting up foreign key constraints on an InnoDB table.

[AMT India]

Heya, AMT.

Only InnoDB tables properly support foreign key constraints right now. You can change the storage engine for any table by executing this command:
[code=mysql]
ALTER TABLE `tableName` ENGINE=InnoDB;
[/code]

MyISAM is faster, especially for large data sets, than InnoDB. The trade-off is that MyISAM does not support row locking, transactions nor foreign key constraints.

Check out this document for more information on setting up foreign key constraints on an InnoDB table.

Ok..i will change tables to InnoDB. But can I add foreign key for a table which contains data?

[AMT India]

specify foreign keys for tables with data

I have two tables with lots of data. How can I specify foreign keys for these tables in MySQL 4. (Both tables has primary key)

[pbmods]

Merged duplicate threads.

[pbmods]

Heya, AMT.

Ok..i will change tables to InnoDB. But can I add foreign key for a table which contains data?

You sure can. Simply use the ALTER TABLE command, and add foreign keys the same way you would any other.

[mwasif]

Ok..i will change tables to InnoDB. But can I add foreign key for a table which contains data?

Make sure you do not have FULLTEXT index in any MyISAM table. FULLTEXT is only supported in MyISAM tables.

[AMT India]

Thanx a lot for all the replies...