Java DB constraint

Question

I'm trying to create a constraint in java DB. I have two tables: task and subtask. These tables have 1:m relationship (task 1:m subtask). The constraint need to check that the start_date in subtask is not earlier than the start date in the task table. Not sure, how this can be done. My code:

ALTER TABLE subtask
ADD CONSTRAINT c1 CHECK(
    s.start_date >= (SELECT t.start_date
                        FROM task t, subtask s
                        WHERE t.start_date = s.start_date)
)

This gives me error: 'subquery' may not appear in a CHECK CONSTRAINT definition because it may return non-deterministic results.

How can I express the constraint in SQL? Any help is appreciated.

Which DBMS are you using? – Lucia Pasarin Aug 28 '13 at 20:43 — Lucia Pasarin, Aug 28 '13 at 20:43

score 1 · Accepted Answer · edited May 23 '17 at 12:05

I don't now about Derby but almost no DBMS (except Access which has issues) supports subqueries in CHECK constraints or (a similar concept) ASSERTIONs which are constraints that involve more than one tables (Firebird documentation says it does support these).

You can read some details about the difficulties involved in this fine answer: Why don't DBMS's support ASSERTION

One way to enforce your constraint declaratively is to add a (redundant) task.start_date column in table subtask as well and change the foreign key constraint to include that column. Sample table definitions:

CREATE TABLE task
( task_id INT NOT NULL PRIMARY KEY
, task_start_date DATE NOT NULL
, CONSTRAINT task_id_start_date_UQ
    UNIQUE (task_id, task_start_date)
) ;

CREATE TABLE subtask
( subtask_id INT NOT NULL PRIMARY KEY
, start_date DATE NOT NULL
, task_id INT NOT NULL
, task_start_date DATE NOT NULL
, CONSTRAINT task_subtask_FK
    FOREIGN KEY (task_id, task_start_date)
    REFERENCES task (task_id, task_start_date)
, CONSTRAINT subtask_should_not_start_before_task_CK
    CHECK (start_date >= task_start_date)
) ;

Java DB constraint

1 Answers1