MySQL Forums :: Newbie :: Alter table add foreign key causes can't create table error


Advanced Search

Alter table add foreign key causes can't create table error
Posted by: Andrew Cutforth ()
Date: December 20, 2013 07:42AM

I am creating two tables then doing an alter table to add a foreign key constraint and it gives the following error:
Error Code: 1005. Can't create table 'mydb.#sql-870_16' (errno: 150)

Here is a simple test to prove it:

CREATE TABLE `test` (
`id` binary(16) NOT NULL,
`description` varchar(100) NOT NULL,
`main_contact` binary(16) NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `test2` (
`id` binary(16) NOT NULL,
`description` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
);

-- This command fails
alter table test add constraint test_ri foreign key (main_contact) references test2;

Does anyone know what is wrong?

Thanks

Options: ReplyQuote


Subject Written By Posted
Alter table add foreign key causes can't create table error Andrew Cutforth 12/20/2013 07:42AM
Re: Alter table add foreign key causes can't create table error Peter Brawley 12/20/2013 09:52AM
Re: Alter table add foreign key causes can't create table error pia D'Alia 12/20/2013 10:33AM
Re: Alter table add foreign key causes can't create table error Rick James 12/20/2013 11:49AM
Re: Alter table add foreign key causes can't create table error Andrew Cutforth 12/23/2013 06:34AM


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.