PK UK enable validate 以及PK上INDEX unusable及 truncate table ，隐式 not null

DROP TABLE SYSTEM.TEST_CON CASCADE CONSTRAINTS;

CREATE TABLE SYSTEM.TEST_CON_PK
(
  C1  VARCHAR2(10 BYTE),
  C2  NUMBER,
  C3  DATE,
  C4  VARCHAR2(10 BYTE)
)

CREATE TABLE SYSTEM.TEST_CON_FK
(
  C1  VARCHAR2(10 BYTE),
  C2  NUMBER,
  C3  DATE,
  C4  VARCHAR2(10 BYTE)
)
 

alter table TEST_CON_PK
  add ( constraint TEST_CON_PK primary key (C1));---可以same name ,有没有 ()都可以

--ALTER TABLE SYSTEM.TEST_CON_PK ADD ( CONSTRAINT TEST_CON_UK1 UNIQUE (SYS_NC00005$) USING INDEX SYSTEM.IDX1 ENABLE VALIDATE);--完整语法

  

alter table TEST_CON_FK
  add constraint TEST_CON_FK foreign key (C1)
  references test_con_pk (C1);
  
insert into TEST_CON_PK (C1, C2, C3, C4)
values ('1', 1, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), '1');

 insert into TEST_CON_FK (C1, C2, C3, C4)
values ('1', 1, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), '1');--success

 insert into TEST_CON_FK (C1, C2, C3, C4)
values ('2', 1, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), '1');--ORA-02291: integrity constraint (SYSTEM.TEST_CON_FK) violated - parent key not found，主表如果为空值不可以是PK ，UK是可以的

insert into TEST_CON_PK (C1, C2, C3, C4)
values (null, 1, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), '1');---ORA-01400: cannot insert NULL into ("SYSTEM"."TEST_CON_PK"."C1")

alter table TEST_CON_PK drop constraint TEST_CON_PK;  --ORA-02273: this unique/primary key is referenced by some foreign keys
alter table TEST_CON_PK drop constraint TEST_CON_PK cascade; -- FK is dropped also

--try 2 column
alter table TEST_CON_PK add ( constraint TEST_CON_PK primary key (C1,C2)); 
alter table TEST_CON_PK add ( constraint TEST_CON_PK1 primary key (C1 )); ---ORA-02260: table can have only one primary key

insert into TEST_CON_PK (C1, C2, C3, C4)
values ('1', null, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), '1');--ORA-01400: cannot insert NULL into ("SYSTEM"."TEST_CON_PK"."C2")---两列PK 两个列都不能为空
 

alter table TEST_CON_FK add constraint TEST_CON_FK  foreign key (C4)
  references test_con_pk (C4); ---ORA-02270: no matching unique or primary key for this column-list
  

----- UK 可以建多个
alter table TEST_CON_PK add ( constraint TEST_CON_UK1 unique   (C4));
alter table TEST_CON_FK add constraint TEST_CON_FK  foreign key (C4)
  references test_con_pk (C4); ----- matching unique 
  
alter table TEST_CON_FK add constraint TEST_CON_FK  foreign key (C1)
  references test_con_pk (C1); ----- ORA-02270: no matching unique or primary key for this column-list
  
alter table TEST_CON_FK add constraint TEST_CON_FK1  foreign key (C1,C2)
  references test_con_pk (C1,C2); -----  NEED TWO COLUMN together，主键两列不能单独
  

alter table TEST_CON_PK add ( constraint TEST_CON_UK1 unique   (C4));
alter table TEST_CON_FK add constraint TEST_CON_FK  foreign key (C4)
  references test_con_pk (C4); ----- matching unique 
  
  
insert into TEST_CON_FK (C1, C2, C3, C4)
values ('1', 4, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), null);--ORA-02291: integrity constraint (SYSTEM.TEST_CON_FK1) violated - parent key not found

 insert into TEST_CON_PK (C1, C2, C3, C4)
values ('1', 4, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), null);  

insert into TEST_CON_FK (C1, C2, C3, C4)
values ('1', 4, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), null); 
insert into TEST_CON_FK (C1, C2, C3, C4)
values ('1', 5, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 5); --ORA-02291: integrity constraint (SYSTEM.TEST_CON_FK1) violated - parent key not found--null不=5

alter table TEST_CON_PK add ( constraint TEST_CON_UK2 unique   (C3,C4));

insert into TEST_CON_PK (C1, C2, C3, C4)
values ('1', 4, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), null); --insert twice --ORA-00001: unique constraint (SYSTEM.TEST_CON_PK) violated
insert into TEST_CON_PK (C1, C2, C3, C4)
values ('1', 4, null, null); --insert twice --ORA-00001: unique constraint (SYSTEM.TEST_CON_PK) violated

insert into TEST_CON_FK (C1, C2, C3, C4)
values ('1', 4, to_date('30-12-1899 01:00:00','dd-mm-yyyy hh24:mi:ss'), null); 
insert into TEST_CON_FK (C1, C2, C3, C4)
values ('1', 4, to_date('30-12-1899 01:00:00','dd-mm-yyyy hh24:mi:ss'), null); --可以多次

 

---------------------------PK 的index 不能drop 但是可以unusable，unusable还是可以enable---

  
  
alter table TEST_CON_FK add constraint TEST_CON_FK1  

insert into TEST_CON_PK (C1, C2, C3, C4)
values ('1', 3, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), '3'); 
insert into TEST_CON_FK (C1, C2, C3, C4)
values ('1', 3, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), '3'); 
 
alter table TEST_CON_FK enable validate constraint TEST_CON_FK1  ;
alter table TEST_CON_FK enable novalidate constraint TEST_CON_FK1  ;

alter index test_con_pk unusable;
alter table test_con_pk enable validate constraint test_con_pk  ;---no problem
insert into TEST_CON_PK (C1, C2, C3, C4)
values ('1', 4, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), '4'); --ORA-01502: index 'SYSTEM.TEST_CON_PK' or partition of such index is in unusable state

drop  index test_con_pk  ;---ORA-02429: cannot drop index used for enforcement of unique/primary key

alter table TEST_CON_FK enable validate constraint TEST_CON_FK1  ;---即使unusable index no problem

  

select status from dba_indexes a where a.INDEX_NAME='TEST_CON_PK'
---UNUSABLE--

alter index test_con_pk unusable;
alter index test_con_uk1 unusable;
alter index test_con_uk2 unusable;

-- Create/Recreate primary, unique and foreign key constraints 
alter table TEST_CON_FK
  add constraint TEST_CON_FK2 foreign key (C1)
  references test_con_pk (C4);----全部unusable也不影响references

  
  

-----truncate table  index 自动valid----

SQL>   truncate table test_con_pk;
truncate table test_con_pk

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> select status from dba_indexes a where a.INDEX_NAME like 'TEST_CON%';

STATUS
--------
UNUSABLE
UNUSABLE
UNUSABLE

SQL> truncate table test_con_fk;

Table truncated

SQL> truncate table test_con_pk;

Table truncated

SQL> select status from dba_indexes a where a.INDEX_NAME like 'TEST_CON%';

STATUS
--------
VALID
VALID
VALID

SQL> 

 
  
  

------隐式 not null 由validate 决定

 

SQL> desc test_con_pk;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
C1   VARCHAR2(10) Y                         
C2   NUMBER       Y                         
C3   DATE         Y                         
C4   VARCHAR2(10) Y                         

 
SQL> alter table TEST_CON_PK
  2    modify constraint TEST_CON_PK validate;

Table altered

 

SQL> desc test_con_pk;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
C1   VARCHAR2(10)                           
C2   NUMBER                                 
C3   DATE         Y                         
C4   VARCHAR2(10) Y                         

SQL>  alter table TEST_CON_PK  modify constraint TEST_CON_PK  novalidate;

Table altered

SQL>  desc test_con_pk;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
C1   VARCHAR2(10) Y                         
C2   NUMBER       Y                         
C3   DATE         Y                         
C4   VARCHAR2(10) Y                         

SQL> alter table TEST_CON_PK  modify constraint TEST_CON_PK enable novalidate;

Table altered

SQL> desc test_con_pk;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
C1   VARCHAR2(10) Y                         
C2   NUMBER       Y                         
C3   DATE         Y                         
C4   VARCHAR2(10) Y                         

SQL>  alter table TEST_CON_PK  modify constraint TEST_CON_PK enable validate;

Table altered

SQL>  desc test_con_pk;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
C1   VARCHAR2(10)                           
C2   NUMBER                                 
C3   DATE         Y                         
C4   VARCHAR2(10) Y                         

SQL> alter table TEST_CON_PK  modify constraint TEST_CON_PK disable  validate;
ORA-02297: cannot disable constraint (SYSTEM.TEST_CON_PK) - dependencies exist

>  alter table TEST_CON_PK  modify constraint TEST_CON_PK  disable novalidate cascade;

Table altered

SQL>  desc test_con_pk;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
C1   VARCHAR2(10) Y                         
C2   NUMBER       Y                         
C3   DATE         Y                         
C4   VARCHAR2(10) Y                         

SQL>  alter table TEST_CON_PK  modify constraint TEST_CON_PK  disable  validate;

Table altered

SQL>  desc test_con_pk;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
C1   VARCHAR2(10)                           
C2   NUMBER                                 
C3   DATE         Y                         
C4   VARCHAR2(10) Y                         

SQL> alter table TEST_CON_PK  modify constraint TEST_CON_PK enable novalidate;

Table altered

SQL>  desc test_con_pk;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
C1   VARCHAR2(10) Y                         
C2   NUMBER       Y                         
C3   DATE         Y                         
C4   VARCHAR2(10) Y                         

-----虽然没有隐式not null ，还是插入不了NULL

SQL>  alter table TEST_CON_PK  modify constraint TEST_CON_PK enable novalidate;

Table altered

SQL> desc test_con_pk;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
C1   VARCHAR2(10) Y                         
C2   NUMBER       Y                         
C3   DATE         Y                         
C4   VARCHAR2(10) Y                         

 SQL> insert into TEST_CON_PK (C1, C2, C3, C4)  values (null, 3, to_date('30-12-1899 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), '3');
ORA-01400: cannot insert NULL into ("SYSTEM"."TEST_CON_PK"."C1")

-----------disable cascade --enable 没有 cascade

SQL> alter table TEST_CON_PK  modify constraint TEST_CON_PK enable cascade;
alter table TEST_CON_PK  modify constraint TEST_CON_PK enable cascade

ORA-00933: SQL command not properly ended

SQL> alter table TEST_CON_PK  modify constraint TEST_CON_PK disable cascade;

Table altered

SQL> alter table TEST_CON_PK  modify constraint TEST_CON_PK enable cascade;
alter table TEST_CON_PK  modify constraint TEST_CON_PK enable cascade

ORA-00933: SQL command not properly ended

SQL>