CREATE TABLE source (
type CHAR(1) NOT NULL CHECK (type IN ('J', 'B')),
id INT NOT NULL,
PRIMARY KEY (type, id)
);
CREATE TABLE book (
type CHAR(1) NOT NULL CHECK(type = 'B'), id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (type, id) REFERENCES source (type, id) ON DELETE CASCADE
);
CREATE TABLE journal (
type CHAR(1) NOT NULL CHECK(type = 'J'), id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (type, id) REFERENCES source (type, id) ON DELETE CASCADE
);
CREATE TABLE scan (id INT NOT NULL, sourcetype CHAR(1) NOT NULL, sourceid INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (sourcetype, sourceid) REFERENCES source (type, id)
);
在这种设计中,您不应该直接从
book
或
journal
:相反,从表中删除
source
将操作级联到相应的表。
可以将常见的属性移动到
书
和
杂志
到
来源
.
