The following bug has been logged online:

Bug reference: 1162
Logged by: Fehmi Noyan ISI

Email address: fnoyan (AT) linuxmail (DOT) org

PostgreSQL version: 7.4

Operating system: FreeBSD 5.1 - x86 platform

Description: lots of values that have "unique" property

Details:

Hello
First of all I am not a database expert! I just run MySQL and PostgreSQL
on my FreeBSD 5.1 system and develop small web applications.
I am new to PostgreSQL and don't know this is a bug (or something like
it) or not. But while I was reading the constraints section of user's
manual I noticed something strange!
Please consider the procedure below...

noyan=> CREATE TABLE table_1 (
noyan(> id integer UNIQUE NOT NULL,
noyan(> dsc text NOT NULL,
noyan(> passwd text NOT NULL);

The table is created successfully.Ok, go on...

noyan=> INSERT INTO table_1 VALUES (1,'System Admin','something');
noyan=> INSERT INTO table_1 VALUES (1,'Normal User','something');
ERROR: Cannot insert a duplicate key into unique index table_1_id_key
noyan=> SELECT * FROM table_1;
id | dsc | passwd
----+--------------+-----------
1 | System Admin | something
(1 row)

I got the error message as expected. Everything is ok.Now, let's create
another table called "table_2" with an inheritence from "table_1".

noyan=> CREATE TABLE table_2 (ext_column integer) INHERITS (table_1);
CREATE TABLE
noyan=> \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+-------
public | table_1 | table | noyan
public | table_2 | table | noyan
(2 rows)

But, when I insert a new value into "table_2" with an "id" value same
as with one of the values in "table_1"...

noyan=> INSERT INTO table_2 VALUES (1,'Any User','AnyPasswd',123);
INSERT 17114 1
noyan=> SELECT * FROM table_2;
id | dsc | passwd | ext_column
----+----------+-----------+------------
1 | Any User | AnyPasswd | 123
(1 row)

noyan=> SELECT * FROM table_1;
id | dsc | passwd
----+--------------+-----------
1 | System Admin | something
1 | Any User | AnyPasswd
(2 rows)

I think, I must get an error message like the message above (when I try
to insert a new value into "table_1" directly with a same "id" value).
There are two same "id"s with the value "1" although the "id" column is
defined as UNIQUE.
If I use "PRIMARY KEY" instead of "UNIQUE NOT NULL", the same thing
happens.Nothing changes!

System Information :
PostgreSQL 7.3.4 on i386-unknown-freebsd5.1, compiled by GCC gcc (GCC)
3.2.2 [FreeBSD] 20030205 (release)

As I sad at the begining, I am not an SQL expert so please let me know
this is a strange behaviour or not!
And finally, thanks to all of PostgreSQL team for giving us this
excellent and powerfull free database software.


Thanks....
Fehmi Noyan ISI




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

On Sun, 13 Jun 2004, PostgreSQL Bugs List wrote:

Primary key, unique and foreign key constraints are not currently
inherited by child tables. This is a known deficiency with the
inheritance system, but AFAIK isn't being actively worked on by anyone at
this time.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html