469,277 Members | 2,062 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,277 developers. It's quick & easy.

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....
Aug 29 '07 #1
9 4288
pbmods
5,821 Expert 4TB
Heya, AMT.

Are these tables MyISAM or InnoDB?

If you're not sure, try issuing a
Expand|Select|Wrap|Line Numbers
  1. SHOW CREATE TABLE `tableName`\G
.

Look for ENGINE=MyISAM or ENGINE=InnoDB.
Aug 29 '07 #2
Heya, AMT.

Are these tables MyISAM or InnoDB?

If you're not sure, try issuing a
Expand|Select|Wrap|Line Numbers
  1. 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?
Aug 30 '07 #3
pbmods
5,821 Expert 4TB
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
  1. ALTER TABLE `tableName` ENGINE=InnoDB;
  2.  
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.
Aug 30 '07 #4
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
  1. ALTER TABLE `tableName` ENGINE=InnoDB;
  2.  
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?
Aug 31 '07 #5
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)
Sep 1 '07 #6
pbmods
5,821 Expert 4TB
Merged duplicate threads.
Sep 1 '07 #7
pbmods
5,821 Expert 4TB
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.
Sep 1 '07 #8
mwasif
801 Expert 512MB
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.
Sep 2 '07 #9
Thanx a lot for all the replies...
Sep 3 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by Bodza Bodza | last post: by
3 posts views Thread by Alex Satrapa | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.