Talk:Propagation constraint

From Wikipedia, the free encyclopedia
Jump to: navigation, search

UPDATE propagation[edit]

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)

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)