MySQL Adding Foreign Key Error 1215

Question

I know such a question is asked before. I made sure that they have the same data type and also checked my syntax, but I am still getting the error:

 ALTER TABLE meetings ADD FOREIGN KEY (ownerName) REFERENCES employees(name);

ERROR 1215 (HY000): Cannot add foreign key constraint

mysql> desc `meetings`;
    +-----------+-------------+------+-----+---------+----------------+
    | Field     | Type        | Null | Key | Default | Extra          |
    +-----------+-------------+------+-----+---------+----------------+
    | id        | int(11)     | NO   | PRI | NULL    | auto_increment |
    | room      | int(6)      | NO   |     | NULL    |                |
    | ownerName | varchar(30) | NO   |     | NULL    |                |
    | ownerID   | varchar(30) | NO   |     | NULL    |                |
    +-----------+-------------+------+-----+---------+----------------+
    4 rows in set (0.00 sec)

    mysql> desc `employees`;
    +----------+--------------+------+-----+---------+-------+
    | Field    | Type         | Null | Key | Default | Extra |
    +----------+--------------+------+-----+---------+-------+
    | name     | varchar(30)  | NO   |     | NULL    |       |
    | username | varchar(30)  | NO   | PRI | NULL    |       |
    | pswd     | varchar(255) | YES  |     | NULL    |       |
    +----------+--------------+------+-----+---------+-------+

What am I doing wrong?

InnoDB permits a foreign key to reference any column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html — P.Salmon, Feb 27 '18 at 09:21
There's a good checklist for foreign keys in the answer to this: https://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 — Bill Karwin, Feb 27 '18 at 16:38

ScaisEdge · Accepted Answer · 2018-02-27T09:21:34.187

3

name is not primary key in employees table so .. try using username

ALTER TABLE meetings ADD FOREIGN KEY (ownerName) REFERENCES employees(username);

or as suggested by DanielE or you can use the column name but need an UNIQUE index for this column

edited Feb 27 '18 at 09:21

answered Feb 27 '18 at 09:14

ScaisEdge

131,976
10
91
107

Agreed but to complete your answer, you can add the foreign key on "name" if it's a PK or Unique in "employees" table. – Daniel E. Feb 27 '18 at 09:18
1

@DanielE. correct .. answer updated . with your right suggestion – ScaisEdge Feb 27 '18 at 09:21

score 0 · Answer 2 · answered Feb 27 '18 at 10:04

0

Change the primary key of 'employees' from user name to name. Then you can use

ALTER TABLE meetings ADD FOREIGN KEY (ownerName) REFERENCES employees(name);

answered Feb 27 '18 at 10:04

Rinsha CP

51
3
11

Paul Campbell · Answer 3 · 2018-02-27T15:47:09.830

What am I doing wrong? Error 1215 is probably the least of the problems here.

Some of the answers given for this question suggest making username the referenced column in employees rather than name, which is fine as far as it goes, but ignores some really fundamental issues in the schema and quite possibly wasn't the poster's intention for these columns. The rest of this answer is based on my own set of assumptions.

meetings table

Looking at the meetings table, I'm left wondering about the purpose of the ownerID column. Since the intention is to have ownerName as a foreign key to employees, what exactly is ownerID? The name suggests it also somehow references employees, but there is no id or ownerID in employees. Also, if any column starting owner... refers to an employee then why would you need both in the meetings table? One of them is surely redundant. Why is ownerID a VARCHAR(30)? ID columns tend to be INT. Of course, I may be reading to much into this and ownerID may have some other purpose that has nothing to do with an employee, but if that's the case the name is likely going to cause confusion in the future.

The meetings table also has an INT surrogate key in id. There's another INT for room. Since room isn't a foreign key, it suggests that rooms are either consistently identified only by number (which would be strange in my experience) and that there is nothing more to a 'room' that's worth capturing (e.g. location, capacity, equipment etc.) to bother with modelling data about the room in a separate table (again unlikely). Alternatively, room might itself be a foreign key referencing an INT id column in an, as yet undefined, rooms table.

employees table

If we accept ownerID as a more appropriate foreign key to the employee that owns the meeting (it uses less memory to index than either name or username) then consistency would suggest another surrogate key id as the primary key in the employees table. It's not necessary to do this, username would be unique and is fine on it's own, but it's simpler and more efficient. The other suggestion made that name should be the PK in employees is wrong - it presupposes that names are always unique.

A single column to cover an employee name would also be unusual.

The point made about referencing a PK or a unique index is well made (even if it's not strictly necessary in Innodb), I'd just say that ownerName is the wrong foreign key and username and name are the wrong references because there is a better alternative.

And, finally, is a NULL password (pswd) a good idea?

MySQL Adding Foreign Key Error 1215

3 Answers3