CODEWITHSUNDEEP
postgresqlsql-updatecascade
Phil Freeman
Phil Freeman

Reputation: 256

ON UPDATE CASCADE with no foreign key in postgresql

I've got some tables I need to cascade update in postgresql, but I don't think I can use foreign keys to do it.

The simple version is I have 4 object tables and 2 tying tables:

Table X    Table A       Table A-B      Table B      Table B-C    Table C
-------    --------      ---------      -------      ---------    -------
x_id         a_id           x_id          b_id          x_id        c_id
...          ...            a_id          ...           b_id        ...
...          ...            b_id          ...           c_id        ...
                            ...                         ...

On my tying tables, I have a PK on all the id fields (x_id, a_id, b_id), (x_id, b_id, c_id).

If I update b_id in Table A-B, I want it to update b_id in Table B-C where x_id matches.

If I try to create a foreign key constraint, it tells me

"there is no unique constraint matching given keys for referenced table..."

I understand that it's not doing that because(x_id,b_id) isn't a unique constraint, but it can't be, because I'll have multiple c_id's with the same b_id and x_id.

Hopefully this makes sense.

Is the only way to do this with triggers?

Upvotes: 1

Views: 1555

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95741

I understand that it's not doing that because(x_id,b_id) isn't a unique constraint, but it can't be, because I'll have multiple c_id's with the same b_id and x_id.

In order to have an update to b_id in Table A-B cascade to Table B-C, you need

  • a unique constraint on some set of columns that includes Table_A-C.b_id,
  • that same set of columns in Table_B-C, and
  • a foreign key reference in Table_B-C referencing the same set of columns in Table_A-C, with ON UPDATE CASCADE.

It's not clear to me whether that's possible with your current structure.

If

  • Table_A-B.x_id is an autoincrementing integer, it's unique by itself, then
  • in Table_A-B, the set of columns {x_id, b_id} will also be unique, and
  • in Table_B-C, the column x_id is an integer (or bigint), then
  • in Table_B-C the set of columns {x_id, b_id} can have a foreign key reference to Table_A-C.

Upvotes: 1

Related Questions