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

Expand|Select|Wrap|Line Numbers

SHOW CREATE TABLE `tableName`\G

.

Look for ENGINE=MyISAM or ENGINE=InnoDB.

AMT India

Quote:

Originally Posted by pbmods

Heya, AMT.

Are these tables MyISAM or InnoDB?

If you're not sure, try issuing a

Expand|Select|Wrap|Line Numbers

SHOW CREATE TABLE `tableName`\G

.

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:

Expand|Select|Wrap|Line Numbers

ALTER TABLE `tableName` ENGINE=InnoDB;

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

Quote:

Originally Posted by 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:

Expand|Select|Wrap|Line Numbers

ALTER TABLE `tableName` ENGINE=InnoDB;

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

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.

Quote:

Originally Posted by AMT India

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

Quote:

Originally Posted by AMT India

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...

Add foreign key to existing tables

re: Add foreign key to existing tables

re: Add foreign key to existing tables

re: Add foreign key to existing tables

re: Add foreign key to existing tables

re: Add foreign key to existing tables

re: Add foreign key to existing tables

re: Add foreign key to existing tables

re: Add foreign key to existing tables

re: Add foreign key to existing tables

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about