CODEWITHSUNDEEP
sqlsqliteconstraints
Felix
Felix

Reputation: 2668

SQL constraint check if entry in another table

Based on an integer ID, how can one constrain two tables referencing a common table from including the same element already in the other table?

A basic table of, let's say personal information is created:

CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    info VARCHAR(10)
);

Then two tables referencing person is created:

CREATE TABLE special (
    id INTEGER PRIMARY KEY,
    skill VARCHAR(10),
    FOREIGN KEY (id) REFERENCES person(id)
);

CREATE TABLE snowflake (
    id INTEGER PRIMARY KEY,
    meltingpoint DECIMAL,
    FOREIGN KEY (id) REFERENCES person(id)
);

However, I want to constrain the two tables from including the same person.

So I thought something like this would do the trick, but SQLite3 gives syntax errors (near CHECK).

ALTER TABLE special ADD CHECK (
    (SELECT COUNT(*) FROM snowflake WHERE snowflake.id = special.id) = 0
);

OR based on this answer

ALTER TABLE special ADD CHECK (
    NOT EXISTS (SELECT 1 FROM snowflake WHERE snowflake.ID = special.ID)
);

How might this be achieved? Am I onto something or should an entirely different approach be taken?

Upvotes: 5

Views: 9797

Answers (3)

CL.
CL.

Reputation: 180060

The documentation says:

The expression of a CHECK constraint may not contain a subquery.

So you have to use triggers:

CREATE TRIGGER no_snowflake_if_special
AFTER INSERT ON snowflake
WHEN EXISTS (SELECT * FROM special WHERE id = NEW.id)
BEGIN
    SELECT RAISE(FAIL, "a special with the same ID already exists");
END;

-- same for special

Upvotes: 8

MikeT
MikeT

Reputation: 56938

You cannot use ALTER TABLE to add a CHECK to a column.

You can only use ALTER TABLE to ADD a column which can then have a CHECK constraint. You cannot rename a column nor delete a column.

The easiest method would be to define the CHECK in the column when creating the table. If you have data that needs to be kept, then you will have to :-

  • 1)create a new table,
  • 2) copy the data from the original to the new table,
  • 3) rename/drop the original and
  • 4) rename the new table to the name of the original table.

Upvotes: 0

Joakim Danielson
Joakim Danielson

Reputation: 51900

You could have two foreign keys in 'person' to both 'special' and 'snowflake' and a check that only one of the keys has a value. Another solution could be to join 'special' and 'snowflake' into one table and have a check that only one of 'skill' or 'meltingpoint' is given.

Upvotes: 0

Related Questions