Skip site navigation (1) Skip section navigation (2)

"Procedure" or deferrable check constraint?

From: Ian Pilcher <i(dot)pilcher(at)comcast(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: "Procedure" or deferrable check constraint?
Date: 2004-05-15 19:49:08
Message-ID: 40A67434.9050202@comcast.net (view raw or flat)
Thread:
Lists: 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(dot)pilcher(at)comcast(dot)net
========================================================================


pgsql-novice by date

Next:From: Bob JarmanDate: 2004-05-17 03:10:18
Subject: 7.4.2 & DBVis
Previous:From: Michael A WeberDate: 2004-05-15 00:11:35
Subject: Set search_path

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group