one to many

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:

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).