Skip site navigation (1) Skip section navigation (2)

Re: TRUNCATE question

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: TRUNCATE question
Date: 2001-09-06 20:01:16
Message-ID: 3B97D60C.2FB7174D@mascari.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Bruce Momjian wrote:
> 
> Is there a TODO item here?

Yes. It should read:

"Disallow TRUNCATE TABLE on tables that are parents of a referential
integrity constraint"

In PostgreSQL current sources I can do:

CREATE TABLE employees (
 employeeid INTEGER PRIMARY KEY NOT NULL
);

CREATE TABLE salaries ( 
 employeeid INTEGER NOT NULL REFERENCES employees(employeeid),
 salary FLOAT NOT NULL
);

INSERT INTO employees VALUES (1);

INSERT INTO salaries VALUES (1, 45000);

TRUNCATE TABLE employees;

SELECT * FROM salaries;

 employeeid | salary 
------------+--------
          1 |  45000
(1 row)

In Oracle, the following occurs:

CREATE TABLE employees (
 employeeid INTEGER NOT NULL PRIMARY KEY
);

CREATE TABLE salaries (
 employeeid INTEGER NOT NULL REFERENCES employees(employeeid),
 salary FLOAT NOT NULL
);

INSERT INTO employees VALUES (1);

INSERT INTO salaries VALUES (1, 40000);

TRUNCATE TABLE employees;

TRUNCATE TABLE employees;
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled
foreign keys

Mike Mascari
mascarm(at)mascari(dot)com

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2001-09-06 20:11:40
Subject: Re: TRUNCATE question
Previous:From: Tom LaneDate: 2001-09-06 19:16:26
Subject: Re: Conditional NOTIFY is not implemented

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group