Thread: one to many
CC me please. How do I set up a one to many relationship in Postgres, (any DB for that matter.) I.E., if a delete or update of a child table causes a row in the parent table to no longer refer to any rows in the child table, to either cause Postgres to error out or delete the parent? I can see it does it for when a parent is upudated or deleted.
So, you're looking for something like "referenced by" instead of the SQL-standard "references"? Seems like you could always whip up a stored procedure.... On May 16, 2004, at 9:27 AM, Dennis Gearon wrote: > CC me please. > > How do I set up a one to many relationship in Postgres, (any DB for > that matter.) > > I.E., if a delete or update of a child table causes a row in the > parent table to no longer refer to any rows in the child table, to > either cause Postgres to error out or delete the parent? I can see it > does it for when a parent is upudated or deleted. > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match
Dennis Gearon wrote: > How do I set up a one to many relationship in Postgres, (any DB for > that matter.) Read about foreign keys: http://www.postgresql.org/docs/7.4/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
Peter Eisentraut wrote: >Dennis Gearon wrote: > > >>How do I set up a one to many relationship in Postgres, (any DB for >>that matter.) >> >> > >Read about foreign keys: > >http://www.postgresql.org/docs/7.4/static/ddl-constraints.html#DDL-CONSTRAINTS-FK > > > That only takes care of if the PARENT is deleted/updated, if I read it right. I'm trying to take care of the case where the CHILD is deleted or updated, i.e. preserving 1-Many part of a rlationship.
On Sun, 16 May 2004, Dennis Gearon wrote: > CC me please. > > How do I set up a one to many relationship in Postgres, (any DB for that > matter.) > > I.E., if a delete or update of a child table causes a row in the parent > table to no longer refer to any rows in the child table, to either cause > Postgres to error out or delete the parent? I can see it does it for > when a parent is upudated or deleted. I don't think there's any built in direct way to do this right now, but you could probably build triggers that would do it for you (the current foreign key triggers might give a starting point. You'd probably also want to use CREATE CONSTRAINT TRIGGER to be able to defer the trigger (similarly to how the foreign key deferred works).
Stephan Szabo wrote: >On Sun, 16 May 2004, Dennis Gearon wrote: > > > >>CC me please. >> >>How do I set up a one to many relationship in Postgres, (any DB for that >>matter.) >> >>I.E., if a delete or update of a child table causes a row in the parent >>table to no longer refer to any rows in the child table, to either cause >>Postgres to error out or delete the parent? I can see it does it for >>when a parent is upudated or deleted. >> >> > >I don't think there's any built in direct way to do this right now, but >you could probably build triggers that would do it for you (the current >foreign key triggers might give a starting point. You'd probably also >want to use CREATE CONSTRAINT TRIGGER to be able to defer the trigger >(similarly to how the foreign key deferred works). > > > > I didn't know you could set up triggers to be deferred! AWESOME! That makes for some additional flexibility that I could use. Thank you very much Stephen. I was beginnning to consider TRIGGERS as the solution; Now I know that they will work.
> Stephan Szabo wrote: > > >On Sun, 16 May 2004, Dennis Gearon wrote: > > > > > > > >>CC me please. > >> > >>How do I set up a one to many relationship in Postgres, (any DB for that > >>matter.) > >> > >>I.E., if a delete or update of a child table causes a row in the parent > >>table to no longer refer to any rows in the child table, to either cause > >>Postgres to error out or delete the parent? I can see it does it for > >>when a parent is upudated or deleted. > >> > >> > > > >I don't think there's any built in direct way to do this right now, but > >you could probably build triggers that would do it for you (the current > >foreign key triggers might give a starting point. You'd probably also > >want to use CREATE CONSTRAINT TRIGGER to be able to defer the trigger > >(similarly to how the foreign key deferred works). > > > > > > > > > I didn't know you could set up triggers to be deferred! AWESOME! That > makes for some additional flexibility that I could use. > > Thank you very much Stephen. I was beginnning to consider TRIGGERS as > the solution; Now I know that they will work. Well, I should note that AFAIK CREATE CONSTRAINT TRIGGER is considered an "internal" feature. It's unlikely to go away until it's considered okay to break compatibility with 7.0/7.1 dumps however (and would be more likely to be replaced with a more general feature anyway).