How to combine constraint CHECK in SQL?

Question

There is a following table:

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);
);

Is it possible to create such a constraint in sql so that when floor = 1 classroom> 0 and classroom <= 30?

Answer 1

Is this what you want?

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))
);

Note that I removed the unique constraint on (id, floor, classroom): id is the primary key of the table, so this is a no-op anyway.