sql - 父亲为男性的外键，母亲为女性的外键，您不能成为自己的母亲/父亲

我有3张 table :

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

我的问题是“FatherID”和“MotherID”上的外键约束，它们引用了他们自己的表。是否可以只允许外键，其中“M”在“FatherID”的性别列中，“F”在“MotherID”中？是否可以禁止母亲/父亲引用同一行？

基本上:父亲的必须是男性。妈妈一定是女的。你不能成为你自己的母亲/父亲。

最佳答案

我相信 SQLite 不支持包含从其他行动态获取值的表达式的约束，外键除外。

您必须创建触发器来检查父亲和母亲的性别。

使用这个表定义:

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

这可能是 INSERT 触发器(我会让你写 UPDATE 一个):

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;

一些简单的测试:

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

关于sql - 父亲为男性的外键，母亲为女性的外键，您不能成为自己的母亲/父亲，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12237660/