这是我第一次真正尝试以第三范式制作数据库。我成功地创建了 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;
关于mysql - 在 MySQL 中出现 ERROR 1701、ERROR 1452 和 ERROR 1305 错误 - 需要一些专业知识，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10258515/
我正在使用这个:4.times{|i|assert_not_equal("content#{i+2}".constantize,object.first_content)}我之前声明过局部变量content1content2content3content4content5我得到的错误NameError:wrongconstantnamecontent2这个错误是什么意思？我很确定我想要content2=\ 最佳答案 你必须用一个大字母来调用ruby常量:Content2而不是content2。Aconstantnamestart
我克隆了一个rails仓库，我现在正尝试捆绑安装背景:OSXElCapitanruby2.2.3p173(2015-08-18修订版51636)[x86_64-darwin15]rails-v在您的Gemfile中列出的或native可用的任何gem源中找不到gem'pg(>=0)ruby'。运行bundleinstall以安装缺少的gem。bundleinstallFetchinggemmetadatafromhttps://rubygems.org/............Fetchingversionmetadatafromhttps://rubygems.org/...Fe
我在新的Debian6VirtualBoxVM上安装RVM时遇到问题。我已经安装了所有需要的包并使用下载了安装脚本(curl-shttps://rvm.beginrescueend.com/install/rvm)>rvm，但以单个用户身份运行时bashrvm我收到以下错误消息:ERROR:Unabletocheckoutbranch.安装在这里停止，并且(据我所知)没有安装RVM的任何文件。如果我以root身份运行脚本(对于多用户安装)，我会收到另一条消息:Successfullycheckedoutbranch''安装程序继续并指示成功，但未添加.rvm目录，甚至在修改我的.bas
文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co
我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin
我正在关注Hartl的railstutorial.org并已到达11.4.4:Imageuploadinproduction.我做了什么:注册亚马逊网络服务在AmazonIdentityandAccessManagement中，我创建了一个用户。用户创建成功。在AmazonS3中，我创建了一个新存储桶。设置新存储桶的权限:权限:本教程指示“授予上一步创建的用户读写权限”。但是，在存储桶的“权限”下，未提及新用户名。我只能在每个人、经过身份验证的用户、日志传送、我和亚马逊似乎根据我的名字+数字创建的用户名之间进行选择。我已经通过选择经过身份验证的用户并选中了上传/删除和查看权限的框(而不
我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi
我在en:语言环境中有一个字符串display_device:toplay:getplayer冒号给我的错误是cannotloadtranslationsfromC:/DocumentsandSettings/rajg/discoveryaws/branches/internationalization/config/locales/en.yml,expectedittoreturnahash,butdoesnot我怎样才能让它工作？ 最佳答案 如果你用这样的引号将它括起来，你可以在你的字符串中转义冒号(和其他“重要”字符):dis
我使用RVM安装Ruby-2.1.5并再次运行bundle。现在pggem不会安装，我得到这个错误:geminstallpg-v'0.17.1'----with-pg-config=/Applications/Postgres.app/Contents/Versions/9.3/bin/pg_configBuildingnativeextensionswith:'--with-pg-config=/Applications/Postgres.app/Contents/Versions/9.3/bin/pg_config'Thiscouldtakeawhile...ERROR:Error
我是Rails的新手，我遇到了一个错误，但我似乎找不到问题所在。这是日志:[32651:ERROR]2012-10-0913:46:52::comparisonofFloatwithFloatfailed[32651:ERROR]2012-10-0913:46:52::/home/sunny/backend/lib/analytics/lifetime.rb:45:in`each'/home/sunny/backend/lib/analytics/lifetime.rb:45:in`max'/home/sunny/backend/lib/analytics/lifetime.rb:45