Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account

Can't create foreign key on mysql table

⇧
0
⇩
46 views

When I try this

ALTER TABLE Comp_Entry
ADD FOREIGN KEY(CompetitionID) REFERENCES Comp__Competition(CompetitionID)

I get this error

#1005 - Can't create table 'chris_db.#sql-1464b_66' (errno: 150) (<a href="server_engines.php?engine=InnoDB&amp;page=Status&amp;token=7a7f690fbd1413e74979d79fa9044fb0">Details...</a>)

My Comp_Competition table has CompetitionID set as the primary key. Is anyone familiar with this problem?

mysql sql foreign-keys
asked March 23, 2011 11:00 pm CDT
Chris Paynter
posted via StackOverflow

2 Answers

⇧
0
⇩
 

Your Comp_Entry table needs an existing index for CompetitionID. Also, your command has a double __ characters in the name for table Comp__Competition, but in your explanation you have only one. Which one is right?

answered March 23, 2011 11:23 pm CDT
Ted Hopp
⇧
0
⇩
 

Because there is data in your table which does not feet to your constraint (for example nonexistent keys)

try to make

select * from Comp_Entry where CompetitionID not in (select distinct CompetitionID from Comp__Competition)
answered March 23, 2011 11:23 pm CDT
Yuriy Vikulov

Your answer

Join with account you already have

Sign in with Twitter account
Sign in with Facebook account
Sign in with Google Friend Connect

Preview
Similar questions
Foreign Key Error on MySQL CREATE TABLE statement (err: 150)
March 18, 2011
Foreign key constraints missing from SHOW CREATE TABLE output
December 28, 2010
Can I change the datatype for the "Create Table" field in the Show Create Table function in MySQL?
January 10, 2011
MYSQL inserting records form table A into tables B and C (linked by foreign key) depending on column values in table A
January 7, 2011
Mysql FOREIGN key pointing to same table
March 21, 2011
Mysql foreign key for entire table
March 19, 2011
Display MySQL table attributes via a foreign key relationship using PHP
March 12, 2011
MySQL Multiple foreign keys pointing to same table
January 22, 2011
Mysql multi table foreign keys? - beginner
December 25, 2010
Mysql: How to insert values in a table with foreign key reference
January 23, 2011
Adding rows to table with foreign key constraint [MySQL]
March 10, 2011
Create table from select then alter table by adding an auto increment column in mysql
January 11, 2011
Mysql create table based on another table including indexes
January 25, 2011
MySQL, Is it possible to have multiple Foreign key values in one table looking at values from a differnt Table?
March 2, 2011
MySQL create table containing values from a column in another table.
January 28, 2011
Create a enum table and access it from other table in mysql
March 21, 2011
MySQL - Aren't these privileges enough to create a table?
January 3, 2011
How to create index in two table mysql?
January 13, 2011
Mysql how to create optimum query for these table
February 11, 2011
Mysql create a table character sets
January 25, 2011
MySQL table create taking way too long
February 11, 2011
Convert Simple T-SQL DDL to MySQL DDL (Create Table)
January 6, 2011
Foreign-key on a table not in the database, with ORM
December 27, 2010
Get field from first table by foreign key from second
March 3, 2011
Help to creating a formula on mysql to create another column on another table
March 6, 2011
Is there a mysql equivalent of show create table in postgres???
February 6, 2011
How to create mysql table with many hasMany association in CakePHP?
January 18, 2011
Mysql proxy to check create table statement
February 22, 2011
Having a foreign key as the primary key in a child table.
January 31, 2011
Using MySQL, I'm attempting to create a table in a db, and then insert into, but am getting a 'syntax' error
January 13, 2011