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(at)mascari(dot)com