sqlpostgresqlcardinality

PostgreSQL: implement minimum cardinality database

I have an entity-relationship diagram like this
enter image description here

So in PostgreSQL I've created three tables like these:

create table Artist(
    id id primary key
)

create table Musical_Event(
    id id primary key
)

create table Play_In(
    artist id,
    musical_event id,
    primary key (artist, musical_event),
    foreign key (artist) references Artist(id),
    foreign key (musical_event) references Musical_Event(id)
)

As we can see from the diagram, a musical event must have at least one artist who plays in. I'm not sure how to implement this constraint in PostgreSQL. If a row in the table Play_In is deleted, I can simply create a trigger which checks that the musical event involved by the row has at least one other artist, and, if there isn't, raise an exception. However I can't create a trigger like this which activates when I insert a new musical event, in fact I can't insert a row in Play_in before the musical event involved is created, since this will violate the foreign key constraint. So the only ways that came to my mind are

  1. create a trigger that activates after a new row in Musical_Event is inserted, and calls a function that prompts the user to add a row in Play_In too (which refers to the same musical_event). But I don't know if this is possible.
  2. Create a function that inserts a row in both Musical_Event and Play_in (with musical_event column equal to the new musical event's id), and the user can insert a new row in Musical_Event only by using this function.
  3. Create a view as the join of Musical_Event and Play_in, with a trigger that activate every time a new row in inserted in the view and call a function that inserts the new values in Musical_Event and Play_In. The user can insert a new row in Musical_event only by inserting values in this view.

Are there other options? Which is the best way to handle this situation and how to implement it?

Solution

  • This is a very difficult relationship to implement. Because:

    Circular logic. There are some ways to "get around" the problem -- for instance by turning off or deferring constraints. So, one method is to insert the artists and events in a single transaction using deferred constraints. This works for Postgres and is probably the preferred solution for Postgres. It is not, however, a general solution for all databases.

    Another solution is to have a special artist. This special artist would have a direct foreign key relationship from events to artists and be NOT NULL. The problem is that one artist is "special" so would be queried separately.

    I think my preferred approach if you really want to "enforce" this is to add an artist_count to the events table. This count can be maintained using triggers.

    Then use a view to show valid events:

    create view v_events as
    select e.*
    from events e
    where artist_count > 0;

    The code that uses the view would only have events that have valid artists and you won't have a problem populating the data.