MySQL kann keine Fremdschlüsseleinschränkung hinzufügen
Ich versuche also, meiner Datenbank Fremdschlüsseleinschränkungen als Projektanforderung hinzuzufügen, und es funktionierte beim ersten Mal oder bei zwei verschiedenen Tabellen, aber ich habe zwei Tabellen, bei denen ich einen Fehler bekomme, wenn ich die Fremdschlüsseleinschränkungen hinzufügen möchte. Die Fehlermeldung, die ich erhalte, lautet:
FEHLER 1215 (HY000): Fremdschlüsseleinschränkung kann nicht hinzugefügt werden
Dies ist die SQL, die ich zum Erstellen der Tabellen verwende. Die beiden fehlerhaften Tabellen sind Patient und Appointment.
SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1;
SET @[email protected]@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 [email protected]_SQL_MODE;
SET [email protected]_FOREIGN_KEY_CHECKS;
SET [email protected]_UNIQUE_CHECKS;
Um den spezifischen Fehler zu finden, führen Sie folgendes aus:
SHOW ENGINE INNODB STATUS;
Und schau in den LATEST FOREIGN KEY ERROR Abschnitt.
Der Datentyp für die untergeordnete Spalte muss genau mit der übergeordneten Spalte übereinstimmen. Da medicalhistory.MedicalHistoryID beispielsweise eine INT ist, muss Patient.MedicalHistory auch eine INT und keine SMALLINT sein.
Sie sollten auch die Abfrage set foreign_key_checks=0 ausführen, bevor Sie die DDL ausführen, damit Sie die Tabellen in einer beliebigen Reihenfolge erstellen können, anstatt alle übergeordneten Tabellen vor den entsprechenden untergeordneten Tabellen erstellen zu müssen.
Ich hatte ein Feld als "Unsigned" und das andere nicht gesetzt. Sobald ich beide Spalten auf Unsigned gesetzt habe, hat es funktioniert.
- Engine sollte gleich sein z. InnoDB
- Datentyp sollte gleich sein und dieselbe Länge haben. z.B. VARCHAR (20)
- Collation Columns-Zeichensatz sollte gleich sein. z.B. utf8
Watchout: Auch wenn Ihre Tabellen die gleiche Sortierung haben, können Spalten noch andere haben. - Eindeutig - Fremdschlüssel sollte sich auf ein Feld beziehen, das eindeutig ist (normalerweise Primärschlüssel) in der Referenztabelle.
Versuchen Sie, denselben Typ Ihrer Primärschlüssel zu verwenden - int (11) - auf den Fremdschlüsseln - smallint (5) - auch.
Ich hoffe es hilft!
Stellen Sie sicher, dass die Zeichenkodierung und -sortierung für die beiden Tabellen identisch ist.
In meinem eigenen Fall verwendete eine der Tabellen utf8 und die andere Tabelle latin1.
Ich hatte einen anderen Fall, bei dem die Kodierung gleich war, die Sortierung jedoch anders war. Ein utf8_general_ci der andere utf8_unicode_ci
Sie können diesen Befehl ausführen, um die Kodierung und Sortierung für eine Tabelle festzulegen.
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Ich hoffe das hilft jemandem.
Um einen FOREIGN KEY in Tabelle B festzulegen, müssen Sie in der Tabelle A einen KEY setzen.
In Tabelle A: INDEX id (id)
Und dann in der Tabelle B,
CONSTRAINT `FK_id` FOREIGN KEY (`id`) REFERENCES `table-A` (`id`)
Ich hatte das gleiche Problem und die Lösung war sehr einfach. Lösung: In Tabelle deklarierte Fremdschlüssel sollten nicht auf null gesetzt werden.
referenz: Wenn Sie eine SET NULL-Aktion angeben, stellen Sie sicher, dass Sie die Spalten in der untergeordneten Tabelle nicht als NOT NULL deklariert haben. ( ref )
Stellen Sie sicher, dass beide Tabellen das InnoDB-Format haben. Auch wenn einer im MyISAM-Format vorliegt, funktioniert die Fremdschlüsseleinschränkung nicht.
Eine andere Sache ist, dass beide Felder vom gleichen Typ sein sollten. Wenn einer INT ist, sollte der andere auch INT sein. Wenn es sich um VARCHAR handelt, sollte das andere auch VARCHAR sein usw.
Überprüfen Sie die folgenden Regeln:
Zuerst wird geprüft, ob Namen für Tabellennamen richtig angegeben sind
Zweiter rechter Datentyp Fremdschlüssel angeben?
Ich bin mit dem Problem konfrontiert und konnte es lösen, indem ich mich vergewisserte, dass die Datentypen genau übereinstimmen.
Ich habe SequelPro zum Hinzufügen der Einschränkung verwendet, und der Primärschlüssel wurde standardmäßig als unsigniert festgelegt.
Überprüfen Sie die Signatur in beiden Tabellenspalten. Wenn die referenzierende Tabellenspalte SIGNED ist, sollte auch die referenzierte Tabellenspalte SIGNED sein.
Mein Problem war, dass ich versuchte, die Relationstabelle vor anderen Tabellen zu erstellen!
Eine weitere Ursache für diesen Fehler liegt vor, wenn Ihre Tabellen oder Spalten reservierte Schlüsselwörter enthalten:
Manchmal vergisst man diese.
Ich hatte einen ähnlichen Fehler beim Erstellen eines Fremdschlüssels in einer Many to Many-Tabelle, bei dem der Primärschlüssel aus 2 Fremdschlüsseln und einer anderen normalen Spalte bestand. Ich habe das Problem behoben, indem ich den Namen der referenzierten Tabelle korrigierte, d. H. Firma, wie im nachstehenden korrigierten Code gezeigt:
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
);
ANMERKUNG: Die folgenden Tabellen wurden von einer Site übernommen, als ich einige R & D in der Datenbank. Die Namenskonvention ist also nicht richtig.
Für mich bestand das Problem darin, dass meine übergeordnete Tabelle einen anderen Zeichensatz hatte als der, den ich erstellt hatte.
Übergeordnete Tabelle (PRODUKTE)
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
Child-Tabelle, bei der ein Problem aufgetreten ist (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
);
GEÄNDERT ZU
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
Ich hatte einen ähnlichen Fehler mit zwei Fremdschlüsseln für verschiedene Tabellen, aber mit denselben Schlüsselnamen! Ich habe die Schlüssel umbenannt und der Fehler war weg.
Ich habe den gleichen Fehler bekommen. Die Ursache in meinem Fall war:
- Ich habe eine Sicherungskopie einer Datenbank über phpmyadmin erstellt, indem ich die gesamte Datenbank kopierte.
- Ich habe eine neue Datenbank mit demselben Namen wie die alte Datenbank erstellt und sie ausgewählt.
- Ich habe ein SQL-Skript gestartet, um aktualisierte Tabellen und Daten zu erstellen.
- Ich habe den Fehler bekommen. Auch wenn ich foreign_key_checks deaktiviert habe. Obwohl die Datenbank vollständig leer war.
Die Ursache war: Da ich phpmyadmin verwendet habe, um einige Fremdschlüssel in der umbenannten Datenbank zu erstellen, wurden die Fremdschlüssel mit einem Datenbanknamenpräfix erstellt, das Datenbanknamenpräfix wurde jedoch nicht aktualisiert. Es gab also immer noch Verweise in der Backup-Datenbank, die auf die neu erstellte Datenbank hindeuteten.
Hatte einen ähnlichen Fehler, aber in meinem Fall fehlte ich, um die PK als auto_increment zu erklären.
Nur für den Fall, dass es für jeden hilfreich sein könnte
Meine Lösung ist vielleicht ein bisschen peinlich und erzählt die Geschichte, warum Sie manchmal schauen sollten, was Sie vor Ihnen haben, anstatt in diesen Beiträgen :)
Ich hatte vorher einen Forward Engineer ausgeführt, der fehlgeschlagen war, was bedeutete, dass meine Datenbank bereits ein paar Tabellen hatte. Dann saß ich beim Versuch, Fehler bei Fremdschlüsselkontrakten zu beheben, um sicherzustellen, dass alles perfekt war zuvor erstellte Tabellen, so war es nicht vorherrschend.
In meinem Fall ist ein Syntaxfehler aufgetreten, der von MySQL Console beim Ausführen der Abfrage nicht explizit gemeldet wurde. Der SHOW ENGINE INNODB STATUS-Abschnitt des LATEST FOREIGN KEY ERROR-Befehls wurde jedoch gemeldet.
Syntax error close to:
REFERENCES`role`(`id`) ON DELETE CASCADE) ENGINE = InnoDB DEFAULT CHARSET = utf8
Ich musste ein Leerzeichen zwischen REFERENCES und role lassen, damit es funktioniert.