Сначала вы должны подтянуть свою модель данных. Все, что должно присутствовать, должно быть помечено NOT NULL, например, название здания. Мне нравится следить за тем, чтобы в обязательных текстовых полях были фактические значения, поэтому я устанавливаю ограничение проверки, которое соответствует хотя бы одному символу «слово» (буквенно-цифровому).
Вы никогда не должны разрешать отрицательные номера комнат, верно? Кроме того, вам следует избегать использования информации из частной базы данных, такой как номер комнаты или номер здания, в качестве первичного ключа. Его следует рассматривать только как ключ-кандидат. На мой взгляд, лучшей практикой было бы использовать UUID для первичных ключей., но некоторым людям нравятся автоматически увеличивающиеся целые числа, поэтому я не буду здесь настаивать. Дело в том, что комнаты, как правило, (например) отделяют их гипсокартоном или снимают, чтобы увеличить пространство. Переключение идентификаторов первичного ключа может привести к неожиданным результатам. Лучше отделить то, как он идентифицируется в базе данных, от самих данных, чтобы вы могли добавить «Комната 6А».
Также следует с уверенностью сказать, что у вас не будет более 32 767 комнат в здании, поэтому здесь подойдет int2 (16-битное, 2-байтовое целое).
CREATE TABLE building (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name varchar(15) NOT NULL UNIQUE CHECK (name ~ '\w'),
rooms_num int2 NOT NULL CHECK (rooms_num >= 0)
);
CREATE TABLE room (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
-- Should there be a room name too?
room_id int2 NOT NULL CHECK (room_id > 0),
building_id integer NOT NULL REFERENCES building (id),
UNIQUE (room_id, building_id)
);
Хорошо, теперь у вас есть более прочная основа для работы. Сделаем триггер.
CREATE FUNCTION room_in_building ()
RETURNS TRIGGER LANGUAGE plpgsql STRICT STABLE AS $$
BEGIN
IF (
-- We can safely just check upper bounds because the check constraint on
-- the table prevents zero or negative values.
SELECT b.rooms_num >= NEW.room_id
FROM building AS b
WHERE b.id = NEW.building_id
) THEN
RETURN NEW; -- Everything looks good
ELSE
RAISE EXCEPTION 'Room number (%) out of bounds', NEW.id;
END IF;
END;
$$;
CREATE TRIGGER valid_room_number
BEFORE INSERT OR UPDATE -- Updates shouldn't break the data model either
ON room
FOR EACH ROW EXECUTE PROCEDURE room_in_building();
Вы также должны добавить триггер обновления для таблицы построения. Если кто-то обновит столбец rooms_num до меньшего числа, вы можете получить несогласованную модель данных.
23.12.2019
