MySQL Forums
Forum List  »  InnoDB

ALTER TABLE on foreign column
Posted by: Julio Biason
Date: February 17, 2008 09:15PM

Is there a way to do an ALTER TABLE on several tables, at the same time, to prevent the foreign key from throwing an errno 150?

I have a base table and about 5 other tables with foreign keys pointing to the first one. Now we want to change the size of the column from INT to TINYINT but, when I call the ALTER TABLE, I get the errno 150 (because the column doesn't match on all the tables.)

Say, I have something like this:

TABLE 1 (
id INT NOT NULL PRIMARY KEY,
desc varchar(20)
);

TABLE 2 (
id INT NOT NULL PRIMARY KEY,
table1_id INT NOT NULL,
desc varchar(20),
FOREIGN KEY (table1_id) REFERENCES TABLE1(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);

I'm trying to change TABLE1.id to TINYINT, but it doesn't matter if I change TABLE1.id or TABLE2.table1_id first, I always get the errno 150.

Ideas?

Options: ReplyQuote


Subject
Views
Written By
Posted
ALTER TABLE on foreign column
5931
February 17, 2008 09:15PM
3070
March 14, 2008 09:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.