Michael: One thing to keep in mind with Richard's answer is that when you recreate the key with the third column, you are no longer guaranteeing uniqueness on the original two column key.
Any tables referencing the old key could become corrupt (in the data sense) if a second identical entry becomes available. EG If you have in the table in question a key of (a,b) now you add the new column... you could have a key of (a,b,c) and (a,b,d). Any existing data will not know which item to reference if you need it to reference only one. So, give a need for uniqueness in the referencing tables you will need to accommodate the new key in each of them as well. If it is OK to get more than one row back from the FK reference then you can ignore the concern. Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. Life is not a journey to the grave arriving safely in a well preserved body, but rather a skid in broadside, thoroughly used, totally worn, and loudly proclaiming: "WOW! What a ride!" -----Original Message----- From: Richard Davey [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 03, 2004 7:53 AM To: [EMAIL PROTECTED] Subject: Re: Changing the primary key Hello Michael, Wednesday, March 3, 2004, 1:40:00 PM, you wrote: RM> I have a table, that has a primary key with two columns and want to add a RM> third column to this primary key. RM> Is this possible and when yes: Do I have to delete all tables, that RM> reference to this table? No, you don't have to delete all tables that reference this table. You can drop the key: ALTER TABLE tablename DROP PRIMARY KEY and then re-create it: ALTER TABLE tablename ADD PRIMARY KEY (a,b,c) Please note that if you have a field with a property such as "auto-increment" then dropping the primary key will fail because it will leave an invalid table definition. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
