Strange problem on MySQL

Hi.

I'm having problems trying to create this table:

CREATE TABLE Type(
  type varchar(25) NOT NULL,
  CONSTRAINT pk_type PRIMARY KEY (type)
) ENGINE=InnoDB;
CREATE TABLE Games(
  barcode int(10) zerofill NOT NULL,
  name varchar(30) NOT NULL,
  min_age int (3),
  max_age int (3),
  price decimal(3,2) zerofill,
  type varchar(25),
  complement int(10),
  CONSTRAINT pk_games PRIMARY KEY (barcode),
  CONSTRAINT fk_type_game FOREIGN KEY (type) REFERENCES Type(type),
  CONSTRAINT fk_compl_game FOREIGN KEY (complement) REFERENCES Games(barcode)
) ENGINE=InnoDB;

Open in new window


I'm getting error "Cannot add foreign key constraint". If I remove last foreign key then it works, however I can't see any error on that line. Any ideas?
LVL 15
gplanaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
Looks like the last foreign key constraint is pointing to the same table on which you are trying to add it. It should be pointing to the parent table of the constraint, as you do with the first one (pointing to table Type).
0
gplanaAuthor Commented:
There are some cases when you need a foreign key pointing to the same table. For example imagine you have an employee table with an id as primary key, and then you want to know which employee is the boss of every employee, then you have to add a boss field which is a foreign key that point to the id.

I tryied to create the constraint after creating the table, by using ALTER TABLE Games ADD CONSTRAINT... but same result. Any other ideas?
0
Mukesh YadavFull Stack DeveloperCommented:
Just remove zeofill from  
barcode int(10) zerofill NOT NULL,

Open in new window

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
@Gplana thanks for clarifying that. Still somewhat unfamiliar with foreign keys as our systems don't use them.
@Mukesh, just tested that and indeed that works. Can you clarify why that is the case? I apologise for butting in on a thread that's not my own but I assume the poster would like to know this as well.
Thanks.
0
Mukesh YadavFull Stack DeveloperCommented:
This is the rule that both columns must have same datatypes when we are referencing them. Means parent and child fields must have same datatype.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gplanaAuthor Commented:
But they are both int, aren't they? So zerofill is also part of the datatype? Isn't it just for showing results?
0
Mukesh YadavFull Stack DeveloperCommented:
ZEROFILL automatically adds the UNSIGNED attribute to the column. That why you are getting error. Parent is unsigned in and child is signed int. Both are different.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.