通过约束条件检查远程关联表中的值（通过连接等方式）。

Question

我希望添加一个约束，它将检查相关表中的值。

我有3个表：

CREATE TABLE somethink_usr_rel (
    user_id BIGINT NOT NULL,
    stomethink_id BIGINT NOT NULL
);

CREATE TABLE usr (
    id BIGINT NOT NULL,
    role_id BIGINT NOT NULL
);

CREATE TABLE role (
    id BIGINT NOT NULL,
    type BIGINT NOT NULL
);

(如果你想让我在FK上添加约束，请告诉我。)

我想为 somethink_usr_rel 添加一个约束，检查role中的type（“两个表格之外”），例如：

ALTER TABLE somethink_usr_rel
    ADD CONSTRAINT CH_sm_usr_type_check 
    CHECK (usr.role.type = 'SOME_ENUM');

我尝试使用JOIN来完成这个任务，但没有成功。有什么想法可以实现吗？

- lukaszrys

Answer 1

CHECK约束目前无法引用其他表。 手册:

当前，CHECK表达式不能包含子查询，也不能引用除当前行的列之外的变量。

一种方法是使用触发器，例如 @Wolph演示的。

一种没有触发器的干净解决方案：添加冗余列并在FOREIGN KEY约束条件中包括它们，这是强制执行引用完整性的首选方法。在dba.SE上的相关答案中详细说明：

• 强制“两个表之间”的约束条件

另一个选择是"伪造"一个做检查的IMMUTABLE函数，并在CHECK约束条件中使用它。Postgres将允许此操作，但请注意可能存在的注意事项。最好将其设为NOT VALID约束条件。请参阅：

• 在还原转储时禁用所有约束条件和表检查

- Erwin Brandstetter

Answer 2

如果需要进行连接操作，CHECK约束条件不是一个选项。您可以创建一个触发器来引发错误。

请参阅以下示例：http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when she must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

- Wolph

---

一个使用联接的示例会更适合这个问题。 - faintsignal

我也这么认为。OP的问题是关于检查相关表的。 - Redgren Grumbholdt

与 CONSTRAINT TRIGGER 类似。 - OJFord

Answer 3

我这样做了（nazwa=用户名，firma=公司名）：

CREATE TABLE users
(
  id bigserial  CONSTRAINT firstkey PRIMARY KEY,
  nazwa character varying(20),
  firma character varying(50)
);


CREATE TABLE test
(
  id bigserial  CONSTRAINT firstkey PRIMARY KEY,
  firma character varying(50),
  towar character varying(20),
  nazwisko character varying(20)
);

ALTER TABLE public.test ENABLE ROW LEVEL SECURITY;

CREATE OR REPLACE FUNCTION whoIAM3() RETURNS varchar(50) as $$
declare
    result varchar(50);
   BEGIN
 select into result users.firma from users where users.nazwa = current_user;
    return result;
    END;

    $$ LANGUAGE plpgsql;


CREATE POLICY user_policy ON public.test
    USING (firma = whoIAM3());

CREATE FUNCTION test_trigger_function()
RETURNS trigger AS $$
BEGIN
  NEW.firma:=whoIam3();
return NEW;
END
$$ LANGUAGE 'plpgsql'
CREATE TRIGGER test_trigger_insert BEFORE INSERT  ON test FOR EACH ROW EXECUTE PROCEDURE  test_trigger_function();

- blackmoon

Answer 4

一个表可以有多个外键约束。这用于在表之间实现多对多的关系。假设您有关于产品和订单的表，但现在您想允许一个订单包含可能多个产品（上面的结构不允许）。您可以使用以下表结构：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

- Андрій Душар