Re: refential integrity to multiple tables ??

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(at)kregert(dot)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(at)postgresql(dot)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