one to many

[Dennis Gearon]

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

[Ben]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Dennis Gearon]

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/s...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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Peter Eisentraut]

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/s...CONSTRAINTS-FK

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stephan Szabo]

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).
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Dennis Gearon]

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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Stephan Szabo]

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).
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org