主键约束

sys@ORCL> create table t (id int primary key,id1 int);

Table created.

sys@ORCL> select CONSTRAINT_NAME,COLUMN_NAME from  dba_cons_columns where table_name='T';

CONSTRAINT_NAME
------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
SYS_C0011647
ID


sys@ORCL> select index_name,column_name from dba_ind_columns where table_name='T';

INDEX_NAME
------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
SYS_C0011647
ID

默认创建约束和索引。

 

sys@ORCL>  alter table t modify (id int );

Table altered.

sys@ORCL> desc t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                    NOT NULL NUMBER(38)
 ID1                                                            NUMBER(38)

sys@ORCL> select CONSTRAINT_NAME,COLUMN_NAME from  dba_cons_columns where table_name='T';

CONSTRAINT_NAME
------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
SYS_C0011647
ID


sys@ORCL>  select index_name,column_name from dba_ind_columns where table_name='T';

INDEX_NAME
------------------------------------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
SYS_C0011647
ID

sys@ORCL> select dbms_metadata.get_ddl('TABLE','T','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T','SYS')
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."T"
   (    "ID" NUMBER(*,0),
        "ID1" NUMBER(*,0),
         PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC
HE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC
HE DEFAULT)
  TABLESPACE "SYSTEM"

可以得出modify是不能修改表的主键，

sys@ORCL> alter table t drop constraint SYS_C0011647; Table altered. sys@ORCL>  select dbms_metadata.get_ddl('TABLE','T','SYS') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE','T','SYS') --------------------------------------------------------------------------------   CREATE TABLE "SYS"."T"    (    "ID" NUMBER(*,0),         "ID1" NUMBER(*,0) 主键字眼不见了    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC HE DEFAULT)   TABLESPACE "SYSTEM"   sys@ORCL> select index_name,column_name from dba_ind_columns where table_name='T'; no rows selected

 

sys@ORCL>  alter table t add constraint ind_t primary key(id); Table altered. sys@ORCL>  select dbms_metadata.get_ddl('TABLE','T','SYS') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE','T','SYS') --------------------------------------------------------------------------------   CREATE TABLE "SYS"."T"    (    "ID" NUMBER(*,0),         "ID1" NUMBER(*,0),          CONSTRAINT "IND_T" PRIMARY KEY ("ID")   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC HE DEFAULT)   TABLESPACE "SYSTEM"  ENABLE    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC HE DEFAULT)   TABLESPACE "SYSTEM"   sys@ORCL> select index_name,column_name from dba_ind_columns where table_name='T'; INDEX_NAME ------------------------------------------------------------ COLUMN_NAME ---------------------------------------------------------------------------------------------------- IND_T ID

 

这样能指定索引、约束的名字。