--指定约束名

CREATE TABLE employees(

employee_id NUMBER(6),

first_name VARCHAR2(20),

job_id VARCHAR2(10) NOT NULL,

CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));

create table employees(

--列级约束

id number(10),

name varchar2(20) constraint emp_name_nn not null,

email varchar2(20),

salary number(10,2) constraint emp_salary_min check (salary > 1500),

department_id number(10),

--表级约束

constraint emp_email_uk unique(email),

constraint emp_id_pk primary key(id),

constraint emp_dept_id_fk foreign key(department_id)

references departments(department_id ) ON DELETE CASCADE

)

salary NUMBER(2) CONSTRAINT emp_salary_min CHECK (salary > 0)

Alter table emp modify(empname varchar2(50) not null);

ALTER TABLE employees

ADD CONSTRAINT emp_email_pk primary(employee_id)

ALTER TABLE employees DROP CONSTRAINT emp_email_uk;

SELECT    constraint_name, constraint_type, search_condition

FROM    user_constraints

WHERE    table_name = 'EMPLOYEES';

CONSTRAINT_NAME             CONSTRAINT_TYPE              SEARCH_CONDITION

--------------            ------------------------     --------------------

EMP_DEPT_FK                           R

EMP_JOB_FK                            R

EMP_MANAGER_FK                        R

EMP_LAST_NAME_NN                      C               "LAST_NAME" IS NOT NULL

EMP_EMAIL_NN                          C               "EMAIL" IS NOT NULL

EMP_HIRE_DATE_NN                      C               "HIRE_DATE" IS NOT NULL

EMP_JOB_NN                            C               "JOB_ID" IS NOT NULL

EMP_SALARY_MIN                        C               salary > 0

EMP_EMAIL_UK                          U

EMP_EMP_ID_PK                         P            

SELECT    constraint_name, column_name

FROM    user_cons_columns

WHERE    table_name = 'EMPLOYEES';

CONSTRAINT_NAME                           COLUMN_NAME

---------------------               ---------------------

EMP_LAST_NAME_NN                          LAST_NAME

EMP_EMAIL_NN                              EMAIL

EMP_HIRE_DATE_NN                          HIRE_DATE

EMP_JOB_NN                                JOB_ID

EMP_SALARY_MIN                            SALARY

EMP_EMAIL_UK                              EMAIL

EMP_EMP_ID_PK                             EMPLOYEE_ID

EMP_DEPT_FK                               DEPARTMENT_ID

EMP_JOB_FK                                JOB_ID

EMP_MANAGER_FK                            MANAGER_ID