postgresql - 两列 A 和 B，表 Y (A, B) 参考表 X (A, B)。当 Y 中的 A 发生变化时，如何使 Y 中的 B 更改为引用的表 X 值？

Question

我正在开发一个 WPF 程序。在这个程序中，我有两个DataGrid，一个是Quotation List，另一个是一个Quotation Status List。Quotation List' 数据和Quotation Status List' 数据都存储在PostgreSQL数据库的表中（quotation_list & quotation_status_list）。

在quote_list表中，将有两列名为status和status_color。status用于呈现报价状态，用于通过Control中的Binding方法控制status_color行的Foreground。WPF TextBox

<DataGridTemplateColumn SortMemberPath="status" CanUserSort="True" Header="Status" Width="100" >
    <DataGridTemplateColumn.CellTemplate>
        <DataTemplate>
            <TextBox Name="Box_status" Foreground="{Binding status_color}" Text="{Binding status}" IsReadOnly="True" />
        </DataTemplate>
    </DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>

<DataGridTemplateColumn SortMemberPath="status_color" CanUserSort="True" Header="Color" Width="100" >
    <DataGridTemplateColumn.CellTemplate>
        <DataTemplate>
            <TextBox Name="Box_status_color" Foreground="{Binding status_color}" Text="{Binding status_color}" IsReadOnly="True" />
        </DataTemplate>
    </DataGridTemplateColumn.CellTemplate>
</DataGridTemplateColumn>

我想要实现的是：

1. 当用户编辑quotation_status_list表时，quotation_list 中的所有status_color内容都将被更改。status_colorstatus_color

报价状态列表

 id |  status  | status_color 
----+----------+--------------
  1 |  Active  |     Red      
  2 |  Firm    |     Blue       

报价单

 id |  status  | status_color |  title 
----+----------+--------------+--------
  1 |  Active  |     Red      |   abc       
  2 |  Firm    |     Blue     |   def    
  3 |  Active  |     Red      |   hij    

status_color 将Red修改为Black后，status_colorin 的值quotation_list会自动改变。

报价状态列表

 id |  status  | status_color 
----+----------+--------------
  1 |  Active  |     Black    
  2 |  Firm    |     Blue       

报价单

 id |  status  | status_color |  title 
----+----------+--------------+--------
  1 |  Active  |     Black    |   abc       
  2 |  Firm    |     Blue     |   def   
  3 |  Active  |     Black    |   hij   

2. 当用户编辑quotation_liststatus表时，其值将更改为quotation_status_list表。status_colorstatus_color

报价状态列表

 id |  status  | status_color 
----+----------+--------------
  1 |  Active  |     Red      
  2 |  Firm    |     Blue       

报价单

 id |  status  | status_color |  title 
----+----------+--------------+--------
  1 |  Active  |     Red      |   abc       
  2 |  Firm    |     Blue     |   def   
  3 |  Active  |     Red      |   hij   

修改后，status第 1 行的 变为Firm，status_color会自动变为蓝色。

报价状态列表

 id |  status  | status_color 
----+----------+--------------
  1 |  Active  |     Red      
  2 |  Firm    |     Blue       

报价单

 id |  status  | status_color |  title 
----+----------+--------------+--------
  1 |  Firm    |     Blue     |   abc       
  2 |  Firm    |     Blue     |   def    
  3 |  Active  |     Red      |   hij  

为了实现这两个功能，我尝试通过创建两个表

报价状态列表

CREATE TABLE IF NOT EXISTS quotation_status_list
( 
    id SERIAL UNIQUE NOT NULL, 
    status TEXT UNIQUE NOT NULL, 
    status_color TEXT UNIQUE NOT NULL, 
    PRIMARY KEY(id, status, status_color)
);

报价单

CREATE TABLE IF NOT EXISTS quotation_list
( 
    id SERIAL UNIQUE NOT NULL, 
    title TEXT NOT NULL, 
    status TEXT NOT NULL, 
    status_color TEXT NOT NULL, 
    FOREIGN KEY(status, status_color)  
    REFERENCES quotation_status_list(status, status_color)  
    ON DELETE NO ACTION  
    ON UPDATE CASCADE
) ;

我可以创建quote_status_list表，但无法创建quote_list。错误是

there is no unique constraint matching given keys for referenced table "quotation_status_list"

会有什么问题，quote_status_list 中的status,​​ 应该status_color是UNIQUE并且它们是PRIMARY KEY。我使用了错误的方法吗？或者数据库不能做这个工作？

欢迎任何建议。

标签： postgresql

Answer 1

不需要quote_status_list中的3列PK，单列就足够了。然后不要重复status和status_colorquotation_list。删除它们并添加包含quotation_status_list PK的列。

create table quotation_status_list(
       id           integer generated always as identity  primary key
     , status       text unique not null 
     , status_color text unique not null 
     );


create table if not exists quotation_list(     
       id                  integer generated always as identity 
     , title               text not null  
     , quotation_status_id integer
                           references quotation_status_list(id)
                           on delete no action  
     ) ;

现在检索状态和 status_color 加入表：

select ql.title
     , qs.status
     , gs.status_color 
  from quotation_list        ql  
  join quotation_status_list qs 
    on (qs.id  = ql.quotation_status_id);

现在quote_status_list 中的任何更改（PK 除外，它永远不应该更改）都会自动反映在选择中。

注意：我使用较新的首选generated as identity键。如果您有不支持（v10 之前？）的旧 Postgres 版本，则串行完成相同的操作。