This is my first real crack at making a database in Third Normal Form. I managed to create the DDL script ok (I had the logical model verified to 3NF before I went ahead and built the script) but I get a whole lot of errors I am unsure of fixing. I may have bitten more than I can chew for this particular case but I don’t want to give up — it’s a big learning curve for me and with some help I can get past this and move on.
First of all, here is my DDL script:
-- 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;
And here is my DML script (note: so far the script only attempts to insert records in the «person», «school», and «player» details. If the problems I have encountered so far are fixed, this could help me for later on..):
-- 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"));
And here’s the output on the MySQL command line interface produced from the two scripts — that will show more information on the errors:
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:scriptsdatabase_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:scriptsdatabase_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>
And here’s the logical model (if anyone sees problems with any of the relationships — in particular, the many:many relationships, please let me know).
I apologise if I gave too much information, but I thought if I gave all this information it would make things easier on people that want to help me out. I have spent a good 3 hours making adjustments to the DDL script but after spending that long I realised I need someone with more experience to help me. I’ve showed the logical model to someone with industry experience in databases and they believe it conforms to 3NF. Doing the conceptual and logical models wasn’t so bad for myself, it was trying to convert the logical model to working script and successfully inserting data (I want to insert at least 10 records for each table) that has become the biggest hurdle.
Thanks in advance!
-
Distructor
- Администратор
- Сообщения: 1607
- Зарегистрирован: 28.12.2009
Иногда во время тестов внешние ключи мешают изменять данные:
Код: Выделить всё
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraintКод: Выделить всё
ERROR 1217 (23000): Cannot delete or update parent row: a foreign key constraint failsв этом случае можно временно отключить проверку внешних ключей:
Код: Выделить всё
-- отключаем проверку
SET foreign_key_checks = 0;
-- выполняем нужные запросы
DELETE FROM downloads WHERE id = 59;
-- включаем проверку назад
SET foreign_key_checks = 1; -
AgentSIB
- Сообщения: 329
- Зарегистрирован: 17.05.2010
Добавлю, что в PhpMyAdmin эту фитчу можно применять автоматически. Например, при удалении таблиц.
- Вложения
-
Frustra fit per plura quod potest fieri per pauciora © Закон «Бритвы Оккама»
MySQL Error code lair
Error Code: 1005. Can’t create table
MySQL could not create a table.
Two possible cases:
- Foreign key constraints (does the FK exist before creation?)
- Are you renaming an internal table ?
Error Code: 1044. Access denied for user ‘user’@’10.%.%.%’ to database ‘databaseName’
Troubleshooting
- DatabaseName: In mySQL database names are CaSe SeNsItIvE.
- Username incorrect?
- And finally.. permissions.
This is a quick fix for permissions:
grant all privileges on databaseName.* to 'user'@'%';
Error Code: 1075. Incorrect table definition; there can be only one auto column and it must be defined as a key
Troubleshooing: Error caused by copy-paste.
Error: 1095 SQLSTATE: HY000 (ER_KILL_DENIED_ERROR)
Message: You are not owner of thread %lu
Troubleshooting:
- Are you the owner of the thread you’re trying to kill?
- Do you have sufficient permissions to kill a thread lauched by another user?
Error Code: 1222. The used SELECT statements have a different number of columns
You are trying to make a UNION with two tables that have diferent number of columns.
How to reproduce:
SELECT 1,2,3,4
UNION
SELECT 1
Be careful with UNIONs and data types. You could end up with something unexpected
like the example below:
SELECT 1,2,3,4
UNION
SELECT 1.1, "hello", 3, 4
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails ( details about the foreign key )
Looks like someone defined foreign keys in the database…
People call this referential integrity.
This means in order to delete/update the table you’ll need to
delete/update other stuff first.
Error Code: 1630. FUNCTION schema_name.sum does not exist. Check the ‘Function Name Parsing and Resolution’ section in the Reference Manual
Looks like you have an space character between the function name and the opening parenthesis, like this:
SELECT sum (column) FROM ...
This applies not just to the sum function; but for any sql function.
How to reproduce:
Let’s make a 3 row table from scratch:
SELECT 1 as aa
UNION
SELECT 2 as aa
UNION
SELECT 3 as aa )
SELECT sum(uu.aa) FROM ( SELECT 1 as aa union select 2 as aa union select 3 as aa ) uu;
``
```{sql}
SELECT sum (1,2) ;
Error Code: 1701. Cannot truncate a table referenced in a foreign key constraint ( details about the foreign key constrain )
This is like error code 1451 above.
Foreign keys force you to update and remove data in a particular order.
Try to remove the data that refereces this table first.
Be warned MySQL can have cross-database constraints!
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
This is a MySQL Workbench error to avoid deleting data unexpectedly.
If you want to delete/update a whole table don’t look for the prefences option:
this error acts as your life vest and wi. The alternative is simple: just add
WHERE NOT NULL to your SQL statement.
Error Code: 1427. For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column ‘whatever’).
This happened trying to change a column from decimal(10,0) to decimal(3,7)…
… because I thought that (10,0) meant 10 digits total, zero decimal places
instead of 10 digits total, 0 of those are for decimals.
In case you’re wondering how to store properly store longitude/latitude take a look at this table:
| decimal places | decimal degrees |
DMS | qualitative scale that can be identified | resolution at equator |
|---|---|---|---|---|
| 0 | 1.0 | 1° 00′ 0″ | country or large region | 111.32 km |
| 1 | 0.1 | 0° 06′ 0″ | large city or district | 11.132 km |
| 2 | 0.01 | 0° 00′ 36″ | town or village | 1.1132 km |
| 3 | 0.001 | 0° 00′ 3.6″ | neighborhood, street | 111.32 m |
| 4 | 0.0001 | 0° 00′ 0.36″ | individual street, land parcel | 11.132 m |
| 5 | 0.00001 | 0° 00′ 0.036″ | individual trees | 1.1132 m |
| 6 | 0.000001 | 0° 00′ 0.0036″ | individual humans | 111.32 mm |
| 7 | 0.0000001 | 0° 00′ 0.00036″ | practical limit of commercial surveying | 11.132 mm |
| 8 | 0.00000001 | 0° 00′ 0.000036″ | specialized surveying (e.g. [[tectonic plate]] mapping) | 1.1132 mm |
Table source: Wikipedia: Decimal Degrees
Error Code: 2013. Lost connection to MySQL server during query
https://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query