SQLite variable CHECK constraint

Question

I have table with chromosomes (objects that have length) and table with regions (for example genes) on the chromosomes (objects that have range defined as two integers - position start and position end). I would like to forbid inserting into database regions with coordinates greater than length of particular chromosome.

Is it possible in SQLite? If not is it possible in any other SQL system (preferably free)?

DROP TABLE IF EXISTS chromosomes;
CREATE TABLE chromosomes
  (
     chromosome_id INTEGER UNIQUE NOT NULL CHECK(TYPEOF(chromosome_id) = 'integer'),
     name          VARCHAR UNIQUE NOT NULL CHECK(TYPEOF(name) = 'text'),
     length        INTEGER NOT NULL CHECK(TYPEOF(length) = 'integer' AND length > 0),
     PRIMARY KEY (chromosome_id)
  );


DROP TABLE IF EXISTS genes;
CREATE TABLE genes
  (
     gene_id           INTEGER UNIQUE NOT NULL CHECK(TYPEOF(gene_id) = 'integer'),
     symbol            VARCHAR NOT NULL CHECK(TYPEOF(symbol) = 'text'),
     refseq_id         VARCHAR NOT NULL CHECK(TYPEOF(refseq_id) = 'text'),
     chromosome_id     INTEGER NOT NULL CHECK(TYPEOF(chromosome_id) = 'integer'),
     start             INTEGER NOT NULL CHECK(TYPEOF(start) = 'integer' AND start > 0 AND start < end),
     end               INTEGER NOT NULL CHECK(TYPEOF(end) = 'integer' AND end > 0 AND end > start),
     external_db_link  VARCHAR NOT NULL CHECK(TYPEOF(external_db_link) = 'text'),
     PRIMARY KEY (gene_id)                   
     FOREIGN KEY (chromosome_id) REFERENCES chromosomes(chromosome_id)
  );

Some sample data might help you here, even for those who have a biochemistry background. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 10, 2017 at 11:06
sqlite does have constraints. And it's perhaps a good idea to do a type check like you are doing because sqlite doesn't other wise enforce types! — e4c5
– e4c5, Commented Jan 10, 2017 at 11:08
In Postgres you can define exclusion constraints on range types. Not sure if that would work here (I know nothing about the representation of chromosomes). Can you edit your question and add some sample data and how the constraint should apply to that? In Postgres you wouldn't need things like CHECK(TYPEOF(length) = 'integer' either) — user330315
– user330315, Commented Jan 10, 2017 at 14:35
Would you accept a solution involving deletion of a newly inserted record that violates the condition? — Bill Bell
– Bill Bell, Commented Jan 10, 2017 at 20:08

marc_s · Accepted Answer · 2017-01-19 11:30:46Z

1

This type of constraint is not easily available in any database. In general, this would be handled using a trigger. The problem is that it is a constraint between two tables, but it does not use equality.

Triggers are available in SQLite as well as other databases.

One work-around is a check constraint using a user-defined function. The function can do the lookup into the chromosomes table and be used in a check constraint. SQLite doesn't really have user-defined functions. One database that supports this is Postgres.

Another option is to wrap all data modifications in stored procedures (this tends to be the way that I design systems). Then the stored procedure can do all the checks that are needed.

edited Jan 19, 2017 at 11:30

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 10, 2017 at 11:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

monojohnny · Accepted Answer · 2022-09-12 16:23:09Z

0

Redundantly - bring 'length' into the child table using a foreign key. Then your Check Constraint can reference that.

answered Sep 12, 2022 at 16:23

monojohnny

6,22117 gold badges65 silver badges88 bronze badges

Collectives™ on Stack Overflow

SQLite variable CHECK constraint

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related