"Gabriel PREDA" <[EMAIL PROTECTED]> wrote on 03/16/2005 06:12:14 AM:
> > It looks from googling as though I need to drop all foreign key > constraints on this column, perform the change and then reestablish the > foreign keys. Could anyone confirm or advise of a better solution? > > That is the way ! :) > You need to drop the constrains... > Alter `reference` and make `id` INT > You will need to alter the `monogenic` table as well... making it's `id` INT > also... > Recreate constrains... > > Though... I do not know if you need to drop ALL constrains or only the one > that ties the tables: > <this>CONSTRAINT `0_179` FOREIGN KEY (`id`) REFERENCES `reference` > (`id`) ON DELETE CASCADE</this> > > Gabriel PREDA > www.amr.ro > www.lgassociations.info > www.falr.ro > dev.falr.ro Yes, Gabriel is right. You do not have to remove ALL of your foreign constraints, only those that include the column you need to change. Imagine the situation that would occur if you had been able to successfully change the definition of the ID column and you didn't un-define your foreign keys or change any of your other referencing columns. You would have had a relationship that was trying to enforce equality between an int on one side and a smallint on the other. Clearly that would be an illegal FK constraint, right? That's why you were not permitted to change the column as it would have made your existing foreign keys illegal. Follow Gabriel's advice, drop the FKs that reference the column you want to change, modify the fields on BOTH ends of your FKs to have matching datatypes, then re-establish your FKs. It may be a little work but that's just part of the job. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
