Foreign key reference with ON UPDATE CASCADE inside array

Question

I've a natural primary key in table A.

In table B, I want to have an array of foreign key references to A.

Is it possible to specify ON UPDATE CASCADE on the elements of the array, such that when the value of a primary key in table A changes, arrays in B get modified. Or should I just normalise the array out into a separate table?

Erwin Brandstetter · Answer 1 · 2022-04-28T14:31:16.637

2

Normalizing this would allow you to use standard ON UPDATE CASCADE in a foreign key constraint. That would be much faster, because the system can use plain indexes. That should give you three tables. Needs somewhat more disk space, but worth every bit:

table a
table b
table a_b -- to implement n:m relationship

See:

Else you will have to write a trigger function to find and replace all references in B to values of master A.

edited Apr 28 '22 at 14:31

answered Oct 03 '11 at 16:53

Erwin Brandstetter

605,456
145
1,078
1,228

Agreed- you definitely want to do it this way. You don't want to get in to messy, slow triggers. A little extra disk space saves you a lot of hassle and query time. – Chris Cashwell Oct 03 '11 at 17:49
Hmmm, although I didn't mention it, I'm in the process of denormalising from an existing a_b table. Because I've got compound/natural primary keys in a, the little extra disk space in this case more than doubles disk space usage, and is significant. – EoghanM Mar 02 '12 at 09:25
@EoghanM: In this case I would resort to surrogate primary keys. Add a serial column as primary (or unique) key. – Erwin Brandstetter Mar 02 '12 at 17:39

score 0 · Answer 2 · answered Oct 03 '11 at 18:11

Is it possible to specify ON UPDATE CASCADE on the elements of the array, such that when the value of a primary key in table A changes, arrays in B get modified.

Only if

both the referenced column and the referencing column are arrays of the same type, and
the values have the same number of elements.

If you want to insert valid values for array elements in one table, and in another table store an array of those valid values, it won't work.

OTOH, this does work, but only in part.

create table a (
  str varchar[2] primary key
);

create table b (
  -- Room for two values from table a . . .
  str varchar[4] primary key references a (str) on update cascade
);

insert into a values 
('{''A'', ''B''}'),
('{''C'', ''D''}'),
('{''E'', ''F''}');

insert into b values 
('{''A'', ''B''}');

update a set str = '{''A'',''C''}'
where str = '{''A'',''B''}';

select * from b;
{'A','C'}

That much works. But if you try to store two arrays in table b, you'll get an error.

insert into b values
('{{"C", "D"}, {"E", "F"}}');
ERROR:  insert or update on table "b" violates foreign key constraint "b_str_fkey"
DETAIL:  Key (str)=({{C,D},{E,F}}) is not present in table "a".

And, when you squint and tilt your head just right, that makes sense. In the relational model, the intersection of every row and column contains just one value. So you shouldn't be able to update half a value by ON UPDATE CASCADE.

Foreign key reference with ON UPDATE CASCADE inside array

2 Answers2