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 studentADD CONSTRAINT chk_sex CHECK (s_sex IN ('meal', 'femeal'));3.添加新欄位後：SQL> DESC studentName       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 studentDROP 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                          PUNIQUE_PHONE                   UCHK_SEX                        CUNIQUE_MAIL                    UCHK_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                        CCHK_SEX                        CPK_ID                          PUNIQUE_MAIL                    UUNIQUE_PHONE                   U