先看我下面已经创建的SQL代码。
我现在要求这样:允许更改CUSTOMER表CU_ID字段。当CUSTOMER表CU_ID更改时,其sales表CU_ID字段也就自动更改。请问要怎么做???CREATE TABLE CUSTOMER
(
CU_ID NUMBER(4),
CU_NO NUMBER(2)
)
CREATE TABLE sales
(
CU_ID NUMBER(4),
s_code NUMBER(2),
s_model VARCHAR2(50 BYTE)
)
ALTER TABLE SALES ADD (
CONSTRAINT FK_SALES_CU_ID
FOREIGN KEY (CU_ID)
REFERENCES CUSTOMER (CU_ID));
我现在要求这样:允许更改CUSTOMER表CU_ID字段。当CUSTOMER表CU_ID更改时,其sales表CU_ID字段也就自动更改。请问要怎么做???CREATE TABLE CUSTOMER
(
CU_ID NUMBER(4),
CU_NO NUMBER(2)
)
CREATE TABLE sales
(
CU_ID NUMBER(4),
s_code NUMBER(2),
s_model VARCHAR2(50 BYTE)
)
ALTER TABLE SALES ADD (
CONSTRAINT FK_SALES_CU_ID
FOREIGN KEY (CU_ID)
REFERENCES CUSTOMER (CU_ID));
解决方案 »
- oracel 企业管理平台问题
- ORA-04021: 等待锁定对象 ULTRANMS.AGGRE_PROVINCE_CONDIRECTION 时发生超时
- oracle9i pre_page_sga=true的问题
- 急等,高手帮忙,我的pl/sql devloper 软件为啥无法使用导出表功能?
- 请教一个oracle生产环境下监听的问题!
- 写删除索引的过程出错
- proc多线程的数据库连接问题?
- 急请问高手!!
- 急:Oracle中能否只导出数据而不导出表结构等信息?
- 求一条sql语句!
- asp应用程序中如何调用orcle的存储过程?
- 如何在oracle或sqlserver中用insert into 命令或者其他命令插入多行值
SQL> create table t_main(
2 m_id number(4) not null,
3 c1 varchar(10),
4 constraint pk_main primary key(m_id));Table created.SQL> create table t_sub(
2 sub_id number(8) not null,
3 m_id number(4) not null,
4 c2 varchar(10),
5 constraint pk_sub primary key(sub_id));Table created.SQL> alter table t_sub add constraint fk_sub foreign key (m_id) references t_main(m_id);Table altered.SQL> create or replace trigger tau_main
2 after update on t_main for each row
3 begin
4 if :new.m_id<>:old.m_id then
5 update t_sub set m_id=:new.m_id where m_id=:old.m_id;
6 end if;
7 end;
8 /Trigger created.SQL> insert into t_main values(1,'a');1 row created.SQL> insert into t_main values(2,'b');1 row created.SQL> insert into t_main values(3,'c');1 row created.SQL> insert into t_sub values(11,1,'a1');1 row created.SQL> insert into t_sub values(12,1,'a2');1 row created.SQL> insert into t_sub values(21,2,'b1');1 row created.SQL> commit;Commit complete.SQL> update t_main set m_id=1111 where m_id=1;1 row updated.SQL> commit;Commit complete.SQL> select * from t_main; M_ID C1
---------- ----------
1111 a
2 b
3 cSQL> select * from t_sub; SUB_ID M_ID C2
---------- ---------- ----------
11 1111 a1
12 1111 a2
21 2 b1
SQL> alter table t_sub modify constraint fk_sub initially deferred;Table altered.SQL> alter trigger tau_main disable;Trigger altered.SQL> update t_main set m_id=m_id+1;3 rows updated.SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: 事务处理已重算
ORA-02292: 违反完整约束条件 (SA.FK_SUB) - 已找到子记录日志
SQL> update t_main set m_id=m_id+1;3 rows updated.SQL> update t_sub set m_id=m_id+1;3 rows updated.SQL> commit;Commit complete.SQL> select * from t_main; M_ID C1
---------- ----------
2 a
3 b
4 cSQL> select * from t_sub; SUB_ID M_ID C2
---------- ---------- ----------
11 2 a1
12 2 a2
21 3 b1