Thread: referential integrity for insert

i got two tables with the following structures:
create table unit (
  unit_id int4 not null primary key,
  unit_name varchar(10) not null)

create table plant_unit (
  pu_id int4 not null primary key,
  pu_code varchar(15),
  unit_id int4 not null references unit (unit_id)
match all on delete restrict on update cascade)

that means the two tables are connected to each other
by the unit_id field. i cannot delete records from the
unit table if there is a relation in the plant_unit
table, and when i update the unit_id field in the unit
table the system will update all matching records in
the plant_unit table as well.

my question is how can i avoid to insert record into
the plant_unit table, when the specified unit_id is
not available in the unit table.

do i have to use trigger, or i miss something in the
constraint, or whatelse?

thanks

Ivan


__________________________________________________
Do You Yahoo!?
Try FREE Yahoo! Mail - the world's greatest free email!
http://mail.yahoo.com/

Ivan,

> i got two tables with the following structures:
> create table unit (
>   unit_id int4 not null primary key,
>   unit_name varchar(10) not null)
>
> create table plant_unit (
>   pu_id int4 not null primary key,
>   pu_code varchar(15),
>   unit_id int4 not null references unit (unit_id)
> match all on delete restrict on update cascade)

OK, first, you don't need the "Match All" in the referential
 constraint.  That applies only to multi-column foriegn keys (though I
 don't think it does any harm here).

> my question is how can i avoid to insert record into
> the plant_unit table, when the specified unit_id is
> not available in the unit table.
>
> do i have to use trigger, or i miss something in the
> constraint, or whatelse?

Ummm ... nothing else.  Try inserting a record into plant_unit with no
 matching unit record.  You'll get an error.

-Josh