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

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

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

On Mon, Jun 14, 2004 at 01:07:09 +0800,
Fehmi Noyan ISI <fnoyan (AT) linuxmail (DOT) org> wrote:
This is a deficiency in how inherited tables work. This will not be fixed
in the upcoming 7.5 release. If you need this, you may be better off
using views (of joined tables) rather than inherited tables.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org