Thread: 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

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

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

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:

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