CODEWITHSUNDEEP
sqloracle-databasedatabase-design
Jack Cassidy
Jack Cassidy

Reputation: 159

SQL allowing an entity to participate in a relationship only if it meets certain requirements

I have a practice Super Hero SQL database with a SuperPerson table and a Disaster table. There are two relationships between SuperPerson and Disaster, Create and Prevent. The SuperPerson table has a Disposition column that tells us if they are evil or good. How can I make it so that only a SuperPerson that is good can participate in the Prevent relationship and vice versa for the Create relationship?

I've thought about creating two new tables, SuperHero and SuperVillian that reference the SuperPerson table, is this the most logical thing to do or is there some PL/SQL that I can use to enforce this constraint?

Edit: Here's a link to a schema of the database so far

Upvotes: 3

Views: 99

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

The ideal would be to define the foreign key as:

add constraint fk_disaster_create
    foreign key (create, 'good') references superperson (superpersonid, disposition);

Unfortunately, constants are not allowed for such references. One method is to define a fixed column with the two values:

alter table disaster add disposition_good varchar2(255) default 'good';
alter table disaster add disposition_evil varchar2(255) default 'evil';

alter table disaster add constraint fk_disaster_create
    foreign key (create, 'good') references superperson (superpersonid, disposition);

Upvotes: 1

Related Questions