MySQL : 오류 1215 (HY000) : 외래 키 제약 조건을 추가 할 수 없습니다.

MySQL : 오류 1215 (HY000) : 외래 키 제약 조건을 추가 할 수 없습니다.

---

나는 Database system concepts , 6th edition, Silberschatz를 읽었습니다 . 저는 MySQL의 OS X에서 2 장에 나온 대학 데이터베이스 시스템을 구현할 것입니다. 하지만 테이블을 만드는 데 문제가 course있습니다. 테이블 department은 다음과 같습니다

mysql> select * from department
    -> ;
+------------+----------+-----------+
| dept_name  | building | budget    |
+------------+----------+-----------+
| Biology    | Watson   |  90000.00 |
| Comp. Sci. | Taylor   | 100000.00 |
| Elec. Eng. | Taylor   |  85000.00 |
| Finance    | Painter  | 120000.00 |
| History    | Painter  |  50000.00 |
| Music      | Packard  |  80000.00 |
| Physics    | Watson   |  70000.00 |
+------------+----------+-----------+

mysql> show columns from department
    -> ;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| dept_name | varchar(20)   | NO   | PRI |         |       |
| building  | varchar(15)   | YES  |     | NULL    |       |
| budget    | decimal(12,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

테이블을 만들면 course다음 오류가 발생합니다.

mysql> create table course
    -> (course_id varchar(7),
    -> title varchar (50),
    -> dept_name varchar(20),
    -> credits numeric(2,0),
    -> primary key(course_id),
    -> foreign key (dept_name) references department);
ERROR 1215 (HY000): Cannot add foreign key constraint

Google에서 외래 키 제약 조건을 검색 한 후 '외래 키 제약 조건'이라는 단어가 테이블의 외래 키 열의 데이터가 테이블의 course기본 키 열에 있어야 함을 나타냅니다 department. 하지만 데이터를 삽입 할 때이 오류를 만났어야합니다.

그렇지 않은 경우 작성자가 SQL 문을 실행하게하는 이유는 무엇입니까?

정말 잘못된 SQL 문을 실행하면 dept_name데이터를 삽입 한 후 코스 테이블에서 외래 키로 지정해야 하나요?

편집 : 입력 set foreign_key_checks=0으로하여 mysql>오류를 수정하지 않습니다.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2013-09-21 16:02:20 132cbe000 Error in foreign key constraint of table university/course:
foreign key (dept_name) references department):
Syntax error close to:
)
mysql> set foreign_key_checks=0
    -> ;
Query OK, 0 rows affected (0.00 sec)
mysql> create table course
    -> (course_id varchar(7),
    -> title varchar(50),
    -> dept_name varchar(20),
    -> credits numeric(2,0),
    -> primary key(course_id),
    -> foreign key (dept_name) references department);
ERROR 1215 (HY000): Cannot add foreign key constraint

---

FOREIGN KEYfor 구문은 CREATE TABLE다음과 같이 구성됩니다.

FOREIGN KEY (index_col_name)
        REFERENCES table_name (index_col_name,...)

따라서 MySQL DDL은 다음과 같아야합니다.

 create table course (
        course_id varchar(7),
        title varchar(50),
        dept_name varchar(20),
        credits numeric(2 , 0 ),
        primary key (course_id),
        FOREIGN KEY (dept_name)
            REFERENCES department (dept_name)
    );

또한 department테이블에 dept_name있어야합니다VARCHAR(20)

자세한 정보는 MySQL 문서 에서 찾을 수 있습니다.

---

이 모호한 오류 메시지가 표시되면 다음을 실행하여보다 구체적인 오류를 확인할 수 있습니다.

SHOW ENGINE INNODB STATUS;

가장 일반적인 이유는 외래 키를 만들 때 참조 된 필드와 외래 키 필드가 모두 일치해야하기 때문입니다.

• 엔진 은 동일해야합니다 ( 예 : InnoDB).
• 데이터 유형 은 동일하고 길이가 동일해야합니다.
  예 : VARCHAR (20) 또는 INT (10) UNSIGNED
• 데이터 정렬 은 동일해야합니다. 예 : utf8
• 고유 -외래 키는 참조 테이블에서 고유 한 (일반적으로 개인) 필드를 참조해야 합니다.

이 오류의 또 다른 원인은 다음과 같습니다.
일부 열이 NOT NULL로 정의되었지만 SET NULL 조건을 정의했습니다.

---

dept_name열에 다른 문자 집합이있을 수 있습니다.

둘 중 하나 또는 둘 다를 변경할 수 있습니다.

ALTER TABLE department MODIFY dept_name VARCHAR(20) CHARACTER SET utf8;
ALTER TABLE course MODIFY dept_name VARCHAR(20) CHARACTER SET utf8;

---

foreign key (dept_name) references department

이 구문은 MySQL에 유효하지 않습니다. 대신 다음과 같아야합니다.

foreign key (dept_name) references department(dept_name)

MySQL은 dept_name두 번 사용해야 합니다 . 한 번은 외부 열을 정의하고 한 번은 기본 열을 정의합니다.

13.1.17.2. FOREIGN KEY 제약 조건 사용

... [the] essential syntax for a foreign key constraint definition in a CREATE TABLE or ALTER TABLE statement looks like this:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

---

It is also possible to get this error if the foreign key is not a primary key within its own table.

I did an ALTER TABLE and accidentally removed the primary key status of a column, and got this error.

---

ERROR 1215 (HY000): Cannot add foreign key constraint

It is also worth noting that you get this error when the type of the column that is a foreign key in another able doesn't explicitly match the column in the correct table.

For example:

alter table schoolPersons
         add index FKEF5AB5E532C8FBFA (student_id),
         add constraint FKEF5AB5E532C8FBFA
         foreign key (student_id)
         references student (id);
ERROR 1215 (HY000): Cannot add foreign key constraint

This was because the student_id field was defined as:

mysql> desc schoolPersons;
+--------------------+------------+------+-----+---------+----------------+
| Field              | Type       | Null | Key | Default | Extra          |
+--------------------+------------+------+-----+---------+----------------+
| student_id         | bigint(20) | YES  |     | NULL    |                |

while the id field in the student table was defined as:

mysql> desc persons;
+--------------+----------------------+------+-----+-------------------+-----------------+
| Field        | Type                 | Null | Key | Default           | Extra           |
+--------------+----------------------+------+-----+-------------------+-----------------+
| id           | int(10) unsigned     | NO   | PRI | NULL              | auto_increment  |

The bigint(20) (generated from Java long by hibernate) is not compatible with int(10) unsigned (Java int).

---

I don't meet the problem as you. But I get the same ERROR Message. So I mark it down here for others' convience.

Check the charset of two table if the column type is char or varchar. I use a charset=gbk, but I create a new table whose default charset=utf8. So the charset is not the same.

ERROR 1215 (HY000): Cannot add foreign key constraint

To solve it is to use the same charset. For example utf8.

---

Just add 'unsigned' for the FOREIGN constraint

`FK` int(11) unsigned DEFAULT NULL,

---

Below code worked for me

set @@foreign_key_checks=0;
ALTER TABLE  `table1` ADD CONSTRAINT `table1_fk1` FOREIGN KEY (`coloumn`) REFERENCES `table2` (`id`) ON DELETE CASCADE;

---

It's worth noting that this error can also happen if the target table or column you're using in the REFERENCES portion simply doesn't exist.

---

I don't see anyone stating this explicitly and I had this same error message and my problem was that I was trying to add a foreign key to a TEMPORARY table. Which is disallowed as noted in the manual

Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table. The parent and child tables must use the same storage engine. They must not be TEMPORARY tables.

(emphasis mine)

---

I came across the same issue as well. Not sure why this is working but it indeed works: Try add ENGINE INNODB after your create query.

mysql> create table course
-> (course_id varchar(7),
-> title varchar (50),
-> dept_name varchar(20),
-> credits numeric(2,0),
-> primary key(course_id),
-> foreign key (dept_name) references department) ENGINE INNODB;

---

Even if this is not directly linked precisely to your situation, it may help further readers to note that you can get exactly the same error output when you type show engine innodb mstatus if you do not respect the order of creating the database tables; meaning you must not add a foreign constraint referencing a table that does not exist yet. The reference table must exist prior to the table which points to it.

This is also true when the table creation order is respected but not the columns involved in the foreign key constraint.

---

In my case charset, datatype every thing was correct. After investigation I found that in parent table there was no index on foreign key column. Once added problem got solved.

---

CONSTRAINT vendor_tbfk_1 FOREIGN KEY (V_CODE) REFERENCES vendor (V_CODE) ON UPDATE CASCADE

this is how it could be... look at the referencing column part. (V_code)

ReferenceURL : https://stackoverflow.com/questions/18930084/mysql-error-1215-hy000-cannot-add-foreign-key-constraint