From: BARTKO, Zoltán <bartko.zoltan@pobox.sk>
To: pgsql-sql@postgresql.org
Subject: foreign key problems
Date: Tue, 4 Jan 2005 13:15:40 +0100
Message-ID: <200501041215.j04CFe7s017600@www7.pobox.sk> (raw)
Dear all,
I have a problem:
if I create the tables that are in the attached file, I can't insert
rows into the AAttachment table, even though the rows in DObject with
the given primary key exist (PgSQL 8.0 rc1 complains about
(ownerid)=(insert the number here) not available in DObject. The same
happens with attribute bodyid).
I have noticed that this behavior is different, if I do not use
inherited tables, e.g. if I use
create table A (
a integer references B(b),
b integer references B(b));
create table B
(b serial primary key);
insert into B values (1);
insert into B values (2);
insert into A (a,b) values (1,2);
works flawlessly.
Is this a feature or a bug or is the problem in front of the computer?
thanks
Zoltan
____________________________________
RAMMSTEIN, 22.02.2005 o 20,00, Bratislava Incheba,
Info: 0904 666 363, http://www.xl.skAttachments:tables.txt (text/plain, 2.3K) download
create table AAssociation (
createdBy integer, --* the creator of the association
creation timestamp with time zone default 'now' --* creation timestamp with time zone
);
create table AObjAssociation (
privilege integer not null references SysPrivilege(privilege),
--* the privilege used to create the association
insteadOf integer references DPerson(person)
--* instead of whom is the action taken
) inherits (AAssociation);
create table AAttachment (
ownerID bigint references DObject(object), --* owner object
bodyID bigint references DObject(object) --* attached object
) inherits (AObjAssociation);
create table DPerson (
person serial primary key, --* person row id#
login varchar(16) not null unique, --* login name
passwd varchar(16) not null, --* password
aname varchar not null, --* name
surname varchar not null, --* surname
isAdmin boolean default false, --* is the person an admin?
unique (aname, surname)
) inherits (DOrgEntity);
create table DOrgEntity (
who integer not null, --* record author
createdAt timestamp with time zone default 'now', --* creation timestamp with time zone
isActive boolean default true --* is the entity active?
);
create table SysObjTypes (
objectType integer unique not null primary key, --* object type
aname text, --* name
isAttachment boolean, --* is attachment of other objects
hasAttachment boolean --* has attachments
);
create table SysStatus (
status integer unique not null primary key, --* id#
addAttachment boolean, --* allow adding new attachments
dropAttachment boolean, --* allow dropping existing attachments
aname text not null, --* status name
description text --* status description
);
create table DObject (
object bigserial primary key, --* id#
revision integer not null default 0, --* ordinal number of the commited change
aname text not null, --* object name
description text default '', --* object description
objectType integer not null references SysObjTypes(objectType),
--* the object type
status integer not null references SysStatus(status),
--* the object status
unique (aname, description, objectType)
) inherits (DRecord);
create table SysPrivilege (
privilege integer unique not null primary key, --* id#
aname text not null, --* privilege name
description text --* privilege description
);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bartko.zoltan@pobox.sk
Subject: Re: foreign key problems
In-Reply-To: <200501041215.j04CFe7s017600@www7.pobox.sk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox