Talk:Propagation constraint

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

UPDATE propagation

I've been told that MS SQL Server will propagate primary key updates to foreign keys in referencing tables via some mechanism that's peculiar to MS SQL Server. I find it hard to believe this is even logical! Anyone heard of this? Edj0321 (talk) 17:25, 25 June 2010 (UTC)[reply]

Any database engine that supports the ON UPDATE CASCADE clause on foreign keys can do this...

PostgreSQL:

db=# create table foo(foo_id int primary key);
db=# create table bar(foo_id int references foo (foo_id) on update cascade);
db=# insert into foo values(1);
db=# insert into bar values(1);
db=# update foo set foo_id=2;
db=# select * from bar;
foo_id 
------
     2
(1 row)

MySQL (watch out for gotchas):

mysql> create table foo(foo_id int primary key) engine=InnoDB;
mysql> create table bar(
    ->     foo_id int,
    ->     foreign key (foo_id) references foo (foo_id) on update cascade
    -> ) engine=InnoDB;
mysql> insert into foo values(1);
mysql> insert into bar values(1);
mysql> update foo set foo_id=2;
mysql> select * from bar;
+--------+
| foo_id |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)

-- intgr [talk] 20:48, 13 September 2010 (UTC)[reply]