Suppose I have the following situation I want to model in a SQLite database. A color_type
is a named set of color_channel
s, which specify the meaning and meaningful values for their respective color_channel_value
. In this case a color
is a composite of color_type
and a set of color_channel_value
, one and only one for each color_channel
specified by color_type
. It is this constraint that I don't see how to enforce. The ddl might look like:
CREATE TABLE color_channel (name TEXT PRIMARY KEY);
CREATE TABLE color_type (name TEXT PRIMARY KEY);
CREATE TABLE color_type_channel (
channel_name TEXT NOT NULL REFERENCES color_channel(name),
type_name TEXT NOT NULL REFERENCES color_type(name)
)
CREATE TABLE color_channel_value (
id INTEGER PRIMARY KEY,
value REAL NOT NULL,
channel_name TEXT NOT NULL REFERENCES color_channel(name)
)
CREATE TABLE color (
id INTEGER PRIMARY KEY,
color_type_name TEXT NOT NULL REFERENCES color_type(name)
)
CREATE TABLE color_channel_membership (
color_id INTEGER NOT NULL REFERENCES(id),
color_channel_value_id INTEGER NOT NULL REFERENCES color_channel_value(id)
)
Now what I really want is for the following to be invariant to hold
CREATE VIEW color_channel_via_type AS
SELECT c.id, ctv.channel_name FROM color c
JOIN color_type_channel ctc ON c.color_type_name = ctc.type_name;
CREATE VIEW color_channel_via_values AS
SELECT c.id, ccv.channel_name FROM color c
JOIN color_channel_membership ccm ON c.id = ccm.color_id
JOIN color_channel_value ccv ON ccm.color_channel_value_id = ccv.id;
CHECK(SELECT COUNT(*) = (SELECT COUNT(*) FROM color_channel_via_type)
AND COUNT(*) = (SELECT COUNT(*) FROM color_channel_via_values)
FROM (SELECT * FROM color_channel_via_type
INTERSECT SELECT * from color_channel_via_value)
The question then is the best way to enforce this invariant. One problem is that since the constraint requires agreement over multiple rows it will in general take multiple row inserts to maintain this invariant (essentially, color
have to be added with a deferred constraint). I am not sure then whether the desired invariant can be enforced in SQLite.
Aucun commentaire:
Enregistrer un commentaire