[SQL] foreign key problems

[BARTKO, Zoltán]

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.sk 

create table AAssociation (
createdBy   integer,--* the 
creator of the association
creationtimestamp 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
isActiveboolean default true--* is the 
entity active?
);

create table SysObjTypes (
objectType  integer unique not null primary key,--* object type
aname   text,   --* name
isAttachmentboolean,--* 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#
revisioninteger 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
);

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

Re: [SQL] foreign key problems

[Stephan Szabo]

On Tue, 4 Jan 2005, [iso-8859-2] BARTKO, Zoltán wrote:

> 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).

It looks like the set of table definitions you gave in the attached file
do not actually work as given, and there's no example data. Running on
8.0 beta3 I was able to insert data that allowed me to place a row into
AAttachment (and in fact I actually can insert some data that probably
should not be allowed). I'm not sure if that's based on my basically null
definition of DRecord or not however.

In general, however, foreign keys and inheritance don't mix (neither do
primary keys or unique constraints). In general, they'll only currently
work for the table they're in specifically and only for the table
mentioned specifically. So, for example, the references constraints in
AObjAssociation are not inherited by AAttachment. Inheritance really needs
someone to champion getting all of these deficiencies fixed.

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

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

[SQL] Calling a table in another database from plpgsql

[deepthi]

Hi,

I have function called test1() in database 1. I need to input the value from
this function into another table which is in database2.

How can i achieve this task in plpgsql. I went through some documentation and
found out that it is possible through dblink.

I infact downloaded the dblink package in contrib folder. But still when i tried
to use dblink in the following manner:

SELECT * from dblink('host=192.168.0.50 dbname=AK1 user=akteam','Select userid
from user_reg') as t (userid integer);

I am getting an error as "ERROR:  function dblink("unknown", "unknown") does not
exist"

Is dblink the only way of connecting the databases and if so what is the exact
step by step procedure to be follwed to download dblink and use it to connect
databases.

Thanks in advance,
Regards,
deepthi








---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] Calling a table residing in another database from Plpgsql

[deepthi]

Hi,

I have function called test1() in database 1. I need to input the value
from this function into another table which is in database2.

How can i achieve this task in plpgsql. I went through some documentation
and found out that it is possible through dblink.

I infact downloaded the dblink package in contrib folder. But still when i
tried to use dblink in the following manner:

SELECT * from dblink('host=192.168.0.50 dbname=AK1 user=akteam','Select
userid from user_reg') as t (userid integer);

I am getting an error as "ERROR:  function dblink("unknown", "unknown")
does not exist"

Is dblink the only way of connecting the databases and if so what is the
exact step by step procedure to be follwed to download dblink and use it
to connect databases.

Thanks in advance,
Regards,
deepthi







---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Calling a table residing in another database from Plpgsql

[Kenneth Gonsalves]

On Wednesday 05 January 2005 10:43 am, [EMAIL PROTECTED] wrote:
> Hi,
>
> I have function called test1() in database 1. I need to input the value
> from this function into another table which is in database2.

shouldnt you be using schemas instead?

kg

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match