inserting lots of values into a column that has "unique" property

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 directly into "table_1" 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

--
______________________________________________
Check out the latest SMS services @ http://www.linuxmail.org
This allows you to send and receive SMS through your mailbox.

Powered by Outblaze