The following bug has been logged online:
Bug reference: 1162
Logged by: Fehmi Noyan ISI
Email address: fnoyan@linuxmail.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
