- coco
-
ç¨user_indexesåuser_ind_columnsç³»ç»è¡¨æ¥çå·²ç»åå¨çç´¢å¼ å¯¹äºç³»ç»ä¸å·²ç»åå¨çç´¢å¼æ们å¯ä»¥éè¿ä»¥ä¸ç两个系ç»è§å¾(user_indexesåuser_ind_columns)æ¥æ¥çå ¶å ·ä½å 容ï¼ä¾å¦æ¯å±äºé£ä¸ªè¡¨ï¼åªä¸ªååï¼å ·ä½æäºä»ä¹åæ°ççã user_indexes: ç³»ç»è§å¾åæ¾
ç¨user_indexesåuser_ind_columnsç³»ç»è¡¨æ¥çå·²ç»åå¨çç´¢å¼
对äºç³»ç»ä¸å·²ç»åå¨çç´¢å¼æ们å¯ä»¥éè¿ä»¥ä¸ç两个系ç»è§å¾(user_indexesåuser_ind_columns)æ¥æ¥çå ¶å ·ä½å 容ï¼ä¾å¦æ¯å±äºé£ä¸ªè¡¨ï¼åªä¸ªååï¼å ·ä½æäºä»ä¹åæ°ççã
user_indexes: ç³»ç»è§å¾åæ¾æ¯ç´¢å¼çå称以å该索å¼æ¯å¦æ¯å¯ä¸ç´¢å¼çä¿¡æ¯ã
user_ind_column: ç³»ç»è§å¾åæ¾çæ¯ç´¢å¼å称ï¼å¯¹åºç表ååçã
æ¥çç´¢å¼ä¸ªæ°åç±»å«:
SQL> select * from user_indexes where table="表å" ;
æ¥çç´¢å¼è¢«ç´¢å¼çå段:
SQL> select * from user_ind_columns where index_name=upper("&index_name");
æ们å¯ä»¥éè¿ç±»似ä¸é¢çè¯å¥æ¥æ¥çä¸ä¸ªè¡¨çç´¢å¼çåºæ¬æ åµï¼
select user_ind_columns.index_name,user_ind_columns.column_name,
user_ind_columns.column_position,user_indexes.uniqueness
from user_ind_columns,user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = âä½ æ³è¦æ¥è¯¢ç表ååâ;
éè¿è¿æ¡SQLè¯å¥æ们è½æ¥çå°ä¸ä¸ªè¡¨çå ·ä½çç´¢å¼çæ åµï¼å¦æä½ æ³å¯¹è¿è¡¨çç´¢å¼è¿è¡è¿ä¸æ¥çæ¢ç©¶ä½ åºè¯¥å°user_indexesä¸å»å ·ä½çç以ä¸è¿ä¸ªç´¢å¼çåºæ¬æ åµã
å®æ´æ§çº¦æ
DBA_CONSTRAINTSãALL_CONSTRAINTSåUSER_CONSTRAINST æ¾ç¤ºæå ³çº¦æçä¸è¬ä¿¡æ¯ã
DBA_CONS_COLUMNSãALL_CONS_COLUMNSåUSER_CONS_COLUMNS æ¾ç¤ºæå ³åçç¸å ³çº¦æçä¸è¬ä¿¡æ¯ã
ALL_CONS_COLUMNS è§å¾åDBA_CONS_COLUMNS è§å¾ä¸USER_CONS_COLUMNSæç¸åçåå®ä¹ã
ALL_CONS_COLUMNS è§å¾è½å¤æ¾ç¤ºç¨æ·å¯ä»¥è®¿é®çææ表ä¸çº¦æçåä¿¡æ¯ï¼èä¸ç®¡ææè æ¯è°ã
DBA_CONS_COLUMNS è§å¾ååºäºæ´ä¸ªæ°æ®åºçå级约æä¿¡æ¯ã
USER_CONS_COLUMNS
user_constraints å user_cons_columns表å¾ä½ç¨åå ¶èç³»
user_constraints: æ¯è¡¨çº¦æçè§å¾,æè¿°çæ¯çº¦æç±»å(constraint_type)æ¯ä»ä¹,å±äºåªäºè¡¨(table_name),å¦æ约æçç±»å为R(å¤é®)çè¯,é£ä¹r_constraint_nameå段åæ¾çå°±æ¯è¢«å¼ç¨ä¸»è¡¨ä¸ç主é®çº¦æåã
user_cons_columns: æ¯è¡¨çº¦æå段çè§å¾,说æ表ä¸çå约æç¸å ³çååä¸äºåªäºçº¦æãè¿äºçº¦ææ主é®çº¦æ,å¤é®çº¦æ,ç´¢å¼çº¦æ.
两è å¯ä»¥éè¿(owner,constraint_name,table_name)å ³è:
select
a.owner å¤é®æ¥æè ,
a.table_name å¤é®è¡¨,
substr(c.column_name,1,127) å¤é®å,
b.owner 主é®æ¥æè ,
b.table_name 主é®è¡¨,
substr(d.column_name,1,127) 主é®å
from
user_constraints a,
user_constraints b,
user_cons_columns c,
user_cons_columns d
where
a.r_constraint_name=b.constraint_name
and a.constraint_type="R"
and b.constraint_type="P"
and a.r_owner=b.owner
and a.constraint_name=c.constraint_name
and b.constraint_name=d.constraint_name
and a.owner=c.owner
and a.table_name=c.table_name
and b.owner=d.owner
and b.table_name=d.table_name
æ°æ®åå ¸è¡¨å说æï¼
desc user_constraints
Name Comments
----------------- ---------------------------------------------------------------------------
OWNER Owner of the table
CONSTRAINT_NAME Name associated with constraint definition
CONSTRAINT_TYPE Type of constraint definition
TABLE_NAME Name associated with table with constraint definition
SEARCH_CONDITION Text of search condition for table check
R_OWNER Owner of table used in referential constraint
R_CONSTRAINT_NAME Name of unique constraint definition for referenced table
DELETE_RULE The delete rule for a referential constraint
STATUS Enforcement status of constraint - ENABLED or DISABLED
DEFERRABLE Is the constraint deferrable - DEFERRABLE or NOT DEFERRABLE
DEFERRED Is the constraint deferred by default - DEFERRED or IMMEDIATE
VALIDATED Was this constraint system validated? - VALIDATED or NOT VALIDATED
GENERATED Was the constraint name system generated? - GENERATED NAME or USER NAME
BAD Creating this constraint should give ORA-02436. Rewrite it before 2000 AD.
RELY If set, this flag will be used in optimizer
LAST_CHANGE The date when this column was last enabled or disabled
INDEX_OWNER The owner of the index used by the constraint
INDEX_NAME The index used by the constraint
INVALID
VIEW_RELATED
desc user_cons_columns;
Name Comments
--------------- -------------- -------- ------- ------------------------------------------------------------------------------------------------
OWNER Owner of the constraint definition
CONSTRAINT_NAME Name associated with the constraint definition
TABLE_NAME Name associated with table with constraint definition
COLUMN_NAME Name associated with column or attribute of object column specified in the constraint definition
POSITION Original position of column or attribute in definition
ORACLEçç´¢å¼å约æ详解æ°æ®åº
Oracleç约æ
* å¦ææ个约æåªä½ç¨äºåç¬çå段ï¼å³å¯ä»¥å¨å段级å®ä¹çº¦æï¼ä¹å¯ä»¥å¨è¡¨çº§å®ä¹çº¦æï¼ä½å¦ææ个约æä½ç¨äºå¤ä¸ªå段ï¼
å¿ é¡»å¨è¡¨çº§å®ä¹çº¦æ
* å¨å®ä¹çº¦ææ¶å¯ä»¥éè¿CONSTRAINTå ³é®å为约æå½åï¼å¦æ没ææå®ï¼ORACLEå°èªå¨ä¸ºçº¦æ建ç«é»è®¤çå称
å®ä¹primary key约æ(å个å段ï¼
create table employees (empno number(5) primary key,...)
æå®çº¦æå
create table employees (empno number(5) constraint emp_pk primary key,...)
å®ä¹primary key约æ(å¤ä¸ªå段,å¨è¡¨çº§å®ä¹çº¦æï¼
create table employees
(empno number(5),
deptno number(3) not null,
constraint emp_pk primary key(empno,deptno)
using index tablespace indx
storage (initial 64K
next 64K
)
)
ORACLEèªå¨ä¼ä¸ºå ·æPRIMARY KEY约æçå段(主ç å段)建ç«ä¸ä¸ªå¯ä¸ç´¢å¼åä¸ä¸ªNOT NULL约æ,å®ä¹PRIMARY KEY约ææ¶å¯ä»¥ä¸ºå®çç´¢å¼
æå®åå¨ä½ç½®ååå¨åæ°
alter table employees add primary key (empno)
alter table employees add constraint emp_pk primary key (empno)
alter table employees add constraint emp_pk primary key (empno,deptno)
not null约æ(åªè½å¨å段级å®ä¹NOT NULL约æ,å¨åä¸ä¸ªè¡¨ä¸å¯ä»¥å®ä¹å¤ä¸ªNOT NULL约æ)
alter table employees modify deptno not null/null
unique约æ
create table employees
( empno number(5),
ename varchar2(15),
phone varchar2(15),
email varchar2(30) unique,
deptno number(3) not null,
constraint emp_ename_phone_uk unique (ename,phone)
)
alter table employees
add constraint emp_uk unique(ename,phone)
using index tablespace indx
å®ä¹äºUNIQUE约æçå段ä¸ä¸è½å å«éå¤值ï¼å¯ä»¥ä¸ºä¸ä¸ªæå¤ä¸ªå段å®ä¹UNIQUE约æ,å æ¤ï¼UNIQUEå³å¯ä»¥å¨å段级ä¹å¯ä»¥å¨è¡¨çº§å®ä¹ï¼
å¨UNIQUED约æçå段ä¸å¯ä»¥å å«ç©º值.
foreign key约æ
* å®ä¹ä¸ºFOREIGN KEY约æçå段ä¸åªè½å å«ç¸åºçå ¶å®è¡¨ä¸çå¼ç¨ç å段ç值æè NULL值
* å¯ä»¥ä¸ºä¸ä¸ªæè å¤ä¸ªå段çç»åå®ä¹FOREIGN KEY约æ
* å®ä¹äºFOREIGN KEY约æçå¤é¨ç å段åç¸åºçå¼ç¨ç å段å¯ä»¥åå¨äºåä¸ä¸ªè¡¨ä¸ï¼è¿ç§æ åµç§°ä¸º"èªå¼ç¨"
* 对åä¸ä¸ªå段å¯ä»¥åæ¶å®ä¹FOREIGN KEY约æåNOT NULL约æ
å®ä¹äºFOREIGN KEY约æçå段称为"å¤é¨ç å段",被FORGIEN KEY约æå¼ç¨çå段称为"å¼ç¨ç å段",å¼ç¨ç å¿ é¡»æ¯ä¸»ç æå¯ä¸ç ,å å«å¤é¨ç ç表称为å表ï¼
å å«å¼ç¨ç ç表称为ç¶è¡¨.
A:
create table employees
(.....,
deptno number(3) NOT NULL,
constraint emp_deptno_fk foreign key (deptno)
references dept (deptno)
)
å¦æå表ä¸çå¤é¨ç ä¸ä¸»è¡¨ä¸çå¼ç¨ç å ·æç¸åçå称ï¼å¯ä»¥åæ:
B:
create table employees
(.....,
deptno number(3) NOT NULL
constraint emp_deptno_fk references dept
)
注æï¼
ä¸é¢çä¾å(B)ä¸not nullåé¢æ²¡æå éå·,å 为è¿ä¸å¥çcontraintæ¯è·å¨é£ä¸ådeptnoåé¢çï¼å±äºåå®ä¹ï¼æ以é½æ éææåãèAä¾ä¸çæ¯è¡¨å®ä¹ï¼éè¦ææé£ä¸åï¼æ以è¦å éå·ï¼ä¸è½å¨ååé¢å®ä¹ï¼è¿å¯ä»¥åæï¼
create table employees
(empno char(4),
deptno char(2) not null constraint emp_deptno_fk references dept,
ename varchar2(10)
)
表å®ä¹contraintçåªè½åå¨æåï¼åç两个ä¾åï¼
create table employees
(empno number(5),
ename varchar2(10),
deptno char(2) not null constraint emp_deptno_fk references dept,
constraint emp_pk primary key(empno,ename)
)
create table employees
( empno number(5),
ename varchar2(15),
phone varchar2(15),
email varchar2(30) unique,
deptno number(3) not null,
constraint emp_pk primary key(empno,ename),
constraint emp_phone_uk unique (phone)
)
æ·»å foreign key约æ(å¤å段ï¼è¡¨çº§ï¼
alter table employees
add constraint emp_jobs_fk foreign key (job,deptno)
references jobs (jobid,deptno)
on delete cascade
æ´æ¹foreign key约æå®ä¹çå¼ç¨è¡ä¸º(delete cascade/delete set null/delete no action), é»è®¤æ¯delete on action
å¼ç¨è¡ä¸º(å½ä¸»è¡¨ä¸ä¸æ¡è®°å½è¢«å é¤æ¶ï¼ç¡®å®å¦ä½å¤çå表ä¸çå¤é¨ç å段ï¼ï¼
delete cascade : å é¤å表ä¸ææçç¸å ³è®°å½
delete set null :å°ææç¸å ³è®°å½çå¤é¨ç å段值设置为NULL
delete no action: ä¸åä»»ä½æä½
å å é¤åæ¥çå¤é®çº¦æ,åæ·»å 约æ
ALTER TABLE employees DROP CONSTRAINT emp_deptno_fk;
ALTER TABLE employees ADD CONSTRAINT emp_deptno_fk FOREIGN KEY(deptno) REFERENCES dept(deptno) ON DELETE CASCADE;
check约æ
* å¨CHECK约æç表达å¼ä¸å¿ é¡»å¼ç¨å°è¡¨ä¸çä¸ä¸ªæå¤ä¸ªå段ï¼å¹¶ä¸è¡¨è¾¾å¼ç计ç®ç»æå¿ é¡»æ¯ä¸ä¸ªå¸å°值
* å¯ä»¥å¨è¡¨çº§æå段级å®ä¹
* 对åä¸ä¸ªå段å¯ä»¥å®ä¹å¤ä¸ªCHECK约æï¼åæ¶ä¹å¯ä»¥å®ä¹NOT NULL约æ
create table employees
(sal number(7,2)
constraint emp_sal_ck1 check (sal > 0)
)
alter table employees
add constraint emp_sal_ck2 check (sal < 20000)
å é¤çº¦æ
alter table dept drop unique (dname,loc) --æå®çº¦æçå®ä¹å 容
alter table dept drop constraint dept_dname_loc_uk --æå®çº¦æå
å é¤çº¦ææ¶ï¼é»è®¤å°åæ¶å é¤çº¦ææ对åºçç´¢å¼ï¼å¦æè¦ä¿çç´¢å¼ï¼ç¨KEEP INDEXå ³é®å
alter table employees drop primary key keep index
å¦æè¦å é¤ç约ææ£å¨è¢«å ¶å®çº¦æå¼ç¨ï¼éè¿ALTER TABLE..DROPè¯å¥ä¸æå®CASCADEå ³é®åè½å¤åæ¶å é¤å¼ç¨å®ç约æ
å©ç¨ä¸é¢çè¯å¥å¨å é¤DEPT表ä¸çPRIMARY KEY约ææ¶ï¼åæ¶å°å é¤å ¶å®è¡¨ä¸å¼ç¨è¿ä¸ªçº¦æçFOREIGN KEY约æ:
alter table dept drop primary key cascade
ç¦ç¨/æ¿æ´»çº¦æ(ç¦ç¨/æ¿æ´»çº¦æä¼å¼èµ·å é¤åé建索å¼çæä½)
alter table employees disable/enable unique email
alter table employees disable/enable constraint emp_ename_pk
alter tabel employees modify constraint emp_pk disable/enable
alter tabel employees modify constraint emp_ename_phone_uk disable/enable
å¦ææFOREIGN KEY约ææ£å¨å¼ç¨UNIQUEæPRIMARY KEY约æï¼åæ æ³ç¦ç¨è¿äºUNIQUEæPRIMARY KEY约æï¼
è¿æ¶å¯ä»¥å ç¦ç¨F