Thread: Constraints/Triggers information?

I've got a snipped script like thus:

---------------------------------------------------
alter table license drop constraint fk_lic_lc restrict ;
drop table license ;
create table license (
  id numeric(18),
 . . .
) ;
alter table license add constraint fk_lic_lc
  foreign key (license_code_id) references license_code (id) ;
---------------------------------------------------

Where the final statement had previously run successfully:

---------------------------------------------------
psql:file.sql:284: NOTICE:
  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
---------------------------------------------------

The initial statement (alter table drop constraint) always fails with

---------------------------------------------------
psql:file.sql:22: ERROR:  ALTER TABLE / DROP CONSTRAINT:
  fk_lic_lc does not exist
---------------------------------------------------

I can find nothing in psql's "\?" that talks about either triggers or
referential integrity constraints. Doing a "\d license" shows me several
RI entries like thus:

---------------------------------------------------
Triggers: RI_ConstraintTrigger_5602314,
          RI_ConstraintTrigger_5602320,
          RI_ConstraintTrigger_5602326
---------------------------------------------------

But I can't get any information on the RI_ entries. What's wrong with me?
Do I need to RTFM? If so, which FM do I need to R?

--
Tim Ellis
DBA, Gamet

ps -- doing drop constraint with cascade keyword gives me:
---------------------------------------------------
psql:file.sql:22: ERROR:  ALTER TABLE / DROP CONSTRAINT
  does not support the CASCADE keyword
---------------------------------------------------

On Thu, 27 Jun 2002, Tim Ellis wrote:

> ---------------------------------------------------
> alter table license drop constraint fk_lic_lc restrict ;
> drop table license ;
> create table license (
>   id numeric(18),
>  . . .
> ) ;
> alter table license add constraint fk_lic_lc
>   foreign key (license_code_id) references license_code (id) ;
> ---------------------------------------------------
>
> Where the final statement had previously run successfully:
>
> ---------------------------------------------------
> psql:file.sql:284: NOTICE:
>   ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> ---------------------------------------------------
>
> The initial statement (alter table drop constraint) always fails with
>
> ---------------------------------------------------
> psql:file.sql:22: ERROR:  ALTER TABLE / DROP CONSTRAINT:
>   fk_lic_lc does not exist

Current PostgreSQL's drop constraint only works on check constraints.
To drop foreign keys, you have to do,
drop trigger "<triggername>" on <table> for the three triggers it
creates (2 on the pk table, 1 on the fk table).

> But I can't get any information on the RI_ entries. What's wrong with me?
> Do I need to RTFM? If so, which FM do I need to R?

Pretty much the most useful info on the triggers can be found on
techdocs.postgresql.org.  There are a couple of essays on them.