"Procedure" or deferrable check constraint?
От | Ian Pilcher |
---|---|
Тема | "Procedure" or deferrable check constraint? |
Дата | |
Msg-id | 40A67434.9050202@comcast.net обсуждение исходный текст |
Список | pgsql-novice |
TIA to anyone who reads all the way through this. I'm continuing to work on the database design for my super-, duper-, uber-web jukebox. One of my goals is to have a very flexible data model that allows me to capture the constantly changing lineups of pop bands, jazz ensembles, etc. With this in mind, I've come up with the following: -- -- -- Basic information about individual persons -- -- CREATE SEQUENCE person_name_id_seq START 1; CREATE TABLE person_names ( id INTEGER DEFAULT nextval('person_name_id_seq') PRIMARY KEY, last_name TEXT NOT NULL, first_name TEXT DEFAULT '' NOT NULL, middle_name TEXT DEFAULT '' NOT NULL, CHECK ((id = 0) = (last_name = '')) ); -- "empty" name for "empty" person INSERT INTO person_names (id, last_name) VALUES (0, ''); CREATE SEQUENCE person_id_seq START 1; CREATE TABLE persons ( id INTEGER DEFAULT nextval('person_id_seq') PRIMARY KEY, given_name INTEGER CHECK (given_name != 0) REFERENCES person_names, legal_name INTEGER CHECK (legal_name != 0) REFERENCES person_names, primary_name INTEGER NOT NULL REFERENCES person_names, CHECK ((id = 0) = (primary_name = 0)) ); -- "empty" person for "empty" stage name INSERT INTO persons (id, primary_name) VALUES (0, 0); CREATE SEQUENCE stage_name_id_seq START 1; CREATE TABLE stage_names ( id INTEGER DEFAULT nextval('stage_name_id_seq') PRIMARY KEY, person INTEGER NOT NULL REFERENCES persons, name INTEGER NOT NULL REFERENCES person_names, CHECK ((id = 0) = (person = 0) AND (id = 0) = (name = 0)), UNIQUE (person, name) ); -- "empty" stage name for artists that are groups INSERT INTO stage_names (id, person, name) VALUES (0, 0, 0); -- -- -- Basic information about groups -- -- CREATE SEQUENCE group_name_id_seq START 1; CREATE TABLE group_names ( id INTEGER DEFAULT nextval('group_name_id_seq') PRIMARY KEY, name TEXT NOT NULL, prefix TEXT DEFAULT '' NOT NULL, CHECK ((id = 0) = (name = '')) ); -- "empty" group name for unnamed collaborations INSERT INTO group_names (id, name) VALUES (0, ''); CREATE SEQUENCE lineup_id_seq START 1; CREATE TABLE lineups ( id INTEGER DEFAULT nextval('lineup_id_seq') PRIMARY KEY, name INTEGER NOT NULL REFERENCES group_names ); -- "empty" lineup for artists that are persons INSERT INTO lineups (id, name) VALUES (0, 0); -- -- -- An artist is a person or a group -- -- CREATE SEQUENCE artist_id_seq; CREATE TABLE artists ( id INTEGER DEFAULT nextval('artist_id_seq') PRIMARY KEY, person INTEGER DEFAULT 0 NOT NULL REFERENCES stage_names, lineup INTEGER DEFAULT 0 NOT NULL REFERENCES lineups, CHECK ((person = 0) != (lineup = 0)), UNIQUE (person, lineup) ); -- -- -- A lineup must have two or more artists (each of which may or may not -- have a defined role) or a name. -- -- CREATE SEQUENCE role_id_seq; CREATE TABLE roles ( id INTEGER DEFAULT nextval('role_id_seq') PRIMARY KEY, name TEXT NOT NULL UNIQUE CHECK (name != '') ); CREATE SEQUENCE artist_role_lineup_id_seq; CREATE TABLE artist_role_lineup ( id INTEGER DEFAULT nextval('artist_role_lineup_id_seq') PRIMARY KEY, artist INTEGER NOT NULL CHECK (artist != 0) REFERENCES artists, role INTEGER REFERENCES roles, lineup INTEGER NOT NULL CHECK (lineup != 0) REFERENCES lineups DEFERRABLE ); CREATE FUNCTION lineup_has_artists(INTEGER) RETURNS BOOLEAN AS 'SELECT (SELECT count(*) FROM artist_role_lineup WHERE lineup = $1) >= 2 AS RESULT;' LANGUAGE SQL; ALTER TABLE lineups ADD CHECK ( CASE WHEN id = 0 THEN (name = 0) ELSE (name != 0 OR lineup_has_artists(id)) END ); Creating a group with no membership information is straightforward: => INSERT INTO group_names (name, prefix) VALUES ('Police', 'The'); INSERT 34562 1 => SELECT * FROM group_names; id | name | prefix ----+--------+-------- 0 | | 1 | Police | The (2 rows) => INSERT INTO lineups (name) VALUES (1); INSERT 34563 1 => SELECT * FROM lineups; id | name ----+------ 0 | 0 1 | 1 (2 rows) => INSERT INTO artists (lineup) VALUES (1); INSERT 34564 1 pilcher=> SELECT * FROM artists; id | person | lineup ----+--------+-------- 1 | 0 | 1 (1 row) Adding information about individuals is also pretty simple: => INSERT INTO person_names (last_name, first_name) -> VALUES ('Fitzgerald', 'Ella'); INSERT 34713 1 => INSERT INTO person_names (last_name, first_name) -> VALUES ('Armstrong', 'Louis'); INSERT 34714 1 => SELECT * FROM person_names; id | last_name | first_name | middle_name ----+------------+------------+------------- 0 | | | 1 | Fitzgerald | Ella | 2 | Armstrong | Louis | (3 rows) => INSERT INTO persons (primary_name) VALUES (1); INSERT 34715 1 => INSERT INTO persons (primary_name) VALUES (2); INSERT 34716 1 => SELECT * FROM persons; id | given_name | legal_name | primary_name ----+------------+------------+-------------- 0 | | | 0 1 | | | 1 2 | | | 2 (3 rows) => INSERT INTO stage_names (person, name) VALUES (1, 1); INSERT 34717 1 => INSERT INTO stage_names (person, name) VALUES (2, 2); INSERT 34718 1 => SELECT * FROM stage_names; id | person | name ----+--------+------ 0 | 0 | 0 1 | 1 | 1 2 | 2 | 2 (3 rows) => INSERT INTO artists (person) VALUES (1); INSERT 34719 1 => INSERT INTO artists (person) VALUES (2); INSERT 34720 1 => SELECT * FROM artists; id | person | lineup ----+--------+-------- 1 | 0 | 1 2 | 1 | 0 3 | 2 | 0 (3 rows) Now, however, I want to create an "unnamed" collaboration between Ella Fitzgerald and Louis Armstrong: => BEGIN; BEGIN => SET CONSTRAINTS ALL DEFERRED; SET CONSTRAINTS => SELECT nextval('lineup_id_seq'); nextval --------- 2 (1 row) => INSERT INTO artist_role_lineup (artist, lineup) -> VALUES (2, 2); INSERT 34721 1 => INSERT INTO artist_role_lineup (artist, lineup) -> VALUES (3, 2); INSERT 34722 1 => INSERT INTO lineups (id, name) VALUES (2, 0); INSERT 34723 1 => COMMIT; COMMIT => SELECT * FROM lineups; id | name ----+------ 0 | 0 1 | 1 2 | 0 (3 rows) => SELECT * FROM artist_role_lineup WHERE lineup = 2; id | artist | role | lineup ----+--------+------+-------- 1 | 2 | | 2 2 | 3 | | 2 (2 rows) => INSERT INTO artists (lineup) VALUES (2); INSERT 34724 1 => SELECT * FROM artists; id | person | lineup ----+--------+-------- 1 | 0 | 1 2 | 1 | 0 3 | 2 | 0 4 | 0 | 2 (4 rows) For some reason, I find it very counter-intuitive to create the artist_role_lineup entries before creating the lineup itself. I believe that I'm stuck with approach, because PostgreSQL doesn't support deferrable check constraints. To my questions (finally!): 1. Is there a better approach to working around the lack of deferrable check constraints? (I would consider an approach that allows me to create the lineup before the artist_role_lineup entries to be better -- assuming that the workaround doesn't create more obfuscation than it removes.) 2. If the answer to #1 is no, how can I encapsulate the steps necessary to create an unnamed lineup into some sort of function/procedure? Everything I've read seems to assume that a function must return some value and be invoked through a SELECT statement. I would like to be able to simply: create_unnamed_lineup(artist, artist) It should either succeed or throw(?) an error. (I can always add additional artists to a lineup later.) Thanks! -- ======================================================================== Ian Pilcher i.pilcher@comcast.net ========================================================================
В списке pgsql-novice по дате отправления: