Re: CREATE TABLE with REFERENCE

Jonathan Bartlett wrote:

>That's actually what I was saying. Within a single transaction rather
>than across multiple transactions. If you have to go across multiple
>transactions, there's no real point in having integrity constraints.
>
But why would you have to go accross multiple transactions, when you are
inserting entries into the tables, that are related, and reference each
other?
If one entry doesn't make any sense without the other one, and you
insert it outside a transaction, and then try to insert the other one,
and it fails for any reason, that will leave your database in an
inconsistent state with all those orphaned entries sitting around.
That's exactly what the transactions are invented for - so that you
don't need to worry about the consistency of your data in case of a failure.

>
>
>
>>>Most of my databases don't even user "references", just because I like the
>>>flexibility, and I have multitable keys (keys that can refer to rows from
>>>multiple tables).
>>>
>>>
>>>
>>>
>>Not much to brag about :-)
>>
>>
>
>Do you know of a better way to handle multitable references?
>
>
Sure.
SET CONSTRAINTS DEFERRED;
BEGIN;
insert this
insert that
END;

Dima

>Jon
>
>
>
>>Dima
>>
>>
>>
>>---------------------------(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
>>
>>
>>