我正在为postgres中的下一个用例寻找解决方案:
我有一个表(任务)可以属于一个用户(另一个表)或一个公司(另一个表),它应该属于两个表之一,这意味着我应该有可空的外键,但是我应该在插入时检查是否可以填充,然后仅填充其中之一。我该怎么做?
我正在为postgres中的下一个用例寻找解决方案:
我有一个表(任务)可以属于一个用户(另一个表)或一个公司(另一个表),它应该属于两个表之一,这意味着我应该有可空的外键,但是我应该在插入时检查是否可以填充,然后仅填充其中之一。我该怎么做?
添加带有相应的user_id
的可空列(让我们说company_id
和FOREIGN KEY
)是正确的。
您可以像这样添加检查约束(要在CREATE TABLE
语句中执行此操作,可以将其像“列”一样列出并从CONSTRAINT
开始):
ALTER TABLE tasks ADD CONSTRAINT tasks_fk_check
CHECK (
(user_id IS NOT NULL AND company_id IS NULL)
OR
(user_id IS NULL AND company_id IS NOT NULL)
)
;
还有其他一些可能性,例如(user_id IS NULL) <> (company_id IS NULL)
来表达XOR
属性。您也可以选择它们作为条件。
有关更多信息,请查看文档:
您可以创建一个对VARIADAC参数列表中的空值数量进行计数的函数。然后创建访问该函数的检查约束。
如果这是一个孤立的用例,则不一定是最好的方法,但是它是一种通用方法,要求从一组列中获取一定数量的null。
--setup create test table
create table task( id serial,user_id integer,company_id integer);
-- create function to count number of nulls in VARIADIC parameter
create or replace function num_of_nulls(VARIADIC integer[])
returns bigint
language sql immutable
as $$
with each_item as (select unnest($1) itm)
select sum(case when itm is null then 1 else 0 end) from each_item;
$$;
-- add check constraint
alter table task add constraint one_and_only_one_must_be_null check (num_of_nulls(user_id,company_id) = 1);
-- test
-- valid
insert into task(user_id,company_id)
values (1,null),(null,1);
select * from task;
-- invalid (must be run separately)
insert into task(user_id,company_id)
values (null,null);
insert into task(user_id,company_id)
values (2,2);