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.

Answer 1

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         

Answer 2

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');