Re: deactivating/activating constraint
Re: deactivating/activating constraint
От:
Csaba Nagy <nagy@ecircle-ag.com>
Дата:
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
problem with updateable view and constraint
От:
Oleg <evdakov@iwk.uka.de>
Дата:
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
Olegdeactivating/activating constraint
От:
Oleg <evdakov@iwk.uka.de>
Дата:
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
Re: deactivating/activating constraint
От:
Sebastian Böck <sebastianboeck@freenet.de>
Дата:
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 Have you tried to make the Foreign Key deferrable and initially deferred? See: http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html HTH Sebastian
Re: deactivating/activating constraint
От:
Oleg <evdakov@iwk.uka.de>
Дата:
Thank you very much. With DEFERRABLE INITIALLY DEFERRED (at the end of the constraint) it works fine now Sebastian Böck schrieb: > 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 > > > Have you tried to make the Foreign Key deferrable and initially deferred? > > See: > http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html > > HTH > > Sebastian >
