Oracle Constraint Summary (not null/unique/primary key/foreign Key/check)

Constraint (Constraint): A restriction on the column properties, fields of the created table. such as: Not Null/unique/primary key/foreign Key/check

Scope of Action:
① column-level constraints only work on one column
② table-level constraints can function on multiple columns (of course, table-level constraints can also work on a column)
Defined by: The column constraint must follow the definition of the column, and the table constraint is not associated with the column, but is defined separately.

--not null: Not a NULL constraint, only defined at the column level

CREATE TABLE Employees (    employee_id number    (6),    --<span style= "color: #FF0000;" > System naming </span>   last_name      VARCHAR2 <span style= "color: #FF0000;" >not Null</span>,    salary number         (8,2),    commission_pct number (2,2),--<span    style= " Color: #FF0000; " > User custom naming </span>   hire_date      date  <span style= "color: #FF0000;" >constraint Emp_hire_date_nn not null</span>

You can view the constraints of the table in Pl/sqldev's My objects-tables-employees-check constraints.

--unique: Unique constraint that can be defined at the table-level or column-level

CREATE TABLE Employees (    employee_id number      (6),    --<span style= "color: #FF0000;" > column level, system naming unique constraint </span>   last_name        VARCHAR2 <span style= "color: #FF0000;" >unique</span>,    email            VARCHAR2 (+),    salary number           (8,2),    commission_pct   Number (2,2),    hire_date        date not NULL,...      --<span style= "color: #FF0000;" > table level, user-defined named UNIQUE constraint </span>    CONSTRAINT <span style= "color: #FF0000;" >emp_email_uk</span> UNIQUE (email));

--primary key: Primary key, can be defined at the table level or column level

    CREATE TABLE   Departments (          department_id number        (4),          department_name      VARCHAR2 (in)  CONSTRAINT dept_name_nn not NULL,          manager_id number           (6),          location_id number          (4),          CONSTRAINT DEPT_ID_PK <span style= "color: #FF0000;" >primary key</span> (department_id));

--foreign key: foreign key , specifying a column in a child table at the table level

--references: Columns that are marked in the parent table

--on Delete CASCADE (cascade delete): When a column in the parent table is deleted, the corresponding column in the child table is also deleted

--on DELETE SET null (cascade NULL): The corresponding column in the child table is empty

     Constraint emp1_dept_id_fk <span style= "color: #FF0000;" >foreign key</span> (dept_id) <span style= "color: #FF0000;" >references</span> departments (department_id) <span style= "color: #FF0000;" >on DELETE cascade</span>

--check: Define the conditions that each row must meet

..., Salarynumber (2)     CONSTRAINT emp_salary_min              CHECK (Salary > 0),...

1. How to define a constraint---Add a constraint for the corresponding property while creating the table
1.1 Table-level Constraints & column-level constraints

CREATE TABLE EMP1 (        employee_id Number (8),        salary number (8),        --<span style= "color: #FF0000;" > column-level constraints </span>        hire_date date not NULL,        dept_id number (8),        email varchar2 (8) Constraint Emp1_email _uk Unique,        name VARCHAR2 (8) Constaint emp1_name_uu NOT NULL,        first_name VARCHAR2 (8),        --<span style= " Color: #FF0000; " > table-level constraints </span>        constraint EMP1_EMP_ID_PK primary KEY (employee_id),        constraint Emp1_fir_name_uk Unique (first_name),        --<span style= "color: #FF0000;" > FOREIGN KEY constraints </span>       constraint EMP1_DEPT_ID_FK foreign key (dept_id) <span style= "color: #FF0000;" >references</span> departments (department_id) on DELETE CASCADE    )

1.2 Only a column-level constraint can be used for not NULL. Other constraints are available in two ways

2. Adding and removing constraints to a table--after creating the table, you can only add and delete, not modify
2.1 Add

    ALTER TABLE EMP1    add constaint emp1_sal_ck Check (Salary > 0)

2.1.1 for not NULL, without add, you need to use modify:

    ALTER TABLE EMP1    modify (Salary not NULL)

2.2 Delete

    ALTER TABLE EMP1    drop Constaint emp1_sal_ck

2.3 Invalidates a constraint: This constraint also exists in the table, but it does not work
ALTER TABLE EMP1
Disable constraint emp1_email_uk;

2.4 Make a constraint active: After activation, this constraint is binding

    ALTER TABLE EMP1    enable constraint emp1_email_uk;

Oracle Constraint Summary (not null/unique/primary key/foreign Key/check)