[mysql] MySQL은 외래 키 제약 조건을 만들 수 없습니다.
mysql 데이터베이스의 기존 테이블에 대한 외래 키를 만드는 데 문제가 있습니다.
나는 테이블이있다 exp:
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| EID | varchar(45) | NO | PRI | NULL | |
| Comment | text | YES | | NULL | |
| Initials | varchar(255) | NO | | NULL | |
| ExpDate | date | NO | | NULL | |
| InsertDate | date | NO | | NULL | |
| inserted_by | int(11) unsigned | YES | MUL | NULL | |
+-------------+------------------+------+-----+---------+-------+
sample_df다음을 사용하여 이것을 참조 하는 새 테이블을 만들고 싶지 않습니다 .
CREATE TABLE sample_df (
df_id mediumint(5) unsigned AUTO_INCREMENT primary key,
sample_type mediumint(5) unsigned NOT NULL,
df_10 BOOLEAN NOT NULL,
df_100 BOOLEAN NOT NULL,
df_1000 BOOLEAN NOT NULL,
df_above_1000 BOOLEAN NOT NULL,
target INT(11) unsigned NOT NULL,
assay MEDIUMINT(5) unsigned zerofill NOT NULL,
insert_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
inserted_by INT(11) unsigned NOT NULL,
initials varchar(255),
experiment VARCHAR(45),
CONSTRAINT FOREIGN KEY (inserted_by) REFERENCES user (iduser),
CONSTRAINT FOREIGN KEY (target) REFERENCES protein (PID),
CONSTRAINT FOREIGN KEY (sample_type) REFERENCES sample_type (ID),
CONSTRAINT FOREIGN KEY (assay) REFERENCES assays (AID),
CONSTRAINT FOREIGN KEY (experiment) REFERENCES exp (EID)
);
하지만 오류가 발생합니다.
ERROR 1215 (HY000): Cannot add foreign key constraint
더 많은 정보를 얻으려면 다음을 수행했습니다.
SHOW ENGINE INNODB STATUS\G
내가 얻은 :
FOREIGN KEY (experiment) REFERENCES exp (EID)
):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
나에게 열 유형은 둘 다 varchar (45)이기 때문에 일치하는 것 같습니다. (또한 experiment열을 null이 아닌 것으로 설정하려고 시도 했지만 이것이 수정되지 않았습니다) 그래서 문제는 Cannot find an index in the referenced table where the referenced columns appear as the first columns. 그러나 이것이 무엇을 의미하는지 또는 어떻게 확인 / 고정하는지 잘 모르겠습니다. 누구에게 제안이 있습니까? 그리고 무엇을 의미 first columns합니까?
답변
가능한 원인의 혼합에 이것을 던져서 참조 테이블 열이 동일한 “유형”을 가지지 만 동일한 서명이 없을 때이 문제를 만났습니다.
필자의 경우 참조 된 테이블 열은 TINYINT UNSIGNED이고 참조하는 테이블 열은 TINYINT SIGNED입니다. 두 열을 모두 정렬하면 문제가 해결되었습니다.
답변
이 오류는 참조 테이블과 현재 테이블에 동일한 문자 집합이없는 경우에도 발생할 수 있습니다.
답변
http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html 에 따르면
MySQL은 외래 키 확인이 빠르고 테이블 스캔이 필요하지 않도록 외래 키와 참조 된 키에 대한 인덱스가 필요합니다. 참조 테이블에는 외래 키 열이 동일한 순서의 첫 번째 열로 나열되는 인덱스가 있어야합니다.
InnoDB는 외부 키가 인덱스 열 또는 열 그룹을 참조하도록 허용합니다. 그러나 참조 된 테이블에는 참조 된 열이 동일한 순서의 첫 번째 열로 나열되는 인덱스가 있어야합니다.
따라서 참조 된 테이블에 인덱스가 존재하고 여러 컬럼으로 구성되어 있고 원하는 컬럼이 첫 번째가 아닌 경우 오류가 발생합니다.
오류의 원인은 다음 규칙을 위반했기 때문입니다.
외래 키와 참조 된 키의 해당 열은 유사한 데이터 유형을 가져야합니다. 정수 유형의 크기와 부호는 동일해야합니다. 문자열 유형의 길이가 같을 필요는 없습니다. 2 진이 아닌 (문자) 문자열 열의 경우 문자 세트와 데이터 정렬이 동일해야합니다.
답변
@Anton에서 언급했듯이 이것은 데이터 유형이 다르기 때문일 수 있습니다. 제 경우에는 기본 키 BIGINT (20)이 있고 INT (10)로 foreight 키를 설정하려고했습니다.
답변
제 경우에는 참조 된 열이 기본 또는 고유 한 것으로 선언되지 않은 것으로 나타났습니다.
답변
내 것은 참조 된 테이블과 생성 될 테이블 간의 데이터 정렬 문제 였기 때문에 참조하는 키의 데이터 정렬 유형을 명시 적으로 설정해야했습니다.
- 먼저 데이터 정렬 유형을 가져 오기 위해 참조 된 테이블에서 쿼리를 실행했습니다.
show table STATUS like '<table_name_here>';
- 그런 다음 데이터 정렬 유형을 복사하고 생성 쿼리에서 employee_id의 데이터 정렬 유형을 명시 적으로 지정했습니다. 제 경우에는 utf8_general_ci 였습니다.
CREATE TABLE dbo.sample_db
(
id INT PRIMARY KEY AUTO_INCREMENT,
event_id INT SIGNED NOT NULL,
employee_id varchar(45) COLLATE utf8_general_ci NOT NULL,
event_date_time DATETIME,
CONSTRAINT sample_db_event_event_id_fk FOREIGN KEY (event_id) REFERENCES event (event_id),
CONSTRAINT sample_db_employee_employee_id_fk FOREIGN KEY (employee_id) REFERENCES employee (employee_id)
);
답변
기본 키 의 정확한 순서도 사이에 추가 열없이 일치해야합니다.
열 순서가 실제로 일치 하는 기본 키 설정이 있었지만 문제는 기본 키에 참조 테이블의 외래 키의 일부가 아닌 추가 열이 있다는 것입니다.
예) 표 2, 열 (a, b, c)-> 표 1, 열 (a, b, d, c)-THIS FAILS
동일한 순서로 정렬 될뿐만 아니라 중간에 추가 열이 없도록 기본 키 열의 순서를 변경해야했습니다.
예) 표 2, 열 (a, b, c)-> 표 1, 열 (a, b, c, d)-이 성공
