Re: referential integrity for insert

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