复制表的时候为什么约束会丢失

 

有如下一个创建表的方法：

create table tablename as select * from anothertable

这种方法创建的表只有非空约束，anothertable其他约束都不会被复制到t新表中

这个问题的答案是：非空之外的约束没有存储到表结构中，有个单独的数字字典来存放这些约束user_constraints

SQL> desc user_tab_columns
 名称                                                                                                              是否为空? 类型
 ----------------------------------------------------------------------------------------------------------------- -------- 
 TABLE_NAME                                                                                                        NOT NULL VARCHAR2(30)
 COLUMN_NAME                                                                                                       NOT NULL VARCHAR2(30)
 DATA_TYPE                                                                                                                  VARCHAR2(106)
 DATA_TYPE_MOD                                                                                                              VARCHAR2(3)
 DATA_TYPE_OWNER                                                                                                            VARCHAR2(30)
 DATA_LENGTH                                                                                                       NOT NULL NUMBER
 DATA_PRECISION                                                                                                             NUMBER
 DATA_SCALE                                                                                                                 NUMBER
 NULLABLE                                                                                                                   VARCHAR2(1)
 COLUMN_ID                                                                                                                  NUMBER
 DEFAULT_LENGTH                                                                                                             NUMBER
 DATA_DEFAULT                                                                                                               LONG
 NUM_DISTINCT                                                                                                               NUMBER
 LOW_VALUE                                                                                                                  RAW(32)
 HIGH_VALUE                                                                                                                 RAW(32)
 DENSITY                                                                                                                    NUMBER
 NUM_NULLS                                                                                                                  NUMBER
 NUM_BUCKETS                                                                                                                NUMBER
 LAST_ANALYZED                                                                                                              DATE
 SAMPLE_SIZE                                                                                                                NUMBER
 CHARACTER_SET_NAME                                                                                                         VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                                                                                                       NUMBER
 GLOBAL_STATS                                                                                                               VARCHAR2(3)
 USER_STATS                                                                                                                 VARCHAR2(3)
 AVG_COL_LEN                                                                                                                NUMBER
 CHAR_LENGTH                                                                                                                NUMBER
 CHAR_USED                                                                                                                  VARCHAR2(1)
 V80_FMT_IMAGE                                                                                                              VARCHAR2(3)
 DATA_UPGRADED                                                                                                              VARCHAR2(3)
 HISTOGRAM                                                                                                                  VARCHAR2(15)

SQL> desc user_constraints
 名称                                                                                         是否为空?                           类型
 ----------------------------------------------------------------------------------------------------------------- 
 OWNER                                                                                   NOT NULL             VARCHAR2(30)
 CONSTRAINT_NAME                                                            NOT NULL                 VARCHAR2(30)
 CONSTRAINT_TYPE                                                                                                            VARCHAR2(1)
 TABLE_NAME                                                                        NOT NULL                   VARCHAR2(30)
 SEARCH_CONDITION                                                                                                           LONG
 R_OWNER                                                                                                                    VARCHAR2(30)
 R_CONSTRAINT_NAME                                                                                                          VARCHAR2(30)
 DELETE_RULE                                                                                                                VARCHAR2(9)
 STATUS                                                                                                                     VARCHAR2(8)
 DEFERRABLE                                                                                                                 VARCHAR2(14)
 DEFERRED                                                                                                                   VARCHAR2(9)
 VALIDATED                                                                                                                  VARCHAR2(13)
 GENERATED                                                                                                                  VARCHAR2(14)
 BAD                                                                                                                        VARCHAR2(3)
 RELY                                                                                                                       VARCHAR2(4)
 LAST_CHANGE                                                                                                                DATE
 INDEX_OWNER                                                                                                                VARCHAR2(30)
 INDEX_NAME                                                                                                                 VARCHAR2(30)
 INVALID                                                                                                                    VARCHAR2(7)
 VIEW_RELATED                                                                                                               VARCHAR2(14)

SQL> desc user_tab_columns

TABLE_NAME                                        COLUMN_NAME                                           DATA_TYPE                     

EMP2                                                      EMPNO                                                       NUMBER                                                                                                                                                                         
EMP                                                          SAL                                                          NUMBER                                                           
EMP                                                          COMM                                                         NUMBER                                                             
EMP2                                                         HIREDATE                                                     DATE                                                               
EMP                                                          JOB                                                          VARCHAR2                                                            
EMP2                                                         COMM                                                         NUMBER                                                              
EMP2                                                         SAL                                                          NUMBER                                                               
DEPT                                                         DNAME                                                        VARCHAR2                                                           
EMP2                                                         JOB                                                          VARCHAR2                                                             
DEPT                                                         DEPTNO                                                       NUMBER                                                              
PRODUCT                                                      PID                                                          NUMBER                                                          
EMP                                                          HIREDATE                                                     DATE                                                            
PRODUCT                                                      PDATE                                                        DATE                                                            
PRODUCT                                                      PLOC                                                         CHAR                                                      
EMP2                                                         ENAME                                                        VARCHAR2                                           
EMP2                                                         MGR                                                          NUMBER                                                          
EMP2                                                         DEPTNO                                                       NUMBER                                                            
EMP                                                          DEPTNO                                                       NUMBER                                                        
DEPT                                                         LOC                                                          VARCHAR2                                                        
PRODUCT                                                      FKDEPTNO                                                     NUMBER                                                         
EMP                                                          EMPNO                                                        NUMBER                                                      
EMP                                                          MGR                                                          NUMBER                                                    
PRODUCT                                                      PNAME                                                        VARCHAR2                                                        
EMP                                                          ENAME                                                        VARCHAR2                                                        

已选择24行。

SQL> select table_name,constraint_name,constraint_type from user_constraints;

TABLE_NAME                                                   CONSTRAINT_NAME                                              CO                                                         
------------------------------------------------------------ ------------------------------------------------------------ --                                                                                                                                                                                                                                                                                                                                                                                       
DEPT                                                         PK_DEPT                                                                        P                                                       
EMP                                                          SYS_C009406                                                                C                                                        
EMP                                                          PK_EMP                                                                             P                                                         
EMP                                                          FK_DEPTNO                                                                    R                                               
PRODUCT                                              NN_PDATE_PRODUCT                                              C                                                  
EMP2                                                        SYS_C009530                                                              C                                                    
PRODUCT                                              CK_PID_PRODUCT                                                       C                                        
PRODUCT                                              PK_PID_PRODUCT                                                    P                                                    
PRODUCT                                              UK_PNAME_PRODUCT                                             U                                                
PRODUCT                                               FK_DEPTNO_PRODUCT                                            R                                                            

已选择10行。