X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 68E9ED1D8A0 for ; Tue, 3 Feb 2004 16:38:23 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 09180-01 for ; Tue, 3 Feb 2004 12:38:17 -0400 (AST) Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222]) by svr1.postgresql.org (Postfix) with ESMTP id 2E563D1D2D0 for ; Tue, 3 Feb 2004 12:38:16 -0400 (AST) Received: from css120.ihs.com (css120.ihs.com [170.207.105.120]) by mail1.ihs.com (8.12.10/8.12.10) with ESMTP id i13GZmd1024738; Tue, 3 Feb 2004 09:35:48 -0700 (MST) Date: Tue, 3 Feb 2004 09:31:52 -0700 (MST) From: "scott.marlowe" To: Jan Wieck Cc: Stephan Szabo , Tom Lane , Enio Schutt Junior , Subject: Re: Sometimes referential integrity seems not to work In-Reply-To: <401FA2EC.2090308@Yahoo.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-IHS-MailScanner: Found to be clean X-IHS-MailScanner-SpamCheck: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/34 X-Sequence-Number: 16725 On Tue, 3 Feb 2004, Jan Wieck wrote: > scott.marlowe wrote: > > > On Mon, 2 Feb 2004, Jan Wieck wrote: > > > >> Stephan Szabo wrote: > >> > >> > On Sat, 31 Jan 2004, Tom Lane wrote: > >> > > >> >> Stephan Szabo writes: > >> >> > On Mon, 26 Jan 2004, Enio Schutt Junior wrote: > >> >> >> In a database I am working, I sometimes have to delete all the records in > >> >> >> some tables. According to the referential integrity defined in the creation > >> >> >> of the tables, postmaster should not delete the records, but it does. I have > >> >> >> used the following commands: "delete from table_1" and "truncate table_1". > >> >> >> ... > >> >> >> can the postgres user delete records despite referential integrity? > >> >> > >> >> I think the first PG release or two that had TRUNCATE TABLE would allow > >> >> you to apply it despite the existence of foreign-key constraints on the > >> >> table. Recent releases won't though. > >> > > >> > Yeah, truncate didn't worry me much, but the implication that delete from > >> > table_1; worked did. > >> > >> TRUNCATE cannot be used inside of a transaction, and since 7.3 it checks > >> for foreign keys. So I guess Enio is getting but ignoring the error > >> message when trying the delete, but then the truncate does the job in > >> his pre-7.3 database. > > > > Yes it can. I think it was starting in 7.3. > > Okay, so you're the third one correcting me on this. Now can any of you > violate a foreign key constraint with anything else than using truncate > in a pre-7.3 database? Because I can't do that and that was the original > problem. OK, I just tested the truncate foreign key truncate on 7.2, and other than truncate, I've not found any way to delete the fk data from the parent table.