mercredi 30 mars 2016

SQL Check constraint for dependent type

Suppose I have the following situation I want to model in a SQLite database. A color_type is a named set of color_channels, 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