创建级联更新，级联删除
2010年5月21日 21:16:57参考网站：
http://www.phpchina.com/manual/PostgreSQL/ddl-constraints.html
/*
创建级联更新，级联删除操作
author: dingdang
time :20100520
**/
--创建表
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
-- ...
);
/*
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE NO ACTION, --在SQLServer中设置为RESTRICT失败了
order_id integer REFERENCES orders ON DELETE NO ACTION,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
*/
/* 一个正确的级联更新级联删除
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE CASCADE ON UPDATE CASCADE,
order_id integer REFERENCES orders ON DELETE CASCADE ON UPDATE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
*/
--更新NO ACTION的一个例子.
CREATE TABLE order_items(
product_no integer REFERENCES products ON DELETE CASCADE ON UPDATE NO ACTION,
order_id integer REFERENCES orders ON DELETE CASCADE ON UPDATE NO ACTION,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
--删除表：
drop table products;
drop table orders;
drop table order_items;
--插入资料
insert into products values(1,'洗衣粉',10);
insert into products values(2,'透明皂',20);
insert into products values(3,'洗衣液',30);
insert into orders values(1,'Oracle');
insert into orders values(2,'IBM');
insert into orders values(3,'Sun');
insert into order_items values(1,1,100);
insert into order_items values(1,2,200);
insert into order_items values(1,3,300);
insert into order_items values(2,1,1000);
insert into order_items values(2,2,2000);
insert into order_items values(2,3,3000);
insert into order_items values(3,1,10000);
insert into order_items values(3,2,20000);
--删除操作
delete from products where product_no=1 --若设置为级联删除操作ON DELETE CASCADE，当删除掉product_no=1时，order_items中所有的product_no=1的所有行，也都被删除掉
delete from products where product_no=1 --若设置为级联删除操作ON DELETE NO ACTION:order_items中所有的product_no=1的所有行都删除掉后，再执行才成功，否则会失败
--更新操作
update products set product_no = 11 where product_no=1 --若设置为级联更新操作ON UPDATE CASCADE，当更新product_no=11时，order_items中所有的product_no=1的所有行，也都被更新为11
update products set product_no = 11 where product_no=1 --若设置为级联更新操作ON UPDATE CASCADE，当删除掉order_items中所有的product_no=1时，更新成功，否则失败
--查询操作：
select * from products order by product_no;
select * from orders order by order_id;
select * from order_items order by product_no,order_id;
页:
[1]