refential integrity to multiple tables ??

Almost everything is possible if you accept to write your own triggers
and trigger functions. But there is no standard solution to this
problem.

There are of course inherited tables, but in the current
implementation, foreign key constraints doesn't work very well with
them (making them completly worthless IMO). That is going to change in
a future release according to the docs, but for now, table inheritance
is not the way to go in your case. Triggers are.

Regards
Erik

let's say i have a tansaction table called TRANSACTION
(transaction_id,amount,type,type_id)

Let's say a transaction can have multiple types: TYPE1, TYPE2 for
example.

EACH type has his own definition and his own table.

Every transaction has a type that could be type1 or type2 that's why
if the type is TYPE1 i want to make a referential integrity to the
TYPE1_TABLE and if the type is TYPE2 i want to make a referential
integrity to the TYPE2_TABLE.

IS IT POSSIBLE TO DO THAT???

I made a turn around to this problem by creating two tables:
- table TYPE1_TRANSACTION (type1_id,transaction_id)
- table TYPE2_TRANSACTION (type2_id,transaction_id)

But this does not seem so right for me ??

thx for any help

________________________________________________________________________
Want to chat instantly with your online friends? Get the FREE Yahoo!
Messenger http://mail.messenger.yahoo.co.uk

On Wednesday 08 October 2003 06:53, Nagib Abi Fadel wrote:

HI,

let's say i have a tansaction table called TRANSACTION
(transaction_id,amount,type,type_id)

Let's say a transaction can have multiple types: TYPE1, TYPE2 for example.

EACH type has his own definition and his own table.

Every transaction has a type that could be type1 or type2 that's why if the
type is TYPE1 i want to make a referential integrity to the TYPE1_TABLE and
if the type is TYPE2 i want to make a referential integrity to the
TYPE2_TABLE.

IS IT POSSIBLE TO DO THAT???

You're looking at it the wrong way around, but in any case there are still
problems.

transaction_core(trans_id, trans_name, trans_type)
transaction_type1(tt1_core_id, tt1_extra1, tt1_extra2...)
transaction_type2(tt2_core_id, tt2_extra1, tt2_extra2...)

And have tt1_core reference trans_id (not the other way around). Do the same
for tt2_core and we can guarantee that the two transaction types refer to a
valid trans_id in transaction_core.

Now, what gets trickier is to specify that tt1_core should refer to a row in
transaction_core where trans_type=1.
Ideally, we could have a foreign-key to a view, or specify a constant in the
FK definition. We can't so you have to repeat the type field in
transaction_type1/2 and keep it fixed for every row.

HTH
--
Richard Huxton
Archonet Ltd

What u suggest here is having a null entry in table TABLE_TYPE1 and TABLE_TYPE2.
(This does not seem to be right.)
That way i would be able to make a referential integrity to each table by creating the table transaction like follows:

CREATE TABLE transaction (
transaction_id,
amount,
type1_id default null REFERENCES TABLE_TYPE1 (type1_id) on delete cascade,
type2_id default null REFERENCES TABLE_TYPE2 (type2_id) on delete cascade,
CONSTRAINT (type1_id IS NULL OR type2_id IS NULL)
)

If someone deletes the null row in either table (TABLE_TYPE1 or TABLE_TYPE2) this would be a disaster. (Someone who replaced me in my post for instance)

But in other hand i will make a join between two tables instead of three if i want to retrieve some informations for a specific type.

Or i could create the table without referential integrity ???

The decision is confusing a little bit ...

What should i choose ??

Thx for your help.

Mattias Kregert <mattias@kregert.se> wrote:
Maybe you should skip the "type" field and instead have id columns for each of the types and then on insert set the id for only one of the types. You could also make a constraint to make sure only one of the type id's can be specified:

CREATE TABLE transaction (
transaction_id,
amount,
type1_id default null,
type2_id default null,
CONSTRAINT (type1_id IS NULL OR type2_id IS NULL)
)

I have done something like this, myself...

/Mattias

----- Original Message -----
From: Nagib Abi Fadel
To: pgsql-general@postgresql.org
Sent: Wednesday, October 08, 2003 7:53 AM
Subject: [GENERAL] refential integrity to multiple tables ??

HI,

let's say i have a tansaction table called TRANSACTION (transaction_id,amount,type,type_id)

Let's say a transaction can have multiple types: TYPE1, TYPE2 for example.

EACH type has his own definition and his own table.

Every transaction has a type that could be type1 or type2 that's why if the type is TYPE1 i want to make a referential integrity to the TYPE1_TABLE and if the type is TYPE2 i want to make a referential integrity to the TYPE2_TABLE.

IS IT POSSIBLE TO DO THAT???

I made a turn around to this problem by creating two tables:
- table TYPE1_TRANSACTION (type1_id,transaction_id)
- table TYPE2_TRANSACTION (type2_id,transaction_id)

But this does not seem so right for me ??

thx for any help

---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

--- Richard Huxton <dev@archonet.com> wrote:

On Wednesday 08 October 2003 06:53, Nagib Abi Fadel
wrote:

HI,

let's say i have a tansaction table called

TRANSACTION

(transaction_id,amount,type,type_id)

Let's say a transaction can have multiple types:

TYPE1, TYPE2 for example.

EACH type has his own definition and his own

table.

Every transaction has a type that could be type1

or type2 that's why if the

type is TYPE1 i want to make a referential

integrity to the TYPE1_TABLE and

if the type is TYPE2 i want to make a referential

integrity to the

TYPE2_TABLE.

IS IT POSSIBLE TO DO THAT???

You're looking at it the wrong way around, but in
any case there are still
problems.

transaction_core(trans_id, trans_name, trans_type)
transaction_type1(tt1_core_id, tt1_extra1,
tt1_extra2...)
transaction_type2(tt2_core_id, tt2_extra1,
tt2_extra2...)

And have tt1_core reference trans_id (not the other
way around). Do the same
for tt2_core and we can guarantee that the two
transaction types refer to a
valid trans_id in transaction_core.

Now, what gets trickier is to specify that tt1_core
should refer to a row in
transaction_core where trans_type=1.
Ideally, we could have a foreign-key to a view, or
specify a constant in the
FK definition. We can't so you have to repeat the
type field in
transaction_type1/2 and keep it fixed for every row.

HTH
--
Richard Huxton
Archonet Ltd

Actually a type1_id can have mutiple corresponding
transaction_ids (same thing for type2) that's why i
created the tables as follows:

create table transaction(
transaction_id serial P K,
amount int,...)

create table TABLE_TYPE1(
type1_id serial P K,
...
)

create table transaction_type1(
type1_id int,
transaction_id int
)

for example we can have the following possible entries
in table transaction_type1:
type1_id,transaction_id
100,101
100,102
100,103
200,312
200,313
200,314
200,315

Same thing for type 2.

I can also add that a transaction id can be of type1
or (exclusive) of type2 and never of two types at the
same time.

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

No, you don't need a NULL entry in "table_type1" or "table_type2".

When inserting NULL into a column which REFERENCES another table, there is simply no referencing done.

/Mattias

Nagib Abi Fadel wrote:
What u suggest here is having a null entry in table TABLE_TYPE1 and TABLE_TYPE2.
(This does not seem to be right.)
That way i would be able to make a referential integrity to each table by creating the table transaction like follows:

CREATE TABLE transaction (
transaction_id,
amount,
type1_id default null REFERENCES TABLE_TYPE1 (type1_id) on delete cascade,
type2_id default null REFERENCES TABLE_TYPE2 (type2_id) on delete cascade,
CONSTRAINT (type1_id IS NULL OR type2_id IS NULL)
)

If someone deletes the null row in either table (TABLE_TYPE1 or TABLE_TYPE2) this would be a disaster. (Someone who replaced me in my post for instance)

But in other hand i will make a join between two tables instead of three if i want to retrieve some informations for a specific type.

Or i could create the table without referential integrity ???

The decision is confusing a little bit ...

What should i choose ??

Thx for your help.

Mattias Kregert <mattias@kregert.se> wrote:
Maybe you should skip the "type" field and instead have id columns for each of the types and then on insert set the id for only one of the types. You could also make a constraint to make sure only one of the type id's can be specified:

CREATE TABLE transaction (
transaction_id,
amount,
type1_id default null,
type2_id default null,
CONSTRAINT (type1_id IS NULL OR type2_id IS NULL)
)

I have done something like this, myself...

/Mattias

----- Original Message -----
From: Nagib Abi Fadel
To: pgsql-general@postgresql.org
Sent: Wednesday, October 08, 2003 7:53 AM
Subject: [GENERAL] refential integrity to multiple tables ??

HI,

let's say i have a tansaction table called TRANSACTION (transaction_id,amount,type,type_id)

Let's say a transaction can have multiple types: TYPE1, TYPE2 for example.

EACH type has his own definition and his own table.

Every transaction has a type that could be type1 or type2 that's why if the type is TYPE1 i want to make a referential integrity to the TYPE1_TABLE and if the type is TYPE2 i want to make a referential integrity to the TYPE2_TABLE.

IS IT POSSIBLE TO DO THAT???

I made a turn around to this problem by creating two tables:
- table TYPE1_TRANSACTION (type1_id,transaction_id)
- table TYPE2_TRANSACTION (type2_id,transaction_id)

But this does not seem so right for me ??

thx for any help

--------------------------------------------------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search

------------------------------------------------------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search