Re: Unique values on multiple tables

------ Original Message ------
From: "Emre Hasegeli" <emre(at)hasegeli(dot)com>
To: "Sterpu Victor" <victor(at)caido(dot)ro>
Cc: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Sent: 28/3/2016 12:06:23 PM
Subject: Re: [GENERAL] Unique values on multiple tables

>> I have 2 tables and I must make asure unique values like this.
>>
>> table1
>> id
>> nr - integer
>>
>> table2
>> id
>> id_table1 - FK in Table 1
>> valid_from - timestamp
>>
>> There must be unique values for:
>> - nr - from table1
>> and
>> - YEAR(MIN(valid_from)) from table 2
>
>In situations like this, I add the required column to the other table
>with a foreign key. Assuming that (id) is the primary key of table1,
>you would need another unique key on (nr, id). Than you can add nr
>column to table2 by changing the foreign key to (nr, id_table1)
>references table1 (nr, id).
>
>Obviously, its not an efficient solution. It requires an additional
>unique key and more storage on the referencing table. Though, I
>believe it is a safe one. It doesn't allow the duplicated column to
>be inconsistent. There are many things that can go wrong under
>concurrency with a trigger like you posted.

This wouldn't work in my case because I need unique values only for the
smallest valid_from.
Example:
- table1 has row id=1, nr=100
- table2 has row1 id=5, id_table1=1, valid_from=2015-12-01
row2 id=6, id_table1=1, valid_from=2016-01-01
Then unique values must be assured only for (100, 2015-12-01), ignorig
the second valid_from(2016-01-01)

I changed the execution time of the function from BEFORE to AFTER and I
hope this will solve the problem.
I don't know how postgres works behind this code but I hope that this
will solve the problem.
Could I use lock tables to fix this? Is postgres automaticaly locking a
table while running a trigger on that table?