前端之家

  1. 首页
  2. 问答

根据多个属性定义SQL约束

• 问答

我有这个定义的类型和表：

CREATE TYPE emp_role AS ENUM ('Manager','Developer','accountant','Secretary');
CREATE TABLE employees (
employee_id int NOT NULL UNIQUE,lastname text NOT NULL,firstname text NOT NULL,address text NOT NULL,hire_date date NOT NULL,salary numeric NOT NULL CHECK(salary > 1.500),emp_role emp_role,department_id int NOT NULL
);

我想在此表上创建一个约束而不更改其定义，该约束同时取决于属性hire_date，salary和emp_role，例如，这意味着在emp_role之后雇用的Manager为2019-21-11的员工不能有salary比15.000大，因此这样的查询应返回错误：

INSERT INTO employees VALUES(2,'foo','bar','foostreet','2019-12-20',18.0000,'Manager',3);

我不熟悉该怎么做

geaixin520 回答：根据多个属性定义SQL约束

您可以创建多列检查约束，如下所示：

CREATE TYPE emp_role AS ENUM ('Manager','Developer','Accountant','Secretary');

CREATE TABLE employees (
    employee_id int NOT NULL UNIQUE,lastname text NOT NULL,firstname text NOT NULL,address text NOT NULL,hire_date date NOT NULL,salary numeric NOT NULL CHECK(salary > 1500),emp_role emp_role,department_id int NOT NULL
    CONSTRAINT CK_employee CHECK (
        NOT(
            emp_role = 'Manager' 
            AND hire_date > DATE'2019-11-21' 
            AND salary > 15000
        )
    )
);

Demo on DB Fiddle 

INSERT INTO employees VALUES(2,'foo','bar','foostreet','2019-12-20',180000,'Manager',3);

ERROR:  new row for relation "employees" violates check constraint "ck_employee"
DETAIL:  Failing row contains (2,foo,bar,foostreet,2019-12-20,Manager,3).


INSERT INTO employees VALUES(2,1,3);

ERROR:  new row for relation "employees" violates check constraint "employees_salary_check"
DETAIL:  Failing row contains (2,Developer,3).
constraintsddlpostgresqlsql
本文链接：https://www.f2er.com/3059422.html

大家都在问