I have two tables, one of student and one of staff that look as such:

create table student (
id int not null primary key
)
create table staff (
id int not null primary key
)

I want the id in each to be unique. I know this isn't how it should be in production but I'm just trying to see why my check constraint doesn't work and I'm using a simpler example to explain.

I then alter the tables to include the check as follows:

alter table student add constraint not_staff check (id not in (select id from staff))
alter table staff add constraint not_student check (id not in (select id from student))

These checks seem to be invalid.

My question is whether we're allowed to have these kinds of SQL statements inside of a check constraint. If so, why is the above constraint invalid and how would I go about fixing it.

Thanks!

CodePudding user response：

Create a user-defined function that does the check, and call it from the table check constraint.

Alternatively you could use a database trigger.

CodePudding user response：

You can't use queries in a check constraint in Db2. Refer to the description of the CREATE TABLE statement.

CHECK (check-condition)
Defines a check constraint. The search-condition must be true or unknown for every row of the table.
search-condition
The search-condition has the following restrictions:
...

• The search-condition cannot contain any of the following (SQLSTATE 42621):
  • Subqueries

The easiest way to achieve your goal is not to create constraints, but create a sequence and use it in before triggers on both tables.