Summary of oracle constraints (not null/unique/primary key/foreign key/check), oracleunique

Summary of oracle constraints (not null/unique/primary key/foreign key/check), oracleunique

Constraints: restrict the column attributes and fields of the created table. Such as: not null/unique/primary key/foreign key/check

Scope:
① Column-level constraints can only apply to one column
② Table-level constraints can be applied to multiple columns (of course, table-level constraints can also be applied to one column)
Definition method: the column constraint must follow the column definition. The Table constraint is not defined together with the column but separately.

--Not null: the constraint is NOT empty and can only be defined at the column level.

Create table employees (employee_id NUMBER (6), -- <span style = "color: # FF0000;"> system name </span> last_name VARCHAR2 (25) <span style = "color: # FF0000;"> not null </span>, salary NUMBER (8, 2), commission_pct NUMBER (2, 2), -- <span style = "color: # FF0000; "> custom name </span> hire_date DATE <span style =" color: # FF0000; "> CONSTRAINT emp_hire_date_nn not null </span>,

You can view the constraints of this table in My objects-Tables-employees-Check constraints of PL/SQLDEV.

--UNIQUE: UNIQUE constraint, which can be defined at the table or column level.

Create table employees (employee_id NUMBER (6), -- <span style = "color: # FF0000;"> column-level, system naming unique constraint </span> last_name VARCHAR2 (25) <span style = "color: # FF0000;"> UNIQUE </span>, email VARCHAR2 (25), salary NUMBER (8, 2), commission_pct NUMBER (2, 2 ), hire_date date not null ,... -- <span style = "color: # FF0000;"> table-level, Unique User-Defined naming constraints </span> CONSTRAINT <span style = "color: # FF0000; "> emp_email_uk </span> UNIQUE (email ));

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

    CREATE TABLE   departments(          department_id        NUMBER(4),          department_name      VARCHAR2(30)  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: Specifies the foreign key at the table level as the column in the child table.

--REFERENCES: columns marked in the parent table

--On delete cascade: When the columns in the parent table are deleted, the corresponding columns in the child table are also deleted.

--On delete set null (cascade NULL): The corresponding columns in the subtable are 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: defines the conditions that each row must meet

..., salaryNUMBER(2)     CONSTRAINT emp_salary_min              CHECK (salary > 0),...

1. How to define constraints-add constraints for corresponding attributes while creating a 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 constraint </span> constraint emp1_dept_id_fk foreign key (dept_id) <span style = "color: # FF0000;"> references </span> parameters (department_id) on delete cascade)

1.2 only not null can use column-level constraints. You can use either of the other constraints.

2. Constraints for adding and deleting tables -- after creating a table, you can only add and delete tables, but cannot modify them.
2.1 Add

    alter table emp1    add constaint emp1_sal_ck check(salary > 0)

2.1.1 for not null, you do not need to 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 invalidate a constraint: the constraint still exists in the table, but does not work.
Alter table emp1
Disable constraint emp1_email_uk;

2.4 activate a constraint: the constraint is binding upon activation.

    alter table emp1    enable constraint emp1_email_uk;

Why Does oracle Add a not null primary key after the primary key when defining the primary key?

It is true that primary key is equal to unique key plus not null, but in the end, primary key is only a special key in index, however, there is no rule that the existence of primary cannot be anything else. Is an idea of preemptible, and adding not null is not an error.

In SQL server 2005, the unique, foreign key, and primry key constraints, check

Qnique unique constraint
Foreign key constraint of foreign key
Primry key primary key constraint