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