Foreign key for Father to be male, Mother to be female and you can't be your own mother/father

Question

I have 3 tables:

CREATE TABLE "Names" (
"Name" TEXT(20) NOT NULL,
"Gender" TEXT(20) NOT NULL,
PRIMARY KEY ("Name", "Gender") 
);

CREATE TABLE "Snames" (
"Sname" TEXT(20) NOT NULL,
PRIMARY KEY ("Sname") 
);

CREATE TABLE "People" (
"ID" INTEGER NOT NULL,
"Name" TEXT(20) NOT NULL,
"Sname" TEXT(20) NOT NULL,
"Gender" TEXT(1) NOT NULL,
"FatherID" INTEGER,
"MotherID" INTEGER,
PRIMARY KEY ("ID") ,
CONSTRAINT "Father" FOREIGN KEY ("FatherID") REFERENCES "People" ("ID"),
CONSTRAINT "Mother" FOREIGN KEY ("MotherID") REFERENCES "People" ("ID"),
CONSTRAINT "Sname" FOREIGN KEY ("Sname") REFERENCES "Snames" ("Sname"),
CONSTRAINT "Name" FOREIGN KEY ("Name", "Gender") REFERENCES "Names" ("Name", "Gender")
);

My problem is with the foreign key constraints on "FatherID" and "MotherID", which reference their own table. Is it possible to only allow foreign keys where "M" is in the Gender column for "FatherID", and "F" for "MotherID"? And is it possible to disallow the Mother/Father to reference the same row?

BASICALLY: Father's must be male. Mother's must be female. You can't be your own mother/father.

Names is a table of about 5000 firstnames with genders. Snames is a table of about 5000 lastnames. People is a small table of people, but their names reference the other two tables. I don't really think that's important though. — Ashley, Sep 02 '12 at 16:41
I am not sure FatherID and MotherID is actually required to store if you go by normalization rules. you can easily calculate that at run time by querying gender column. — sqlhdv, Sep 02 '12 at 16:42
I don't know sqllite but imagine that `CHECK ("ID" NOT IN ("FatherID", "MotherID"))` should do the last bit (You can't be your own mother/father). — Martin Smith, Sep 02 '12 at 16:42
@Ashley, since you are looking up the last name with the name it self, you aren't really gaining anything from extracting it into it's own table. — Jesper Fyhr Knudsen, Sep 02 '12 at 16:48
@Arkain: You gain the ability to constrain the values in people.sname to those values in snames.sname. — Mike Sherrill 'Cat Recall', Sep 02 '12 at 16:57

score 3 · Accepted Answer · 2012-09-02T17:51:34.143

I believe SQLite doesn't support constraints that contain expressions with values obtained dynamically from other rows, with the notable exception of foreign keys.

You will have to create triggers to check the gender of the father and mother.

Using this table definition:

CREATE TABLE "People" (
    "ID" INTEGER NOT NULL,
    "Name" TEXT(20) NOT NULL,
    "Sname" TEXT(20) NOT NULL,
    "Gender" TEXT(1) NOT NULL,
    "FatherID" INTEGER,
    "MotherID" INTEGER,
    PRIMARY KEY ("ID") ,
    CONSTRAINT "Father" FOREIGN KEY ("FatherID") REFERENCES "People" ("ID"),
    CONSTRAINT "Mother" FOREIGN KEY ("MotherID") REFERENCES "People" ("ID"),
    CHECK (Gender IN ('M', 'F')),
    CHECK ("ID" NOT IN ("FatherID", "MotherID")));

this could be the INSERT trigger (I'd let you write the UPDATE one):

CREATE TRIGGER checkParentIdsOnInsert BEFORE INSERT ON People 
    WHEN new.FatherID IS NOT NULL OR new.MotherID IS NOT NULL
BEGIN
    SELECT CASE     
    WHEN ((SELECT Gender FROM People AS t1 WHERE t1.ID=new.FatherID) = 'F' 
            AND (SELECT Gender FROM People AS t2 WHERE t2.ID=new.MotherID) = 'M')
       THEN RAISE(ABORT, 'Father must be male and mother female') 
    WHEN ((SELECT Gender FROM People AS t3 WHERE t3.ID=new.FatherID) = 'F')
       THEN RAISE(ABORT, 'Father must be male') 
    WHEN ((SELECT Gender FROM People AS t4 WHERE t4.ID=new.MotherID) = 'M')
       THEN RAISE(ABORT, 'Mother must be female') 
    END; 
END;

Some simple tests:

sqlite> pragma foreign_keys=on;
sqlite> INSERT INTO People (Name, SName, Gender, FatherID, MotherID) VALUES
   ...>     ("Jo", "Blo", "M", NULL, NULL);
sqlite> INSERT INTO People (Name, SName, Gender, FatherID, MotherID) VALUES
   ...>     ("Za", "Bla", "F", NULL, NULL);
sqlite> INSERT INTO People (Name, SName, Gender, FatherID, MotherID) VALUES
   ...>     ("Bad", "Kid", "M", 2, 1);
Error: Father must be male and mother female
sqlite> INSERT INTO People (Name, SName, Gender, FatherID, MotherID) VALUES
   ...>     ("Bad", "Kid", "M", 2, NULL);
Error: Father must be male
sqlite> INSERT INTO People (Name, SName, Gender, FatherID, MotherID) VALUES
   ...>     ("Bad", "Kid", "M", NULL, 1);
Error: Mother must be female
sqlite> INSERT INTO People (Name, SName, Gender, FatherID, MotherID) VALUES
   ...>     ("Good", "Kid", "M", 1, 2);
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM People;
ID          Name        Sname       Gender      FatherID    MotherID  
----------  ----------  ----------  ----------  ----------  ----------
1           Jo          Blo         M                                 
2           Za          Bla         F                                 
3           Good        Kid         M           1           2

Martin Smith · Answer 2 · 2012-09-02T17:47:45.480

The following should work though requires a couple of otherwise redundant columns for the foreign key (SQL Fiddle)

CREATE TABLE "People" (
"ID" INTEGER NOT NULL,
"Name" TEXT(20) NOT NULL,
"Sname" TEXT(20) NOT NULL,
"Gender" TEXT(1) NOT NULL,
"FatherID" INTEGER NULL,
"FatherGender" TEXT(1) NULL,
"MotherID" INTEGER NULL,
"MotherGender" TEXT(1) NULL,
PRIMARY KEY ("ID") ,
UNIQUE ("ID", "Gender"),
CHECK ("ID" NOT IN ("FatherID", "MotherID")),
CHECK ("FatherGender" = 'M'),
CHECK ("MotherGender" = 'F'),
CONSTRAINT "Father" FOREIGN KEY ("FatherID","FatherGender") REFERENCES "People" ("ID", "Gender"),
CONSTRAINT "Mother" FOREIGN KEY ("MotherID","MotherGender") REFERENCES "People" ("ID", "Gender")
);

INSERT INTO "People"
VALUES(1, 'Adam', '?', 'M', NULL, NULL, NULL, NULL);

INSERT INTO "People"
VALUES(2, 'Eve', '?', 'F', NULL, NULL, NULL, NULL);

INSERT INTO "People"
VALUES(3, 'Cain', '?', 'M', 1, 'M', 2, 'F');

Nice trick. It is not a 100% match for this problem, but is useful otherwhere. — David Balažic, Feb 27 '14 at 15:09

djadmin · Answer 3 · 2012-09-02T17:03:37.593

1

To apply such type of Constraint, You need to use Triggers which can do the work

Edited: SQLite Supports Triggers. Thanks @catcall

edited Sep 02 '12 at 17:03

answered Sep 02 '12 at 16:52

djadmin

1,742
3
19
27

Foreign key for Father to be male, Mother to be female and you can't be your own mother/father

3 Answers3