Oracle 表约束

1.创建表：
CREATE TABLE student
(s_id        NUMBER,
 s_name      VARCHAR2(30),
 s_sex       VARCHAR2(10),
 s_age       NUMBER,
 s_birthday  VARCHAR2(15),
 s_phone     VARCHAR2(11),
 s_addr      VARCHAR2(40)
 s_country   VARCHAR2(30),
 s_email     VARCHAR2(30)
 s_sal       NUMBER);
 
 2.添加约束：
 
ALTER TABLE student     --主键约束
ADD CONSTRAINT pk_id PRIMARY KEY(s_id);

ALTER TABLE student      --取值唯一性约束
ADD CONSTRAINT unique_phone UNIQUE(s_phone);

ALTER TABLE student     --非空约束
ADD CONSTRAINT null_id CHECK(s_id IS NOT NULL);

ALTER TABLE student       --检查约束
ADD CONSTRAINT chk_sex CHECK(s_sex='male' OR s_sex='female');
或者
ALTER TABLE student
ADD CONSTRAINT chk_sex CHECK (s_sex IN ('meal', 'femeal'));

3.添加新字段后：
SQL> DESC student
Name       Type         Nullable Default Comments 
---------- ------------ -------- ------- -------- 
S_ID       NUMBER                                 
S_NAME     VARCHAR2(30) Y                         
S_SEX      VARCHAR2(10) Y                         
S_AGE      NUMBER       Y                         
S_BIRTHDAY VARCHAR2(15) Y                         
S_PHONE    VARCHAR2(11) Y                         
S_ADDR     VARCHAR2(40) Y                         
S_COUNTRY  VARCHAR2(20) Y                         
S_EMAIL    VARCHAR2(30) Y                         
S_SAL      NUMBER       Y      

4.取消s_id字段的非空约束：
ALTER TABLE student
DROP CONSTRAINT null_id;

5.查看表的约束字段和类型：
SQL> SELECT constraint_name,constraint_type FROM user_constraints
  2  WHERE table_name='student';    --   table_name='student';
 
CONSTRAINT_NAME                CONSTRAINT_TYPE
------------------------------ ---------------
 
SQL> COMMIT;
 
Commit complete
 
SQL> SELECT constraint_name, constraint_type FROM user_constraints
  2  WHERE table_name='STUDENT';         -- table_name='STUDENT';
 
CONSTRAINT_NAME                CONSTRAINT_TYPE
------------------------------ ---------------
PK_ID                          P
UNIQUE_PHONE                   U
CHK_SEX                        C
UNIQUE_MAIL                    U
CHK_SAL                        C

--提示，在条件语句WHERE中，表明必须大写。
SQL> SELECT constraint_name, constraint_type FROM user_constraints
  2  WHERE table_name='STUDENT'
  3  ORDER BY constraint_name ASC;
 
CONSTRAINT_NAME                CONSTRAINT_TYPE
------------------------------ ---------------
CHK_SAL                        C
CHK_SEX                        C
PK_ID                          P
UNIQUE_MAIL                    U
UNIQUE_PHONE                   U