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 B990AD1C9AB for ; Tue, 3 Feb 2004 14:35:52 +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 47847-05 for ; Tue, 3 Feb 2004 10:35:46 -0400 (AST) Received: from smtp013.mail.yahoo.com (smtp013.mail.yahoo.com [216.136.173.57]) by svr1.postgresql.org (Postfix) with SMTP id 10048D1B80B for ; Tue, 3 Feb 2004 10:35:43 -0400 (AST) Received: from unknown (HELO europa.janwieck.net) (janwieck@68.80.245.191 with login) by smtp013.mail.yahoo.com with SMTP; 3 Feb 2004 14:35:46 -0000 Received: from Yahoo.com (ismtp.afilias.com [216.217.55.254]) (authenticated) by europa.janwieck.net (8.11.6/8.11.6) with ESMTP id i13EZgr02135; Tue, 3 Feb 2004 09:35:42 -0500 Message-ID: <401FA2EC.2090308@Yahoo.com> Date: Tue, 03 Feb 2004 08:32:28 -0500 From: Jan Wieck User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "scott.marlowe" Cc: Stephan Szabo , Tom Lane , Enio Schutt Junior , pgsql-sql@postgresql.org Subject: Re: Sometimes referential integrity seems not to work References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/30 X-Sequence-Number: 16721 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. Jan > > => select * from test2; > info > ------------- > abc'123 > 123 > (2 rows) > > => begin; > BEGIN > => truncate test2; > TRUNCATE TABLE > => rollback; > ROLLBACK > => select * from test2; > info > ------------- > abc'123 > 123 > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #