problem with updateable view and constraint
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
Dear All<br>
<span lang="EN-GB" style="">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:<o:p></o:p></span>
<p class="MsoNormal"><span lang="EN-GB"
style="font-family: "Courier New";">Table1:<br>
| A | B |<br>
---------<br>
| 1 | 1 |<br>
| 2 | 3 |<br>
| 3 | 1 |<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"
style="font-family: "Courier New";"><!--[if !supportEmptyParas]--> <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="FR" style="font-family: "Courier New";">Table2<br>
| C |<br>
-----<br>
| 1 |<br>
| 2 |<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="FR" style="font-family: "Courier New";"><!--[if !supportEmptyParas]-->
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"
style="font-family: "Courier New";">Table3<br>
| D | E |<br>
---------<br>
| 1 | 1 | case 1<br>
| 2 | 3 | case
2<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"
style="font-family: "Courier New";"><!--[if !supportEmptyParas]--> <o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="">Table3 has
constraint:<br>
FOREIGN KEY
E REFERENCES Table2 (C) ON UPDATE NO ACTION ON DELETE RESTRICT;<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style=""><!--[if !supportEmptyParas]-->
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="">I use
updateable view “View1” (due to PostGIS interface) with columns “C” and
“E” and
rule:<br>
AS ON
INSERT TO View1 DO INSTEAD (INSERT INTO Table2 VALUES (NEW.C); INSERT
INTO
Table3 VALUES (NEW.D, NEW.E));<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style=""><!--[if !supportEmptyParas]-->
<o:p></o:p></span></p>
<span lang="EN-GB"
style="font-size: 12pt; font-family: "Times New Roman";">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?<br>
Thanks a lot in advance<br>
Oleg<br>
</span>
</body>
</html>
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
Show quoted text
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 2Table3 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
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
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.
Show quoted text
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
OlegDear 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 2Table3 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
Thank you very much. With DEFERRABLE INITIALLY DEFERRED (at the end of
the constraint) it works fine now
Sebastian B�ck schrieb:
Show quoted text
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
OlegHave you tried to make the Foreign Key deferrable and initially deferred?
See:
http://www.postgresql.org/docs/8.0/interactive/sql-createtable.htmlHTH
Sebastian