postgre如何在SQL中组合约束CHECK

Question

有一个下表：

CREATE TABLE LectureHalls
(
id SERIAL PRIMARY KEY,
floor INTEGER CHECK (floor <= 4),
classroom INTEGER NOT NULL,
CONSTRAINT sc_unique_lectureHall UNIQUE (id, floor, classroom),
CONSTRAINT sc_unique_lectureHall2 UNIQUE (floor, classroom);
);

是否可以在sql中创建这样的约束，以便当floor＝1教室>0和教室<=30？

Answer 1

这就是您想要的吗？

create table lecturehalls (
id serial primary key,
floor integer check (floor <= 4),
classroom integer not null,
unique (floor, classroom),
check(floor is distinct from 1 or (classroom > 0 and classroom <= 30))
);

注意，我删除了(id, floor, classroom)上的唯一约束：id是表的主键，所以这无论如何都是一个无操作。

Answer 2

您可以：

CREATE TABLE LectureHalls
(
id SERIAL PRIMARY KEY,
floor INTEGER NOT NULL CHECK (floor BETWEEN 1 AND 4),
classroom INTEGER NOT NULL,
UNIQUE KEY (floor, classroom),
CHECK (floor = 1 AND classroom BETWEEN 1 AND 30) 
);

这种形式允许扩展到更多楼层：

CREATE TABLE LectureHalls
(
id SERIAL PRIMARY KEY,
floor INTEGER NOT NULL CHECK (floor BETWEEN 1 AND 4),
classroom INTEGER NOT NULL,
UNIQUE KEY (floor, classroom),
CHECK (floor = 1 AND classroom BETWEEN 1 AND 30 OR
floor = 2 AND classroom BETWEEN 1 AND 15 OR
floor = 3 AND classroom BETWEEN 1 and 24) 
);