------ Original Message ------
From: "Emre Hasegeli" <emre@xxxxxxxxxxxx>
To: "Sterpu Victor" <victor@xxxxxxxx>
Cc: "PostgreSQL General" <pgsql-general@xxxxxxxxxxxxxx>
Sent: 28/3/2016 12:06:23 PM
Subject: Re: 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?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
