MySQL Family Tree

Question

Any idea on these syntax errors? Related to FOREIGN KEY syntax? No idea! I removed the ENGINE=INNODB; lines but it still isn't working.

DROP TABLE IF EXISTS Marriage;
CREATE TABLE Marriage (
    marriageID int NOT NULL,
    PRIMARY KEY(marriageID),
    date DATE,
    place varchar(100)
);

That top one works fine--the real trouble begins with the next one's FOREIGN KEYS:

DROP TABLE IF EXISTS MarriagePerson;
CREATE TABLE MarriagePerson (
    marriagePersonID int NOT NULL,
    marriageID int,
    personID int,
    PRIMARY KEY(marriagePersonID),  
    FOREIGN KEY marriageID REFERENCES Marriage(marriageID),
    FOREIGN KEY personID REFERENCES Person(personID)
);

DROP TABLE IF EXISTS Person;
CREATE TABLE Person (
     personID int NOT NULL,
     PRIMARY KEY(personID),
     firstName varchar(100),
     lastName varchar(100),
     gender  ENUM(male, female, nonBinary),
     birthDate DATE,
     birthPlace varchar(100),
     deathDate DATE,
     deathPlace varchar(100),
     causeOfDeath varchar(100),
     note varchar(1000)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS Parent;
CREATE TABLE Parent (
    parentID int NOT NULL,
    PRIMARY KEY(parentID),
    FOREIGN KEY personID int,
    FOREIGN KEY parentPersonID int,
    relationship ENUM(sperm, egg)
) ENGINE=InnoDB;

You have some foreign keys in the `Parent` table, but they don't reference anything. — Tim Biegeleisen, Feb 28 '17 at 06:32

score 1 · Answer 1 · answered Feb 28 '17 at 06:36

1

If you are running it in the same order as you have posted,

In the second query you have:

FOREIGN KEY personID REFERENCES Person(personID)

Which is not created yet. The Person table is created in the next query.

answered Feb 28 '17 at 06:36

Krishnakumar

725
1
6
11

MySQL Family Tree

1 Answers1