Oracle 有关约束的实验
SQL> CREATE TABLE depto04( dno INT PRIMARY KEY, dname VARCHAR2(10),loc VARCHAR2(20) ); 2 3 4 Table created. SQL> SQL> CREATE TABLE emp04( empno INT , ename VARCHAR2(10),dno INT ); 2 3 4 Table created. SQL> create index emp04_idx on emp04(empno); Index created. SQL> alter table emp04 add constraint emp04_fk foreign key(dno) references deptno(dno) on delete cascade; alter table emp04 add constraint emp04_fk foreign key(dno) references deptno(dno) on delete cascade * ERROR at line 1: ORA-00942: table or view does not exist SQL> alter table emp04 add constraint emp04_fk foreign key(dno) references deptno04(dno) on delete cascade; alter table emp04 add constraint emp04_fk foreign key(dno) references deptno04(dno) on delete cascade * ERROR at line 1: ORA-00942: table or view does not exist SQL> alter table emp04 add constraint emp04_fk foreign key(dno) references deptno04 (dno) on delete cascade; alter table emp04 add constraint emp04_fk foreign key(dno) references deptno04 (dno) on delete cascade * ERROR at line 1: ORA-00942: table or view does not exist SQL> alter table emp04 add constraint emp04_fk foreign key(dno) references depto04(dno) on delete cascade; Table altered. SQL> SQL> inert into table emp04 values(123,'dayu',1); SP2-0734: unknown command beginning "inert into..." - rest of line ignored. SQL> insert into table emp04 values(123,'dayu',1); insert into table emp04 values(123,'dayu',1) * ERROR at line 1: ORA-00903: invalid table name SQL> insert into emp04 values(123,'dayu',1); insert into emp04 values(123,'dayu',1) * ERROR at line 1: ORA-02291: integrity constraint (DAYU.EMP04_FK) violated - parent key not found SQL> insert into depto04 values(1,'shuiwu','mazhuang'); 1 row created. SQL> commit; Commit complete. SQL> update table depto04 set dno=2 where dno=1; update table depto04 set dno=2 where dno=1 * ERROR at line 1: ORA-00903: invalid table name SQL> update depto04 set dno=2 where dno=1; 1 row updated. SQL> select count(*) from emp04; COUNT(*) ---------- 0 SQL> insert into emp04(123,'dayu',1); insert into emp04(123,'dayu',1) * ERROR at line 1: ORA-00928: missing SELECT keyword SQL> insert into emp04 values(123,'dayu',1); insert into emp04 values(123,'dayu',1) * ERROR at line 1: ORA-02291: integrity constraint (DAYU.EMP04_FK) violated - parent key not found SQL> rollback; Rollback complete. SQL> ^[[2~insert into emp04 values(123,'dayu',1); SP2-0734: unknown command beginning "[2~insert..." - rest of line ignored. SQL> insert into emp04 values(123,'dayu',1); 1 row created. SQL> commit; Commit complete. SQL> update depto04 set dno=2 where dno=1; update depto04 set dno=2 where dno=1 * ERROR at line 1: ORA-02292: integrity constraint (DAYU.EMP04_FK) violated - child record found SQL> insert into depto04 values(2,'shuiwu','mazhuang'); 1 row created. SQL> commit; Commit complete. SQL> update depto04 set dno=2 where dno=2; 1 row updated. SQL> update emp04 set ename='ma' where dno=1; 1 row updated. SQL> alter table depto04 drop dno; alter table depto04 drop dno * ERROR at line 1: ORA-00905: missing keyword SQL> alter table depto04 drop column dno; alter table depto04 drop column dno * ERROR at line 1: ORA-12992: cannot drop parent key column