MySQL에서 외부 키 제약 조건을 추가할 수 없음
그래서 프로젝트 요건으로 데이터베이스에 외부 키 제약 조건을 추가하려고 합니다.처음에는 다른 테이블에서 한두 번 작동했는데 외부 키 제약 조건을 추가할 때 오류가 발생하는 테이블이 두 개 있습니다.표시되는 오류 메시지는 다음과 같습니다.
ERROR 1215 (HY000):외부 키 제약 조건을 추가할 수 없습니다.
은 SQL이다.Patient
★★★★★★★★★★★★★★★★★」Appointment
.
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
CREATE SCHEMA IF NOT EXISTS `doctorsoffice` DEFAULT CHARACTER SET utf8 ;
USE `doctorsoffice` ;
-- -----------------------------------------------------
-- Table `doctorsoffice`.`doctor`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`doctor` ;
CREATE TABLE IF NOT EXISTS `doctorsoffice`.`doctor` (
`DoctorID` INT(11) NOT NULL AUTO_INCREMENT ,
`FName` VARCHAR(20) NULL DEFAULT NULL ,
`LName` VARCHAR(20) NULL DEFAULT NULL ,
`Gender` VARCHAR(1) NULL DEFAULT NULL ,
`Specialty` VARCHAR(40) NOT NULL DEFAULT 'General Practitioner' ,
UNIQUE INDEX `DoctorID` (`DoctorID` ASC) ,
PRIMARY KEY (`DoctorID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `doctorsoffice`.`medicalhistory`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`medicalhistory` ;
CREATE TABLE IF NOT EXISTS `doctorsoffice`.`medicalhistory` (
`MedicalHistoryID` INT(11) NOT NULL AUTO_INCREMENT ,
`Allergies` TEXT NULL DEFAULT NULL ,
`Medications` TEXT NULL DEFAULT NULL ,
`ExistingConditions` TEXT NULL DEFAULT NULL ,
`Misc` TEXT NULL DEFAULT NULL ,
UNIQUE INDEX `MedicalHistoryID` (`MedicalHistoryID` ASC) ,
PRIMARY KEY (`MedicalHistoryID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `doctorsoffice`.`Patient`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`Patient` ;
CREATE TABLE IF NOT EXISTS `doctorsoffice`.`Patient` (
`PatientID` INT unsigned NOT NULL AUTO_INCREMENT ,
`FName` VARCHAR(30) NULL ,
`LName` VARCHAR(45) NULL ,
`Gender` CHAR NULL ,
`DOB` DATE NULL ,
`SSN` DOUBLE NULL ,
`MedicalHistory` smallint(5) unsigned NOT NULL,
`PrimaryPhysician` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`PatientID`) ,
UNIQUE INDEX `PatientID_UNIQUE` (`PatientID` ASC) ,
CONSTRAINT `FK_MedicalHistory`
FOREIGN KEY (`MEdicalHistory` )
REFERENCES `doctorsoffice`.`medicalhistory` (`MedicalHistoryID` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `FK_PrimaryPhysician`
FOREIGN KEY (`PrimaryPhysician` )
REFERENCES `doctorsoffice`.`doctor` (`DoctorID` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `doctorsoffice`.`Appointment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`Appointment` ;
CREATE TABLE IF NOT EXISTS `doctorsoffice`.`Appointment` (
`AppointmentID` smallint(5) unsigned NOT NULL AUTO_INCREMENT ,
`Date` DATE NULL ,
`Time` TIME NULL ,
`Patient` smallint(5) unsigned NOT NULL,
`Doctor` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`AppointmentID`) ,
UNIQUE INDEX `AppointmentID_UNIQUE` (`AppointmentID` ASC) ,
CONSTRAINT `FK_Patient`
FOREIGN KEY (`Patient` )
REFERENCES `doctorsoffice`.`Patient` (`PatientID` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `FK_Doctor`
FOREIGN KEY (`Doctor` )
REFERENCES `doctorsoffice`.`doctor` (`DoctorID` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `doctorsoffice`.`InsuranceCompany`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`InsuranceCompany` ;
CREATE TABLE IF NOT EXISTS `doctorsoffice`.`InsuranceCompany` (
`InsuranceID` smallint(5) NOT NULL AUTO_INCREMENT ,
`Name` VARCHAR(50) NULL ,
`Phone` DOUBLE NULL ,
PRIMARY KEY (`InsuranceID`) ,
UNIQUE INDEX `InsuranceID_UNIQUE` (`InsuranceID` ASC) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `doctorsoffice`.`PatientInsurance`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `doctorsoffice`.`PatientInsurance` ;
CREATE TABLE IF NOT EXISTS `doctorsoffice`.`PatientInsurance` (
`PolicyHolder` smallint(5) NOT NULL ,
`InsuranceCompany` smallint(5) NOT NULL ,
`CoPay` INT NOT NULL DEFAULT 5 ,
`PolicyNumber` smallint(5) NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (`PolicyNumber`) ,
UNIQUE INDEX `PolicyNumber_UNIQUE` (`PolicyNumber` ASC) ,
CONSTRAINT `FK_PolicyHolder`
FOREIGN KEY (`PolicyHolder` )
REFERENCES `doctorsoffice`.`Patient` (`PatientID` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `FK_InsuranceCompany`
FOREIGN KEY (`InsuranceCompany` )
REFERENCES `doctorsoffice`.`InsuranceCompany` (`InsuranceID` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
USE `doctorsoffice` ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
특정 오류를 찾으려면 다음 절차를 따릅니다.
SHOW ENGINE INNODB STATUS;
★★★★★★★★★★★★★★★★★★★LATEST FOREIGN KEY ERROR
★★★★★★ 。
하위 열의 데이터 유형은 상위 열과 정확히 일치해야 합니다.를 들면, 「」이기 때문에,medicalhistory.MedicalHistoryID
는 입니다.INT
,Patient.MedicalHistory
, also 「 」로 할 가 있습니다.INT
이에요.SMALLINT
.
이 쿼리를 .set foreign_key_checks=0
DDL을 실행하기 전에 모든 부모 테이블을 관련 자녀 테이블 앞에 작성할 필요 없이 임의의 순서로 테이블을 작성할 수 있습니다.
한 필드는 "Unsigned"로 설정하고 다른 필드는 "Unsigned"로 설정하지 않았습니다.두 열을 모두 부호 없음으로 설정하면 동작합니다.
- 엔진은 동일해야 합니다(예: InnoDB
- 데이터 유형은 동일하고 길이가 같아야 합니다. 예: VARCHAR(20)
- 데이터 정렬 열 문자 집합은 동일해야 합니다(예: utf8).
이 같은조합이 수 .테이블에 동일한 데이터 정렬이 있더라도 열에 다른 데이터 정렬이 있을 수 있습니다. - Unique - 외부 키는 참조 테이블에서 고유한 필드(일반적으로 기본 키)를 참조해야 합니다.
외부 키인 smallint(5)에서도 동일한 유형의 기본 키인 int(11)를 사용해 보십시오.
도움이 됐으면 좋겠다!
두 테이블의 문자 인코딩과 조회가 동일한지 확인합니다.
중 가 '우리'를 사용하고 .utf8
다른 는 '먹다'를 요.latin1
인코딩은 동일하지만 조합이 다른 경우가 있었습니다., 하나.utf8_general_ci
utf8_unicode_ci
이 명령어를 실행하여 테이블의 부호화와 조회를 설정할 수 있습니다.
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
이게 도움이 됐으면 좋겠어요.
표 B에 외부 키를 설정하려면 표 A에 키를 설정해야 합니다.
A: a A: 색id
)id
)
그리고 테이블 B에서
CONSTRAINT `FK_id` FOREIGN KEY (`id`) REFERENCES `table-A` (`id`)
나도 같은 문제가 있었고 해결책은 매우 간단했다.해결 방법: 테이블에서 선언된 외부 키는 null이 아닌 것으로 설정해서는 안 됩니다.
reference : SET NULL 액션을 지정할 경우 자녀 테이블의 컬럼을 NOT NULL로 선언하지 않았는지 확인하십시오(ref ).
다음 규칙을 확인합니다.
먼저 테이블 이름에 이름이 올바르게 지정되었는지 확인합니다.
오른쪽 두 번째 데이터 유형은 외부 키에 제공됩니까?
두 테이블이 모두 InnoDB 형식인지 확인하십시오.MyISAM 형식이라도 외부 키 제약은 기능하지 않습니다.
그리고 또 하나, 두 필드가 같은 유형이어야 합니다.한쪽이 INT일 경우 다른 쪽도 INT여야 합니다.한쪽이 VARCHAR일 경우 다른 한쪽도 VARCHAR 등이어야 합니다.
저는 이 문제에 직면했고 데이터 유형이 정확히 일치하는지 확인함으로써 문제를 해결할 수 있었습니다.
구속조건을 추가하기 위해 Sequel Pro를 사용하고 있었는데, 기본적으로는 프라이머리 키가 서명되지 않은 상태로 되어 있었습니다.
두 테이블 열의 서명을 확인합니다.참조 테이블 열이 SIGNED일 경우 참조 테이블 열도 SIGNED여야 합니다.
다른 테이블보다 먼저 관계 테이블을 작성하려고 하는 것이 문제였습니다!
이 문제를 해결하려면 두 가지 방법이 있습니다.
MSQL 명령 순서 변경
쿼리 전에 다음 작업을 수행합니다.
SET foreign_key_set = 0;
메모: 다음 표는 데이터베이스에 대한 R&D를 수행할 때 일부 사이트에서 가져온 것입니다.그래서 명명 규칙은 적절하지 않다.
문제는 부모님 테이블이 제가 만든 테이블과 다른 문자 세트를 가지고 있다는 것입니다.
부모 테이블(PRODUCTS)
products | CREATE TABLE `products` (
`productCode` varchar(15) NOT NULL,
`productName` varchar(70) NOT NULL,
`productLine` varchar(50) NOT NULL,
`productScale` varchar(10) NOT NULL,
`productVendor` varchar(50) NOT NULL,
`productDescription` text NOT NULL,
`quantityInStock` smallint(6) NOT NULL,
`buyPrice` decimal(10,2) NOT NULL,
`msrp` decimal(10,2) NOT NULL,
PRIMARY KEY (`productCode`),
KEY `productLine` (`productLine`),
CONSTRAINT `products_ibfk_1` FOREIGN KEY (`productLine`) REFERENCES `productlines` (`productLine`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
문제가 발생한 하위 테이블(PRICE_LOGS)
price_logs | CREATE TABLE `price_logs` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`productCode` varchar(15) DEFAULT NULL,
`old_price` decimal(20,2) NOT NULL,
`new_price` decimal(20,2) NOT NULL,
`added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `productCode` (`productCode`),
CONSTRAINT `price_logs_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`) ON DELETE CASCADE ON UPDATE CASCADE
);
변경처
price_logs | CREATE TABLE `price_logs` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`productCode` varchar(15) DEFAULT NULL,
`old_price` decimal(20,2) NOT NULL,
`new_price` decimal(20,2) NOT NULL,
`added_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `productCode` (`productCode`),
CONSTRAINT `price_logs_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
이 오류의 또 다른 원인은 테이블 또는 컬럼에 예약된 키워드가 포함되어 있는 경우입니다.
때때로 사람들은 이것들을 잊는다.
PhpMyAdmin에서 이 오류가 발생할 경우 SQL 파일을 가져오기 전에 외부 키 검사를 사용하지 않도록 설정하십시오.
내게는 대상 테이블이 외부 키를 차단하고 있었다. 증가(Auto-Increment를 .AI
Foreign-Key " " " 。
프라이머리 키가 2개의 외부 키와 다른 일반 열로 구성된 [Many to Many]테이블에서 외부 키 작성 시 유사한 오류가 발생하였습니다.아래 수정된 코드와 같이 참조된 테이블명, 즉 회사를 수정하여 문제를 해결하였습니다.
create table company_life_cycle__history -- (M-M)
(
company_life_cycle_id tinyint unsigned not null,
Foreign Key (company_life_cycle_id) references company_life_cycle(id) ON DELETE CASCADE ON UPDATE CASCADE,
company_id MEDIUMINT unsigned not null,
Foreign Key (company_id) references company(id) ON DELETE CASCADE ON UPDATE CASCADE,
activity_on date NOT NULL,
PRIMARY KEY pk_company_life_cycle_history (company_life_cycle_id, company_id,activity_on),
created_on datetime DEFAULT NULL,
updated_on datetime DEFAULT NULL,
created_by varchar(50) DEFAULT NULL,
updated_by varchar(50) DEFAULT NULL
);
테이블이 다른 두 개의 외부 키에서 유사한 오류가 발생했지만 키 이름은 동일합니다.키 이름을 변경했는데 오류가 사라졌습니다.)
비슷한 오류가 발생했지만, 제 경우 pk를 auto_increment로 선언할 수 없었습니다.
누구에게나 도움이 될 수 있을 때를 대비해서
저도 같은 오류가 발생했어요.제 경우 원인은 다음과 같습니다.
- 데이터베이스 전체를 복사하여 phpmyadmin을 통해 데이터베이스 백업을 만들었습니다.
- 이전 db가 선택하지 않은 것과 동일한 이름으로 새 db를 만들었습니다.
- 업데이트된 테이블과 데이터를 생성하기 위해 SQL 스크립트를 시작했습니다.
- 나는 실수를 했다.또한 foreign_key_checks를 디세블로 했을 때도요.데이터베이스가 완전히 비어있었음에도 불구하고.
원인은 phpmyadmin을 사용하여 이름이 바뀐 데이터베이스에 외부 키를 작성했기 때문에 데이터베이스 이름 접두사로 작성되었지만 데이터베이스 이름 접두사가 업데이트되지 않았습니다.따라서 backup-db에는 새로 생성된 db를 가리키는 참조가 남아 있었습니다.
제 해결책은 조금 당황스러울 수 있습니다.또, 이러한 투고가 아니고, 눈앞에 있는 것을 확인할 필요가 있는 이유를 설명하고 있습니다.
이전에 포워드 엔지니어를 실행한 적이 있는데, 이는 데이터베이스에 이미 몇 개의 테이블이 있는 것을 의미하며, 모든 것이 완벽한지 확인하기 위해 앉아 있었지만, 이전에 작성한 테이블과 충돌했기 때문에 더 이상 문제가 발생하지 않았습니다.
제 경우 MySQL 콘솔에서 쿼리를 실행할 때 명시적으로 알리지 않은 구문 오류가 있었습니다.하지만,SHOW ENGINE INNODB STATUS
명령어LATEST FOREIGN KEY ERROR
섹션이 보고되었습니다.
Syntax error close to:
REFERENCES`role`(`id`) ON DELETE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8
나는 그 사이에 여백을 남겨야 했다.REFERENCES
그리고.role
할 수 있을 것 같아요
현재 DB를 참조하는 비현재 DB에 대해 FK를 작성하는 경우 현재 DB 테이블의 접두사를 생략할 수 없습니다.
USE currrent_db;
ALTER TABLE other_db.tasks ADD CONSTRAINT tasks_fk FOREIGN KEY (user_id) REFERENCES currrent_db.users (id);
사용자 테이블에 "current_db"를 생략하면 FK 오류가 나타납니다.흥미로운 점은 SHOW ENGINE INNODB STATUS. 이 경우 아무것도 표시되지 않는다는 것입니다.
마이솔루션!!
table1의 column1을 table2의 외부 키로서 사용하는 경우 column1은 table1의 키여야 합니다.
예를 들어 다음과 같은 경우departments
테이블, 이 테이블은dept_id
기둥.
이제 다른 테이블이 있다고 가정해봅시다employees
가지고 있다emp_dept_id
기둥.
를 사용하고 싶은 경우dept_id
의 컬럼department
의 외부 키로서의 표emp_dept_id
의 열emp
이에요, 이에요.dept_id
department
테이블은 프라이머리 키가 아니면 적어도 키여야 합니다.
꼭...dept_id
depratment
는 다른 테이블의 외부 키로 사용하기 전에 프라이머리 키 또는 고유 키 중 하나입니다.
과 자녀 테이블 이름을 KEYFORE KEY)를 했습니다.c_id
x9o_parent_table
)c_id
)
정상적으로 동작해, 테이블이 올바르게 인스톨 됩니다.이치노
언급URL : https://stackoverflow.com/questions/15534977/mysql-cannot-add-foreign-key-constraint
'programing' 카테고리의 다른 글
MySQL 데이터 디렉터리 위치 (0) | 2022.09.04 |
---|---|
Android에서 현재 요일을 얻는 가장 쉬운 방법은 무엇입니까? (0) | 2022.09.04 |
부동 데이터 유형과 10진 데이터 유형의 차이 (0) | 2022.09.04 |
'sorted(list)'와 'list.sort()'의 차이점은 무엇입니까? (0) | 2022.09.04 |
익명 클래스에 대한 다중 상속 (0) | 2022.09.04 |