Select subquery CHECK constraint DB2

Question

I have 2 tables: TVSHOW and ACTOR. I want to reject records if the inserted actor in TVSHOW does not exist in the ACTOR table using DB2.

db2 "ALTER TABLE TVSHOW ADD CONSTRAINT check_actor CHECK (actor in (select id from ACTOR))"

I keep running into, During SQL processing it returned: SQL0546N The check constraint "check_actor" is invalid.

How can I fix this?

Answer 1

You cannot use a subselect in a check constraint; you can only reference columns from the same table.

However, what you want to accomplish is what referential integrity constraints, also known as foreign keys, are for.

ALTER TABLE TVSHOW ADD CONSTRAINT actor_fk FOREIGN KEY (actor) REFERENCES ACTOR(id)

Presumably you have already defined a primary key constraint on ACTOR.id; if not, you need to define it, or a unique constraint on that column.