CODEWITHSUNDEEP
sqldatabase-designforeign-keysdb2constraints
huntmaster
huntmaster

Reputation: 258

Adding a non-unique foreign key constraint in DB2

I have these two (simplified to illustrate the question) tables:

create table CASE_INSTANCE (id int not null, stage_id int not null,
     primary key (id, stage_id));    
create table SAMPLE (id int not null, case_id int not null);

I want to add a constraint on SAMPLE so that CASE_ID must be a valid value of CASE_INSTANCE.ID . However, I can't do this with a foreign key, because CASE_INSTANCE.ID is not unique. How can I add a constraint to SAMPLE that will enforce this relationship?

This is for DB2.

Upvotes: 0

Views: 907

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

Add another table where case_id is unique, then reference it both from CASE_INSTANCE and SAMPLE.

Upvotes: 1

mustaccio
mustaccio

Reputation: 19001

If you're not willing to change the model, I'm afraid you'll have to enforce this with a trigger or in the application. However, if something depends on a case as opposed to case instance, it should be a child of that case, not the case instance. If you don't have a table where the case ID is unique, I'd say there's something wrong with your model.

Upvotes: 0

Related Questions