Обсуждение: updating data but the constraint is set "immediate"
Hi, I've realised that my tables' constraint was set to IMMEDIATE. So, when I update one table, there's error because of referential integrity. Reading the docs, I read that SET CONSTRAINTS has no effect on IMMEDIATE (and I've confirmed that by trying it out). Is there anyway I can circumvent this? I remembered I can delete using pgAdminII. However, I don't have a windows box now, and therefore I can't use pgAdminII. Can anyone help? __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com
On Tue, Jul 01, 2003 at 02:35:48 -0700, Rudy Koento <rudy_koento@yahoo.com> wrote: > Hi, > > I've realised that my tables' constraint was set to > IMMEDIATE. So, when I update one table, there's error > because of referential integrity. Reading the docs, I > read that SET CONSTRAINTS has no effect on IMMEDIATE > (and I've confirmed that by trying it out). > > Is there anyway I can circumvent this? I remembered I > can delete using pgAdminII. However, I don't have a > windows box now, and therefore I can't use pgAdminII. You can use alter table to drop the constraint and then to add the corrected constraint. I am not sure if this was available prior to 7.3.
On Tue, 1 Jul 2003, Rudy Koento wrote: > I've realised that my tables' constraint was set to > IMMEDIATE. So, when I update one table, there's error > because of referential integrity. Reading the docs, I > read that SET CONSTRAINTS has no effect on IMMEDIATE > (and I've confirmed that by trying it out). Actually SET CONSTRAINTS has no effect on NOT DEFERRABLE constraints (which is the default for initially immediate iirc but not required). > Is there anyway I can circumvent this? I remembered I The best way is probably to drop and re-add the constraint with the attributes you want (possibly deferrable initially immediate if you want to be normally immediate but with the possibility of set constraints)
--- Bruno Wolff III <bruno@wolff.to> wrote: > On Tue, Jul 01, 2003 at 02:35:48 -0700, > Rudy Koento <rudy_koento@yahoo.com> wrote: > > Hi, > > > > I've realised that my tables' constraint was set > to > > IMMEDIATE. So, when I update one table, there's > error > > because of referential integrity. Reading the > docs, I > > read that SET CONSTRAINTS has no effect on > IMMEDIATE > > (and I've confirmed that by trying it out). > > > > Is there anyway I can circumvent this? I > remembered I > > can delete using pgAdminII. However, I don't have > a > > windows box now, and therefore I can't use > pgAdminII. > > You can use alter table to drop the constraint and > then to add the corrected > constraint. I am not sure if this was available > prior to 7.3. I solved it by using pgAdminII remotely through ssh port forwarding. I'm curious though, as to why does pgAdminII is able to defer the constraints whereas using command line cannot... __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com