title: Foreign key for Father to be male, Mother to be female and you can't be your own mother/father
body: <p>I have 3 tables:</p>
  
  <pre><code>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")
  );
  </code></pre>
  
  <p>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?</p>
  
  <p>BASICALLY: Father's must be male. Mother's must be female. You can't be your own mother/father.</p>