Lonkey Lonkey - 4 months ago 10
SQL Question

How to make sure that a foreign key is not set in another table in SQL?

I'm working with Oracle Database 12c Enterprise Edition.

I have three tables:

table producer (
pid int primary key,
name varchar2(50)
);

table movie (
mid int primary key,
pid varchar2(50),
name varchar2(50)
);

table moviecoproducers (
mid int,
pid int
);


The pid in the movies table such as the pid and mid in the moviecoproducers table are referenced by an appropriate foreign key. In addition the combination of the mid and pid values in the moviecoproducers table is unique.

I now have to ensure that no producer is referenced as (main) producer in the movie table and as co-producer in the moviecoproducer intermediate table for the same movie. I created a trigger to do that but now I'm asking myself if there would be a more eazy solution (e.g. a check constraint) if I would redesign my table structure.

Do I only need a trigger because of a bad design because I have no idea how to do it in a different/better way.

Answer

Create a typical 3-table many-to-many structure.

table producer (
  pid int primary key,
  name varchar2(50)
);

table movie (
  mid int primary key,
  name varchar2(50)
);

table movieproducer (
  mid int,
  pid int,
  status varchar2(10)
)

status will hold "main" or "co" values.

Now you can manage your restrictions with table constraints and no triggers.

An added bonus: if at some time in the future you want to introduce something like "junior co-producers" you would not need to alter your schema.

This design is not fully normalized: lack of status table. See if you are interested in adding it in.