I have an entity-relationship diagram like this
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
- 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.
- 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.
- 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?