Detailed explanation of constraints of Oracle table

Constraint Constraint name
If you do not specify a name for the constraint , that oracle The constraint is automatically named

Table level constraints and column level constraints
Scope of action ：
    ① Column level constraints can only act on one column

    ② Table level constraints can act on multiple columns （ Of course, table level constraints can also act on a column ）

Define the way ： The column constraint must follow the definition of the column , Table constraints do not work with columns , It is defined separately .
Non empty (not null) Constraints can only be defined on columns
NOT NULL Non empty constraint
Can only be defined at the column level :

CREATE TABLE test(
    employee_id    NUMBER(6),
    last_name      VARCHAR2(25) NOT NULL,
    hire_date      DATE CONSTRAINT test_last_name NOT NULL
)

UNIQUE Data uniqueness constraint
Be careful ： Unique constraint , Multiple null values are allowed ：NULL.

It can be defined at table level or column level :

( Pay attention to the separation of commas )

CREATE TABLE test(
    employee_id      NUMBER(6),
    last_name        VARCHAR2(25) UNIQUE,
    email            VARCHAR2(25),
    hire_date        DATE NOT NULL,
    CONSTRAINT test_email_uk UNIQUE(email)
);

PRIMARY KEY Primary key constraint
It can be defined at table level or column level :

(1) Data cannot be empty

(2) Data should be unique

CREATE TABLE test(
    employee_id      NUMBER(6) PRIMARY KEY,
    last_name        VARCHAR2(25) UNIQUE,
    email            VARCHAR2(25),
    salary           NUMBER(8,2),
    commission_pct   NUMBER(2,2),
    hire_date        DATE NOT NULL,
    CONSTRAINT test_email_uk UNIQUE(email)
);

-- Or, CONSTRAINT test_id_pk PRIMARY KEY(department_id)

FOREIGN KEY Foreign key constraints
It can be defined at table level or column level :

Only the primary key or unique key in the parent table can be referenced as a foreign key by other tables

CREATE TABLE test(
    employee_id      NUMBER(6) PRIMARY KEY,
    last_name        VARCHAR2(25) UNIQUE,
    email            VARCHAR2(25),
    salary           NUMBER(8,2),
    department_id    NUMBER(4) ,
    hire_date        DATE NOT NULL,   
    CONSTRAINT test_fk FOREIGN KEY (department_id) REFERENCES departments(department_id),
    CONSTRAINT test_email_uk UNIQUE(email)
);

FOREIGN KEY Keywords for constraints
FOREIGN KEY: Specify the columns in the child table at the table level
REFERENCES: Columns marked in the parent table
ON DELETE CASCADE( cascading deletion ): When the columns in the parent table are deleted , The corresponding columns in the sub table are also deleted
ON DELETE SET NULL( Cascade empty ): The corresponding column in the sub table is empty
1.ON DELETE CASCADE

If you remove departments Primary Key department_id, So as test Foreign key department_id The data in the column is deleted together

2.ON DELETE SET NULL

If you remove departments Primary Key department_id, So as test Foreign key department_id The location is empty

CREATE TABLE test(
    employee_id      NUMBER(6) PRIMARY KEY,
    last_name        VARCHAR2(25) UNIQUE,
    email            VARCHAR2(25),
    salary           NUMBER(8,2),
    department_id    NUMBER(4) ,
    hire_date        DATE NOT NULL,   
    CONSTRAINT test_fk FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE CASCADE    CONSTRAINT test_email_uk UNIQUE(email)
);

CHECK Check each row constraint
Define the conditions that each line must meet

CREATE TABLE test(
    employee_id      NUMBER(6) PRIMARY KEY,
    last_name        VARCHAR2(25) UNIQUE,
    email            VARCHAR2(25),
    salary           NUMBER(8,2),
    CONSTRAINT test_salary_min  CHECK (salary > 0)
);

Syntax for adding constraints
Use ALTER TABLE sentence : Add or remove constraints , But you can't modify the constraint , Valid or invalid constraints ;
Use MODIFY sentence ： add to NOT NULL constraint ;
ADD CONSTRAINT   Adding constraints

ALTER TABLE employees

    ADD CONSTRAINT  emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);

DROP CONSTRAINT   Delete constraints

ALTER TABLE employees DROP CONSTRAINT  emp_manager_fk;

DISABLE CONSTRAINT Invalidate constraints

ALTER TABLE employees DISABLE CONSTRAINT  emp_emp_id_pk;

ENABLE CONSTRAINT Activate constraint
When defining or activating UNIQUE or PRIMARY KEY Constraints are created automatically UNIQUE or PRIMARY KEY Indexes

ALTER TABLE employees ENABLE CONSTRAINT   emp_emp_id_pk;

USER_CONSTRAINTS Query constraints

SELECT  constraint_name, constraint_type,search_condition
FROM  user_constraints
WHERE  table_name = 'EMPLOYEES';

USER_CONS_COLUMNS Query the columns that define constraints

SELECT  constraint_name, column_name
FROM    user_cons_columns
WHERE   table_name = 'EMPLOYEES';

Such as ,

————————————————

Link to the original text ：https://blog.csdn.net/weixin_42193004/article/details/89043148