SEO

#1 (**permalink**) 04-09-2008, 01:44 PM

Here's something I've just noticed:

CREATE TABLE foo (f INTEGER PRIMARY KEY);
INSERT INTO foo VALUES (1);
CREATE TABLE bar (b INTEGER REFERENCES foo);
CREATE TABLE bar1 () INHERITS (bar);
INSERT INTO bar1 VALUES (1);

This is quite correct:

TRUNCATE foo;
ERROR: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "bar" references "foo".
HINT: Truncate table "bar" at the same time, or use TRUNCATE ... CASCADE.

But:

TRUNCATE foo, bar;
SELECT * FROM bar;
b
---
1
(1 row)

SELECT * FROM foo;
f
---
(0 rows)

Whoops. The referential constraint has been violated. Perhaps it's a
good idea to extend TRUNCATE on a parent table to all children?

--
Florian Weimer <fweimer@bfk.de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

#2 (**permalink**) 04-09-2008, 01:44 PM

Florian Weimer wrote:
> Here's something I've just noticed:
>
> CREATE TABLE foo (f INTEGER PRIMARY KEY);
> INSERT INTO foo VALUES (1);
> CREATE TABLE bar (b INTEGER REFERENCES foo);
> CREATE TABLE bar1 () INHERITS (bar);
> INSERT INTO bar1 VALUES (1);
>
> This is quite correct:

No, it isn't; try leaving the first INSERT out:

alvherre=# CREATE TABLE foo (f INTEGER PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
alvherre=# CREATE TABLE bar (b INTEGER REFERENCES foo);
CREATE TABLE
alvherre=# CREATE TABLE bar1 () INHERITS (bar);
CREATE TABLE
alvherre=# INSERT INTO bar1 VALUES (1);
INSERT 0 1
alvherre=# select * from bar;
b
---
1
(1 fila)

alvherre=# select * from foo;
f
---
(0 filas)

There is a bug here, but it's not in TRUNCATE. FKs don't work with
inheritance.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Foreign keys, table inheritance, and TRUNCATE