MySQL 中出现 ERROR 1701、ERROR 1452 和 ERROR 1305 错误 - 需要一些专业知识

2024-01-12

这是我第一次真正尝试用第三范式创建数据库。我成功地创建了 DDL 脚本（在继续构建脚本之前，我已将逻辑模型验证为 3NF），但我遇到了很多错误，我不确定是否可以修复。对于这个特殊的情况，我可能已经咬了一口，但我不想放弃——这对我来说是一个很大的学习曲线，在一些帮助下，我可以克服这个问题并继续前进。

首先，这是我的 DDL 脚本：

-- This sql script creates the structure.
-- of the rugby club database.

DROP DATABASE IF EXISTS database_rugby;

CREATE DATABASE database_rugby;

USE database_rugby;

-- Create the "coach" table.
DROP TABLE IF EXISTS `database_rugby`.`coach` ;
CREATE  TABLE IF NOT EXISTS `database_rugby`.`coach` (
  `coachID` INT(5) NOT NULL ,
  `dateBeganCoaching` DATE NOT NULL ,
  `personID` INT(5) NOT NULL ,
  PRIMARY KEY (`coachID`))
ENGINE = InnoDB;


-- Create the "grade" table.
DROP TABLE IF EXISTS `database_rugby`.`grade` ;
CREATE  TABLE IF NOT EXISTS `database_rugby`.`grade` (
  `gradeID` INT(5) NOT NULL AUTO_INCREMENT ,
  `gradeName` VARCHAR(50) NOT NULL ,
  `minWeight` INT(3) NOT NULL ,
  `maxWeight` INT(3) NOT NULL ,
  `minAge` INT(3) NOT NULL ,
  `maxAge` INT(3) NOT NULL ,
  `ballSize` INT(1) NOT NULL ,
  PRIMARY KEY (`gradeID`) )
ENGINE = InnoDB;


-- Create the "coachQualification" table.
DROP TABLE IF EXISTS `database_rugby`.`coachQualification` ;

CREATE  TABLE IF NOT EXISTS `database_rugby`.`coachQualification` (
  `qualID` INT(5) NOT NULL AUTO_INCREMENT ,
  `qualName` CHAR(5) NOT NULL ,
  `gradeID` INT(5) NOT NULL ,
  PRIMARY KEY (`qualID`) ,
  INDEX `gradeID` (`gradeID` ASC) ,
  CONSTRAINT `coachQualification_ibfk_1`
    FOREIGN KEY (`gradeID`)
    REFERENCES `database_rugby`.`grade` (`gradeID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "parent" table.
DROP TABLE IF EXISTS `database_rugby`.`parent` ;
CREATE  TABLE IF NOT EXISTS `database_rugby`.`parent` (
  `parentID` INT(5) NOT NULL ,
  `personID` INT(5) NOT NULL ,
  PRIMARY KEY (`parentID`))
ENGINE = InnoDB;


-- Create the "school" table.
DROP TABLE IF EXISTS `database_rugby`.`school` ;
CREATE  TABLE IF NOT EXISTS `database_rugby`.`school` (
  `schoolID` INT(5) NOT NULL AUTO_INCREMENT ,
  `schoolName` VARCHAR(100) NOT NULL ,
  PRIMARY KEY (`schoolID`))
ENGINE = InnoDB;


-- Create the "player" table.
--
-- Inherits fields from the "person"
-- and "school" tables.
DROP TABLE IF EXISTS `database_rugby`.`player` ;

CREATE  TABLE IF NOT EXISTS `database_rugby`.`player` (
  `playerID` INT(5) NOT NULL ,
  `personID` INT(5) NOT NULL ,
  `schoolID` INT(5) NOT NULL ,
  PRIMARY KEY (`playerID`) ,
  INDEX `schoolID` (`schoolID` ASC) ,
  CONSTRAINT `player_ibfk_1`
    FOREIGN KEY (`schoolID`)
    REFERENCES `database_rugby`.`school` (`schoolID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "person" table.
--
-- This table has one:one relationships
-- with the parent, coach and player 
-- tables.
DROP TABLE IF EXISTS `database_rugby`.`person` ;
CREATE  TABLE IF NOT EXISTS `database_rugby`.`person` (
  `personID` INT(5) NOT NULL AUTO_INCREMENT ,
  `firstName` VARCHAR(50) NOT NULL ,
  `lastName` VARCHAR(50) NOT NULL ,
  `dateOfBirth` DATE NOT NULL ,
  `streetAddress` VARCHAR(150) NOT NULL ,
  `suburbAddress` VARCHAR(150) NULL DEFAULT NULL ,
  `cityAddress` VARCHAR(150) NOT NULL ,
  `photo` BLOB NULL DEFAULT NULL ,
  `parent_parentID` INT(5) NOT NULL DEFAULT '0' ,
  `coach_coachID` INT(5) NOT NULL DEFAULT '0' ,
  `player_playerID` INT(5) NOT NULL DEFAULT '0' ,
  `parent_parentID1` INT(5) NOT NULL DEFAULT '0' ,
  `player_playerID1` INT(5) NOT NULL DEFAULT '0' ,
  `coach_coachID1` INT(5) NOT NULL DEFAULT '0' ,
  `coach_coachID2` INT(5) NOT NULL DEFAULT '0' ,
  `parent_parentID2` INT(5) NOT NULL DEFAULT '0' ,
  `player_playerID2` INT(5) NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`personID`) ,
  INDEX `fk_person_coach1` (`coach_coachID2` ASC) ,
  INDEX `fk_person_parent1` (`parent_parentID2` ASC) ,
  INDEX `fk_person_player1` (`player_playerID2` ASC) ,
  CONSTRAINT `fk_person_coach1`
    FOREIGN KEY (`coach_coachID2` )
    REFERENCES `database_rugby`.`coach` (`coachID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_person_parent1`
    FOREIGN KEY (`parent_parentID2`)
    REFERENCES `database_rugby`.`parent` (`parentID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_person_player1`
    FOREIGN KEY (`player_playerID2`)
    REFERENCES `database_rugby`.`player` (`playerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- Create the "homePhone" table.
DROP TABLE IF EXISTS `database_rugby`.`homePhone` ;
CREATE  TABLE IF NOT EXISTS `database_rugby`.`homePhone` (
  `homePhoneID` INT(5) NOT NULL AUTO_INCREMENT ,
  `homeNumber` CHAR(9) NOT NULL ,
  PRIMARY KEY (`homePhoneID`))
ENGINE = InnoDB;


-- Create the "mobilePhone" table.
DROP TABLE IF EXISTS `database_rugby`.`mobilePhone` ;

CREATE  TABLE IF NOT EXISTS `database_rugby`.`mobilePhone` (
  `mobilePhoneID` INT(5) NOT NULL AUTO_INCREMENT ,
  `mobileNumber` CHAR(10) NULL DEFAULT NULL ,
  PRIMARY KEY (`mobilePhoneID`))
ENGINE = InnoDB;


-- Create the "emailAddress" table.
DROP TABLE IF EXISTS `database_rugby`.`emailAddress` ;

CREATE  TABLE IF NOT EXISTS `database_rugby`.`emailAddress` (
  `emailAddressID` INT(5) NOT NULL AUTO_INCREMENT ,
  `emailAddress` CHAR(10) NULL DEFAULT NULL ,
  PRIMARY KEY (`emailAddressID`))
ENGINE = InnoDB;


-- Create the "Contact" table
--
-- This is a linking table 
-- that describes the many:many 
-- relationships between "person" 
-- and the "homePhone", "mobilePhone", 
-- and "emailAddress" tables.
DROP TABLE IF EXISTS `database_rugby`.`contact` ;
CREATE  TABLE IF NOT EXISTS `database_rugby`.`contact` (
  `personID` INT(5) NOT NULL ,
  `homePhoneID` INT(5) NOT NULL ,
  `mobilePhoneID` INT(5) NULL DEFAULT NULL ,
  `emailAddressID` INT(5) NULL DEFAULT NULL ,
  INDEX `personID` (`personID` ASC) ,
  INDEX `homePhoneID` (`homePhoneID` ASC) ,
  INDEX `mobilePhoneID` (`mobilePhoneID` ASC) ,
  INDEX `emailAddressID` (`emailAddressID` ASC) ,
  CONSTRAINT `contact_ibfk_1`
    FOREIGN KEY (`personID` )
    REFERENCES `database_rugby`.`person` (`personID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `contact_ibfk_2`
    FOREIGN KEY (`homePhoneID`)
    REFERENCES `database_rugby`.`homePhone` (`homePhoneID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `contact_ibfk_3`
    FOREIGN KEY (`mobilePhoneID`)
    REFERENCES `database_rugby`.`mobilePhone` (`mobilePhoneID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `contact_ibfk_4`
    FOREIGN KEY (`emailAddressID`)
    REFERENCES `database_rugby`.`emailAddress` (`emailAddressID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "family" table.
--
-- This is a linking table 
-- that describes the many:many 
-- relationship between "parent" 
-- and "player" tables.
DROP TABLE IF EXISTS `database_rugby`.`family` ;
CREATE  TABLE IF NOT EXISTS `database_rugby`.`family` (
  `parentID` INT(5) NOT NULL ,
  `playerID` INT(5) NOT NULL ,
  `parent_parentID` INT(5) NOT NULL ,
  INDEX `playerID` (`playerID` ASC) ,
  INDEX `fk_family_parent1` (`parent_parentID` ASC) ,
  CONSTRAINT `family_ibfk_2`
    FOREIGN KEY (`playerID` )
    REFERENCES `database_rugby`.`player` (`playerID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_family_parent1`
    FOREIGN KEY (`parent_parentID`)
    REFERENCES `database_rugby`.`parent` (`parentID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- Create the "qualificationSet" table.
--
-- This is a linking table 
-- that describes the many:many 
-- relationship between "coach" 
-- and "coachQualification" tables.
DROP TABLE IF EXISTS `database_rugby`.`qualificationSet` ;
CREATE  TABLE IF NOT EXISTS `database_rugby`.`qualificationSet` (
  `coachID` INT(5) NOT NULL ,
  `qualID` INT(5) NOT NULL ,
  INDEX `coachID` (`coachID` ASC) ,
  INDEX `qualID` (`qualID` ASC) ,
  CONSTRAINT `qualificationSet_ibfk_1`
    FOREIGN KEY (`coachID`)
    REFERENCES `database_rugby`.`coach` (`coachID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `qualificationSet_ibfk_2`
    FOREIGN KEY (`qualID`)
    REFERENCES `database_rugby`.`coachQualification` (`qualID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "team" table.
DROP TABLE IF EXISTS `database_rugby`.`team` ;
CREATE  TABLE IF NOT EXISTS `database_rugby`.`team` (
  `teamID` INT(5) NOT NULL AUTO_INCREMENT ,
  `teamName` VARCHAR(50) NOT NULL ,
  `teamYear` INT(2) NOT NULL ,
  `gradeID` INT(5) NOT NULL ,
  PRIMARY KEY (`teamID`) ,
  INDEX `gradeID` (`gradeID` ASC) ,
  CONSTRAINT `team_ibfk_1`
    FOREIGN KEY (`gradeID`)
    REFERENCES `database_rugby`.`grade` (`gradeID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "teamAllocation" table
--
-- this is a linking table for a 
-- many:many relationship between
-- team and player tables.
DROP TABLE IF EXISTS `database_rugby`.`teamAllocation` ;

CREATE  TABLE IF NOT EXISTS `database_rugby`.`teamAllocation` (
  `teamID` INT(5) NOT NULL ,
  `playerID` INT(5) NOT NULL ,
  INDEX `teamID` (`teamID` ASC) ,
  INDEX `playerID` (`playerID` ASC) ,
  CONSTRAINT `teamallocation_ibfk_1`
    FOREIGN KEY (`teamID` )
    REFERENCES `database_rugby`.`team` (`teamID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `teamAllocation_ibfk_2`
    FOREIGN KEY (`playerID`)
    REFERENCES `database_rugby`.`player` (`playerID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- Create the "teamCoachAllocation" table.
--
-- This is a linking table 
-- that describes the many:many 
-- relationship between "coach" 
-- and "team" tables.
DROP TABLE IF EXISTS `database_rugby`.`teamCoachAllocation` ;
CREATE TABLE `database_rugby`.`teamCoachAllocation` (
  `coachID` INT(5) NOT NULL ,
  `teamID` INT(5) NOT NULL ,
  INDEX `coachID` (`coachID` ASC) ,
  INDEX `teamID` (`teamID` ASC) ,
  CONSTRAINT `teamCoachAllocation_ibfk_1`
    FOREIGN KEY (`coachID`)
    REFERENCES `database_rugby`.`coach` (`coachID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `teamCoachAllocation_ibfk_2`
    FOREIGN KEY (`teamID`)
    REFERENCES `database_rugby`.`team` (`teamID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

这是我的 DML 脚本（注意：到目前为止，该脚本仅尝试在“人”、“学校”和“玩家”详细信息中插入记录。如果我到目前为止遇到的问题得到解决，这可以帮助我稍后的..）：

-- database_data.sql.
-- This sql script inserts data into the 
-- rugby club database.

USE database_rugby;

TRUNCATE TABLE database_rugby.person;

-- Insert new persons which will be 
-- players.
INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Michael", 
"Peck", 
'2002-12-10',
"45 Skibo Street",
"Caversham", 
"Dunedin");

INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Matt", 
"Petersen", 
'2001-06-15',
"192 Bayview Road",
"South Dunedin", 
"Dunedin");

INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Christopher", 
"Petersen", 
'2003-02-19',
"192 Bayview Road",
"South Dunedin", 
"Dunedin");

INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Richard", 
"Michaels", 
'2002-04-08',
"15 Fitzroy Street",
"Caversham",  
"Dunedin");

INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Shaun", 
"Michaels", 
'2003-11-11',
"15 Fitzroy Street",
"Caversham",  
"Dunedin");

INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Harry", 
"Dackers", 
'2004-02-11',
"32 Peter Street",
"Caversham",  
"Dunedin");

INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Daniel", 
"Mitchell", 
'2002-05-19',
"112 South Road",
"Caversham",  
"Dunedin");

-- Insert new persons which will be 
-- parents.
INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Gregory", 
"Peck", 
'1971-07-22',
"123 Burns Street",
"South Dunedin", 
"Dunedin");


INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Laura", 
"Peck", 
'1979-09-08',
"123 Burns Street",
"South Dunedin", 
"Dunedin");

INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Martha", 
"Petersen", 
'1973-12-07',
"192 Bayview Road",
"South Dunedin", 
"Dunedin");

INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Chris", 
"Michaels", 
'1967-08-07',
"15 Fitzroy Street",
"Caversham",  
"Dunedin");

INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Nadine", 
"Michaels", 
'1973-10-19',
"15 Fitzroy Street",
"Caversham",  
"Dunedin");

INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Barry", 
"Dackers", 
'1965-02-11',
"32 Peter Street",
"Caversham",  
"Dunedin");

INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Kevin", 
"Mitchell", 
'1972-05-19',
"112 South Road",
"Caversham",  
"Dunedin");

INSERT INTO database_rugby.person (
firstName,
lastName,
dateOfBirth,
streetAddress,
suburbAddress,
cityAddress)
VALUES(
"Rebecca", 
"Mitchell", 
'1978-01-23',
"112 South Road",
"Caversham",  
"Dunedin");

-- Insert schools into school table.
TRUNCATE TABLE database_rugby.school;
INSERT INTO database_rugby.school(
schoolName)
VALUES(
"College Street School");

INSERT INTO database_rugby.school(
schoolName)
VALUES(
"Macandrew Intermediate School");

INSERT INTO database_rugby.school(
schoolName)
VALUES(
"Carlton Hill Primary");

INSERT INTO database_rugby.school(
schoolName)
VALUES(
"Caversham Primary School");

INSERT INTO database_rugby.school(
schoolName)
VALUES(
"Concord School");

-- Insert players into player table.
TRUNCATE TABLE database_rugby.player;
INSERT INTO database_rugby.player(
personID, 
schoolID)
VALUES(
    (SELECT personID FROM database_rugby.person 
    WHERE database_rugby.person(firstName) = "Michael" 
    AND database_rugby.person(lastName) = "Peck"), 
    (SELECT schoolID FROM database_rugby.school 
    WHERE database_rugby.school(schoolName) =  "College Street School"));

INSERT INTO database_rugby.player(
personID, 
schoolID)
VALUES(
    (SELECT personID FROM database_rugby.person 
    WHERE database_rugby.person(firstName) = "Matt" 
    AND database_rugby.person(lastName) = "Petersen"), 
    (SELECT schoolID FROM database_rugby.school 
    WHERE database_rugby.school(schoolName) =  "Macandrew Intermediate School"));

INSERT INTO database_rugby.player(
personID, 
schoolID)
VALUES(
    (SELECT personID FROM database_rugby.person 
    WHERE database_rugby.person(firstName) = "Christopher" 
    AND database_rugby.person(lastName) = "Petersen"), 
    (SELECT schoolID FROM database_rugby.school 
    WHERE database_rugby.school(schoolName) =  "College Street School"));

INSERT INTO database_rugby.player(
personID, 
schoolID)
VALUES(
    (SELECT personID FROM database_rugby.person 
    WHERE database_rugby.person(firstName) = "Richard" 
    AND database_rugby.person(lastName) = "Michaels"), 
    (SELECT schoolID FROM database_rugby.school 
    WHERE database_rugby.school(schoolName) =  "College Street School"));

INSERT INTO database_rugby.player(
personID, 
schoolID)
VALUES(
    (SELECT personID FROM database_rugby.person 
    WHERE database_rugby.person(firstName) = "Shaun" 
    AND database_rugby.person(lastName) = "Michaels"), 
    (SELECT schoolID FROM database_rugby.school 
    WHERE database_rugby.school(schoolName) =  "College Street School"));

INSERT INTO database_rugby.player(
personID, 
schoolID)
VALUES(
    (SELECT personID FROM database_rugby.person 
    WHERE database_rugby.person(firstName) = "Harry" 
    AND database_rugby.person(lastName) = "Dackers"), 
    (SELECT schoolID FROM database_rugby.school 
    WHERE database_rugby.school(schoolName) =  "Caversham Primary School"));

INSERT INTO database_rugby.player(
personID, 
schoolID)
VALUES(
    (SELECT personID FROM database_rugby.person 
    WHERE database_rugby.person(firstName) = "Daniel" 
    AND database_rugby.person(lastName) = "Mitchell"), 
    (SELECT schoolID FROM database_rugby.school 
    WHERE database_rugby.school(schoolName) =  "Caversham Primary School"));

这是两个脚本生成的 MySQL 命令行界面的输出 - 它将显示有关错误的更多信息：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 82
Server version: 5.5.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> source c:\scripts\database_schema.sql
Query OK, 16 rows affected (0.35 sec)

Query OK, 1 row affected (0.03 sec)

Database changed
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.09 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.08 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.07 sec)

mysql> source c:\scripts\database_data.sql
Database changed
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constrai
nt (`database_rugby`.`contact`, CONSTRAINT `contact_ibfk_1` FOREIGN KEY (`person
ID`) REFERENCES `database_rugby`.`person` (`personID`))
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`database_rugby`.`person`, CONSTRAINT `fk_person_coach1` FOREIGN KEY (`coa
ch_coachID2`) REFERENCES `coach` (`coachID`) ON DELETE NO ACTION ON UPDATE NO AC
TION)
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constrai
nt (`database_rugby`.`player`, CONSTRAINT `player_ibfk_1` FOREIGN KEY (`schoolID
`) REFERENCES `database_rugby`.`school` (`schoolID`))
Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constrai
nt (`database_rugby`.`person`, CONSTRAINT `fk_person_player1` FOREIGN KEY (`play
er_playerID2`) REFERENCES `database_rugby`.`player` (`playerID`))
ERROR 1305 (42000): FUNCTION database_rugby.person does not exist
ERROR 1305 (42000): FUNCTION database_rugby.person does not exist
ERROR 1305 (42000): FUNCTION database_rugby.person does not exist
ERROR 1305 (42000): FUNCTION database_rugby.person does not exist
ERROR 1305 (42000): FUNCTION database_rugby.person does not exist
ERROR 1305 (42000): FUNCTION database_rugby.person does not exist
ERROR 1305 (42000): FUNCTION database_rugby.person does not exist
mysql>

这是逻辑模型（如果有人发现任何关系存在问题 - 特别是许多：许多关系，请告诉我）。

如果我提供了太多信息，我深表歉意，但我想如果我提供了所有这些信息，这会让那些想要帮助我的人变得更容易。我花了整整 3 个小时来调整 DDL 脚本，但花了那么长时间后，我意识到我需要有更多经验的人来帮助我。我已经向具有数据库行业经验的人展示了逻辑模型，他们认为它符合 3NF。做概念和逻辑模型对我自己来说并不是那么糟糕，它试图将逻辑模型转换为工作脚本并成功插入数据（我想为每个表插入至少 10 条记录），这已成为最大的障碍。

提前致谢！

您还必须添加此声明将其添加到转储文件的顶部

SET foreign_key_checks = 0;

这将禁用外键检查，您可以轻松执行转储文件

并在转储文件底部添加另一条语句以启用外键检查

SET foreign_key_checks = 1;

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

MySQL 中出现 ERROR 1701、ERROR 1452 和 ERROR 1305 错误 - 需要一些专业知识的相关文章

在 where 子句中使用聚合函数和不同的列条件

select PO Order Qty Avg PO Order Qty as totalAverage FROM FirstStrike Retail custom Whse Pricing QR where item code 111
JDBC 时间戳和日期 GMT 问题

我有一个 JDBC 日期列如果我使用 getDate 则会得到 date 仅部分2009 年 10 月 2 日但如果我使用 getTimestamp 我会得到完整的 date 2009 年 10 月 2 日 13 56 78 890 这正
如何在事务中使用链接服务器插入远程表？

我的链接服务器设置正确我能够执行以下查询插入远程服务器表 SELECT FROM 本地服务器表然而当我做同样的事情时交易内开始传输插入远程服务器表 SELECT FROM 本地服务器表提交交易我收到类似的错误用于
如何获取knex / mysql中所有更新记录的列表

这是我正在处理的查询 return knex table returning id where boolean false andWhere fooID foo id update boolean true limit num then f
在 azure Devops 管道中部署 SQL 时遇到错误

我在 azure Devops 的发布管道中使用 sql DACPAC 类型的部署但出现以下错误我对 SQL 不了解有什么建议吗 Publishing to database database name on server Serve
验证 sql/oracle 中的电子邮件/邮政编码字段

对于以下方面的一些建议将不胜感激是否可以通过 oracle 中的 sql 中的某种检查约束来验证电子邮件和邮政编码字段或者我怀疑 pl sql 带有正则表达式的这种事情 Thanks 这是电子邮件地址的正则表达式语法包括引号 a zA
我的 SQL 表设置为允许该列为 NULL，但是当我运行它时，它说它不能为 NULL。什么/为什么/如何？

所以我在这里遇到了很奇怪的困境我的 SQL 表设置为允许 ZipCode 列为空如下所示 CREATE TABLE dbo Companies CompanyId BIGINT IDENTITY 1 1 NOT NULL PRIMARY
如何根据同一个表中的先前数据更新 SQL 表

我有一张衡量学生表现的表格student在我的数据库中如下 ID TestDate PerformanceStatus PS 1 15 03 2016 0 1 01 04 2016 2 1 05 05 2016 1 1 07 06 2016
在shell命令行中创建mysql触发器

我需要在命令行中创建一个mysql触发器这个sql在mysql控制台中运行良好 sql USE DB1 DROP TRIGGER IF EXISTS my trigger DELIMITER CREATE TRIGGER my trigg
R：ifelse 中的字符串列表

我正在寻找与 MySQL 中的 where var in 语句类似的东西我的代码如下 data lt data frame id 10001 10030 cc1 rep c a b c 10 attach data data new lt
Oracle如何将UTC时间转换为本地时间（缺少偏移信息）

我有一个包含日期列的表我认为该列中的日期是以 UTC 格式保存的我希望检索日期时以当地时间打印这意味着当我从德国调用日期时结果应该是这样的 2015 04 29 11 24 06 0200UTC EUROPE BERLIN 我尝试了
sql join 告诉我 ID 是否存在于其他表中

我有 2 张桌子 A B ID FKID 1 3 2 3 3 4 4 4 我需要一个 select 语句它显示 A 的所有内容其中一个字段告诉我表 B 是否有任何与该 ID 匹配的 id Desired Result ID hasB 1
PHP，文本从数据库中回显，没有换行，全部一体

我的数据库中有一个长文本从 php mayadmin 来看它看起来很好但是当我将它回显到页面时它会丢失所有格式即没有新行全部都在一个块中有任何想法吗 Thanks 可能是因为换行符是 n 并且 html 想要 br 所以使用nl
Postgres LIMIT/OFFSET 奇怪的行为

我正在使用 PostgreSQL 9 6 我有一个这样的查询 SELECT anon 1 id AS anon 1 id anon 1 is valid AS anon 1 is valid anon 1 first name AS ano
MySQL：“您的 SQL 语法错误...靠近键...”？ [关闭]

Closed 这个问题是无法重现或由拼写错误引起 help closed questions 目前不接受答案我发现了一个非常酷的用于丢失密码的脚本但是这一行给我带来了问题 r mysql query INSERT INTO keys u
对于返回超过1个值的SQL select，当Id为GUID时它们如何排序？

我想知道 SQL Server 如何对查询返回的数据进行排序并且各个表的 Id 列都是 uniqueidentifier 类型我在创建所有 GUID 时使用 NHibernate GuidComb 并执行以下操作 Sheet sheet
合并两个具有相同列名称的 MYSQL 表

我有两张桌子表一是计划时间 id edition time 1 1 9 23am 2 2 10 23am 表二为实际时间 id edition time 1 1 10 23am 2 2 11 23am 我想要的结果是 Caption Ed
如何将 sql 数据输出到 QCalendarWidget

我希望能够在日历小部件上突出显示 SQL 数据库中的一天就像启动程序时突出显示当前日期一样在我的示例中它是红色突出显示我想要发生的是当用户按下突出显示的日期时数据库中日期旁边的文本将显示在日历下方的标签上这是我使用 QT De
如何将事物的组合映射到关系数据库？

我有一个表其记录代表某些对象为了简单起见我假设该表只有一列这是唯一的ObjectId 现在我需要一种方法来存储该表中的对象组合组合必须是唯一的但可以是任意长度例如如果我有ObjectIds 1 2 3 4 我想存储以下组合
创建日期范围表

我正在编写一份需要显示每天值的报告我有查询的开始日期和结束日期但我希望避免丢失日期以防表不包含特定日期的值我正在考虑创建一个基本日期范围表其中包含开始和结束之间的所有日期然后将其与数据表左连接以显示每一天的值我找到了一些适用于

随机推荐

如何获取套接字的主机名？

当我从连接的套接字接收到某个事件时我必须发送一个带有我的主机名和端口作为参数的请求我希望能够从套接字对象检索此信息不幸的是关于这方面的文档很少我似乎无法找出这是否以及如何可能那么是否可以在 Socket io 中执行类似的操作
如何在中显示文件路径？

I tried
Get-InstalledModule 和 Get-Module -ListAvailable 之间有什么区别？

据我所知他们做了同样的事情那么使用其中一种而不是另一种的原因是什么抱歉所以问题规则引擎不知道还能说什么 Get InstalledModule是其一部分PowerShellGet并将使用列出已安装的模块Install Module
Excel IFERROR 的 R 等效项是什么？

我正在尝试将 IFERROR 条件放入 R 中就像 Excel IFERROR 函数一样我正在构建一个随机森林模型为了进行微调我使用tuneRF函数它有助于给出最佳的 mtry 参数 Selecting Optimal MTRY
将 Python 脚本作为 Windows 后台进程运行 [重复]

这个问题在这里已经有答案了我一直在尝试编写一个从串行端口读取数据的Python脚本它在命令行中运行良好但我需要它作为后台进程运行而无需任何命令行界面该脚本有一个 while 循环它从串行端口读取下一个数据字节并相应地模拟按键为
使用 ANSI 转义序列获取终端大小？

在研究这个问题时在评论中我发现有人提到 ANSI 转义码来获取终端大小由于我将使用 ANSI 转义序列我认为这将是一种比获取终端大小更优雅的方法ioctl or getenv 这是一篇关于ioctl https stackoverfl
如何使用 Tailwind 修改 svg 图标颜色

我正在使用 TailwindCSS 并且想要更改 svg 的颜色没有 Tailwind 这个问题之前已经被问过here https stackoverflow com questions 22252472 how to change th
来自 AlertDialog 的 findViewById（使用自定义布局）- NullPointerException

我正在尝试从 AlertDialog 中的 EditTexts 中获取文本该对话框的创建方式如下所示问题是我无法检索文本视图我得到的只是一个空值有任何想法吗 final EditText editFirstname EditTex
Cassandra 要求允许过滤，即使列是聚集键

对 Cassandra 非常陌生如果问题很简单我们深表歉意我创建了一个表 create table ApiLog LogId uuid DateCreated timestamp ClientIpAddress varchar pri
强制多个线程在多个 CPU 可用时使用它们

我正在编写一个 Java 程序由于其工作性质该程序使用大量 CPU 然而其中很多可以并行运行并且我已经使我的程序成为多线程的当我运行它时它似乎只使用一个 CPU 直到它需要更多的 CPU 然后才使用另一个 CPU 我可以在 Ja
无法中断 global.asax / Application_Start

我在第一行有一个断点Application Start 但 Visual Studio 不会中断它 Visual Studio 已将自身附加到 IIS 工作进程自动附加到计算机 SRD00510 上的进程 2092 w3wp exe 已成
Mysql XML表导出命令行

也许我在这里没有看到什么但是为什么这个命令行在 LINUX 命令行上运行时工作得很好并提供结果集但在 WINDOWS 命令行上执行时它会失败并没有返回任何内容 mysql hHOSTNAME uroot p xml e SELECT
Python：连接多个组件来构建 URL

我正在尝试通过加入一些动态组件来构建 URL 我想用类似的东西os path join 但对于我来说的 URL 来说通过研究我发现urlparse urljoin 做同样的事情然而看起来一次只需要两个参数到目前为止我有以下有效但看
学习卡布奇诺

学习卡布奇诺的最佳来源是什么我从事传统网络开发但我对这个新框架非常感兴趣请注意我对 Objective C 毫无了解如上所述该网站是一个好地方但还有一些其他好的资源 http cappuccinocasts com htt
尽管环境变量设置正确，但 Android Studio 中“未找到 SDK 位置”

我有点困惑为什么在实际设置 ANDROID HOME 环境变量时收到此消息 It is also set in project defaults 我可能在这里遗漏了一些微不足道的东西但不确定是什么谢谢面临着同样的问题Intellij
BigQuery：通过 Java API 进行流式插入时出现错误 500

我正在尝试使用以下示例代码插入一行 https developers google com bigquery streaming data into bigquery streaminginsertexamples https develo
如何访问 Silverlight 4 中 App.xaml.cs 中创建的自定义属性

我在 app xaml cs 文件中创建了自己的自定义属性我需要在我的视图模型之一中访问此属性当我尝试通过应用程序对象访问时它不会显示谁能帮我这个 Regards Use Application Current访问您创建的自定义属性
如果大数组包含较小数组中的值，则查找大数组的索引

有没有快的numpy用于返回较大数组中的索引列表的函数其中它与较小数组中的值相匹配较小的数组约为 30M 值较大的数组为 800M 所以我想避免 for 循环numpy where calls searchsorted 的问题是即使
如何使用 numpy 创建单位矩阵

我如何创建一个身份matrix与numpy 有没有比以下更简单的语法 numpy matrix numpy identity n 这是一个更简单的语法 np matlib identity n 这是一个更简单的语法运行速度更快 In 1
MySQL 中出现 ERROR 1701、ERROR 1452 和 ERROR 1305 错误 - 需要一些专业知识

这是我第一次真正尝试用第三范式创建数据库我成功地创建了 DDL 脚本在继续构建脚本之前我已将逻辑模型验证为 3NF 但我遇到了很多错误我不确定是否可以修复对于这个特殊的情况我可能已经咬了一口但我不想放弃这对我来说是一个很大的

MySQL 中出现 ERROR 1701、ERROR 1452 和 ERROR 1305 错误 - 需要一些专业知识

MySQL 中出现 ERROR 1701、ERROR 1452 和 ERROR 1305 错误 - 需要一些专业知识 的相关文章

随机推荐

热门标签

MySQL 中出现 ERROR 1701、ERROR 1452 和 ERROR 1305 错误 - 需要一些专业知识的相关文章