Re: deactivating/activating constraint

You might be looking for a DEFERRED constraint.
You can declare your constraint to be checked only at transaction end,
and then make all your data changes, in one transaction.

You will find details here:
http://www.postgresql.org/docs/8.0/static/sql-createtable.html
Search for DEFERRED.

HTH,
Csaba.

On Tue, 2005-09-20 at 16:26, Oleg wrote:
> Dear All,
> is it possible to temporary deactivate a constraint in PostgreSQL?
> There is a constraint that does not allow me to write some data (see
> e-mail below). But after all datasets are written the constraint is
> valid. So I was wondering wether it is possible to deactivate a
> constraint write all records in all tables then activate constraint
> again. Somebody told me that it is possible in Oracle.
> Thanks a lot in advance
> Oleg
>
> > Dear All
> > I am PostgreSQL beginner. I am trying to write some values from
> > external source table “Table1” (using PostGIS plugin) to 2 destination
> > PostgreSQL tables “Table2” and “Table3” that describes relations of data:
> >
> > Table1:
> > | A | B |
> > ---------
> > | 1 | 1 |
> > | 2 | 3 |
> > | 3 | 1 |
> >
> > Table2
> > | C |
> > -----
> > | 1 |
> > | 2 |
> >
> > Table3
> > | D | E |
> > ---------
> > | 1 | 1 | case 1
> > | 2 | 3 | case 2
> >
> > Table3 has constraint:
> > FOREIGN KEY E REFERENCES Table2 (C) ON UPDATE NO ACTION ON DELETE
> > RESTRICT;
> >
> > I use updateable view “View1” (due to PostGIS interface) with columns
> > “C” and “E” and rule:
> > AS ON INSERT TO View1 DO INSTEAD (INSERT INTO Table2 VALUES (NEW.C);
> > INSERT INTO Table3 VALUES (NEW.D, NEW.E));
> >
> > I faced following problem: As it is shown in Table3 while trying to
> > write data in case 1 everything works fine, but case 2 is not possible
> > due to constraint in Table3. However my aim is to write a column A to
> > column C, column A to column D and column B to column E not removing
> > constraint for table3. May be there is a way to adjust constraint? Or
> > may be to adjust rule somehow to make it to write all data from column
> > A to column C first and after that fill in Table3?
> > Thanks a lot in advance
> > Oleg
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings