CODEWITHSUNDEEP
mysqlsql
user1058272
user1058272

Reputation: 113

MySQL statement to alter a table fails to work

I would like to change the child table by offering it a foreign key that references the parent over its id like this

CREATE TABLE PARENT (parentID int not null auto_increment primary key,...);
CREATE TABLE CHILD (childID int not null auto_increment primary key, parentID int not null,...);

Now the statement to fix the created table is

ALTER TABLE child CHANGE parentID parentID int not null foreign key references parent(childID) ON DELETE CASCADE;

This fails to work. Any help is appreciated. Thanks.

Upvotes: 0

Views: 69

Answers (3)

araqnid
araqnid

Reputation: 133432

Just say:

ALTER TABLE `CHILD`
  ADD FOREIGN KEY (parentId) REFERENCES `PARENT`(parentId) ON DELETE CASCADE;

There's no need (afaik) to change the column type as it's already correct.

Upvotes: 1

Fuzzy Analysis
Fuzzy Analysis

Reputation: 3188

Your last line has the wrong column name reference for the foreign key, should reference parentID instead of childID. You also have some syntax errors in there.

Try this instead:

ALTER TABLE child CHANGE parentID parentID int not null, 
add foreign key (parentID) references parent(parentID) ON DELETE CASCADE;

Upvotes: 0

dbrumann
dbrumann

Reputation: 17166

Please take a look at MSDN Library regarding T-SQL-Syntax for ALTER. You probably want something like:

ALTER TABLE Child ALTER COLUMN...

Upvotes: 0

Related Questions