SQL Foreign key constraints

Question

I have one employee table:

create table employee
(number integer primary key,
 name varchar(20),
 salary integer,
 manager integer,
 birthyear integer,
 startyear integer);

Where the manager column is the employee number of the employees manager, i.e. a couple of rows would look something like this:

number | name               | salary | manager | birthyear | startyear |

    32 | Smythe, Carol      |   9050 |     199 |      1929 |      1967 |
    33 | Hayes, Evelyn      |  10100 |     199 |      1931 |      1963 |
    35 | Evans, Michael     |   5000 |      32 |      1952 |      1974 |

So to clarify, Michael Evans manager is Carol Smythe. And two more things, there are no foreign key constraints on this table and there are a couple of NULL values in the manager column. Now, I would like to create a Managers table which contains all managers. I would do something like this;

create table Mgr(
 Mgr_id INTEGER PRIMARY KEY,
 bonus INTEGER,
 FOREIGN KEY (Mgr_id) REFERENCES employee(manager));

BUT; this doesn´t work and I get an error. Can someone please explain why? Have searched for an answer but can´t find any good explanation. Thanks in advance.

ERROR:

ERROR 1005 (HY0000): Can´t create table johnson.mgr (errno: 150)

ERROR 1005 (HY0000): Can´t create table johnson.mgr (errno: 150) — user2069136, Feb 14 '13 at 11:00
my database is called johnson so I would assume that the table gets the place johnson.mgr or something. — user2069136, Feb 14 '13 at 11:02
I believe you should create your foreign key referencing field "number" instead of field "manager" — vkamayiannis, Feb 14 '13 at 11:05
Referencing "number" works perfectly but the reason I want to have "manager" as reference is because I want to make sure that the tuples in the manager table actually are managers and not just employees. — user2069136, Feb 14 '13 at 11:07
Have a look in here: http://stackoverflow.com/questions/2799021/mysql-error-1005-hy000-cant-create-table-foo-sql-12c-4-errno-150 — vkamayiannis, Feb 14 '13 at 11:10
Actually if you don't want to reference the primary key, add an index to the "manager" field — vkamayiannis, Feb 14 '13 at 11:13

Meherzad · Answer 1 · 2013-02-14T11:59:56.260

The Corresponding columns in the foreign key and the referenced key must have similar internal data types so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

So check whether you are giving a proper datatype for managerId table.

These conditions must be satisfied to not get error 150:

The two tables must be ENGINE=InnoDB.
The two tables must have the same charset.
The PK column(s) in the parent table and the FK column(s) must be the same data type.
The PK column(s) in the parent table and the FK column(s), if they have a define collation type, must have the same collation type;
If there is data already in the foreign key table, the FK column value(s) must match values in the parent table PK columns.

One more problem is that you are creating foreign key and primary key on same column

Hope this helps

Refer http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html

Ahaa, then that´s why. My manager table does contain both NULL values and duplicates which isn´t that suitable for a candidate key. Anyone knows why anyway? Maybe I just want to make sure that the tuple does exist in another table column? Thanks anyway! — user2069136, Feb 14 '13 at 11:54
In mysql it is allowed not in sqlserver check the refernece link — Meherzad, Feb 14 '13 at 11:56
@user2069136 minesh is correct you cannot create primary key and foreign key on same column. — Meherzad, Feb 14 '13 at 11:59

score 0 · Answer 2 · answered Feb 14 '13 at 11:52

0

You are trying to create Primary key and Foreign key on same field and table.

You need to change definition of employee table like below,

create table employee
(number integer primary key,
 name varchar(20),
 salary integer,
 manager integer,
 birthyear integer,
 startyear integer
FOREIGN KEY (manager) REFERENCES Mgr(Mgr_id) 
);

And remove Foreign Key clause from Mgr table definition

answered Feb 14 '13 at 11:52

Minesh

2,284
1
14
22

But this arrangement wouldn´t mean that all managers in the managers table are managers, it would mean that all managers in the employee table are managers in the managers table which is the other way around. – user2069136 Feb 14 '13 at 11:59
Please revisit the schema, it sounds confusing to me – Minesh Feb 14 '13 at 12:05

score 0 · Answer 3 · answered Feb 14 '13 at 12:32

What you want to do makes sense. How you're doing it doesn't. You need to change the way your database identifies managers. Right now, it identifies managers by the values in employee.manager. When you're finished, it will identify managers by the rows in Mgr.

Make sure you have usable data first. Look for id numbers that are no longer in the employee table. Try this.

select manager 
from employee 
where manager not in (select number 
                      from employee);

Every row returned represents an error--a manager's id number for which there is no corresponding row in employee. You have to fix those before you can make much progress.

After you fix those errors, create the table Mgr.

create table Mgr (
  Mgr_id INTEGER PRIMARY KEY,
  bonus INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (Mgr_id) REFERENCES employee(manager)
);

Populate Mgr with a query. Something along these lines should work.

insert into Mgr (Mgr_id) 
select distinct manager 
from employee;

Update that table with the correct value for bonus. You might have to do that manually if you don't have that data stored somewhere handy.

As far as the database is concerned now, you could drop the column employee.manager. And you should drop that column, but not now. You have to consider what will happen to application code that thinks you can identify managers by looking at the employee table.

You can just drop the column, and let application programs fail until they're fixed.
You can warn all the application developers that these changes will be made on, say, March 1, and they'd better get their code ready.
You can make the changes, leave the column in place, warn the application developers, and take steps to prevent changes to employee.manager.
You can make these changes, rename the employee table, and create an updatable view having the old structure (you'll need to join employee and Mgr) and the name "employee". This option is close to ideal--it requires no changes to application code--but I'm not sure to what degree MySQL supports updatable views. It might not be possible.

You´re completely right, I do want to identify my managers with my manager table and reference that table from the employee table. Anything else would only be strange. However, there are no tuples in the employee.manager table that doesn´t exist in the employee.number table. There are though a couple of NULL values. Have tried to change these to a 0 for example but still it doesn´t work. Cannot drop the employee.manager either since that´s the way to see who´s manager to who. Thanks anyway, think I have to look over the schema. — user2069136, Feb 15 '13 at 23:23

SQL Foreign key constraints

3 Answers3