Re: Unique values on multiple tables
От | Sterpu Victor |
---|---|
Тема | Re: Unique values on multiple tables |
Дата | |
Msg-id | em2f4247f4-93c3-472f-a176-c77eb34d8271@victor-pc обсуждение исходный текст |
Ответ на | Re: Unique values on multiple tables (Emre Hasegeli <emre@hasegeli.com>) |
Ответы |
Re: Unique values on multiple tables
|
Список | pgsql-general |
------ Original Message ------ From: "Emre Hasegeli" <emre@hasegeli.com> To: "Sterpu Victor" <victor@caido.ro> Cc: "PostgreSQL General" <pgsql-general@postgresql.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?
В списке pgsql-general по дате отправления: