Re: TRUNCATE question

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@mascari.com