Oracle constraints (1)

Guidance:What isConstraints? A constraint is a table-level forced execution rule. When data in a table is mutually dependent, the data is not deleted.Oracle DatabaseIt is a powerful database system. Data in Oracle databases must be kept confidential. We will introduce the constraints of Oracle databases in the following section.

Oracle has the following types of constraints:

NOT NULL
UNIQUE Key
PRIMARY KEY
FOREIGN KEY
CHECK

Oracle uses SYS_Cn format naming constraints.

Creation constraints:

Create tables simultaneously

Create a table

You can define column-level or table-level constraints.

You can view constraints in a data dictionary table.

Create constraints when creating a table

Create table otl_nictine_grade (id number not null, SEASON_NO NUMBER (4) not null, RECEIPT_NO NUMBER (8) not null,
GRADE VARCHAR2 (10) not null, proportion number (5, 2) not null, weight number (10, 2) not null, value number (12, 2) not
Null, constraint pk_otl_nictine_grade primary key (ID, GRADE), constraint fk_otl_nic_reference_otl_chec foreign
Key (ID) references OTL_CHECK_CHEM (ID ))

Add constraints after creating a table

Alter table (table_name) ADD (CONSTRAINT (foreign key constraint name) foreign key (field name) REFERENCES
Primary_table_name (primary_table_primary_index_field)
SQL> create table emplyees (
2 employee_id number (6 ),
3 last_name varchar2 (25) not null,
4 salary number (8, 2 ),
5 commission_pct number (2, 2 ),
6 hire_date date,
7 constraint emp_hire_date_1 not null,
8 CONSTRAINT dept_dname_uk UNIQUE (emp_name)
9 );
The table has been created.
SQL>
1 select constraint_name, table_name
2 from dba_constraints
3 where table_name = 'empyees'
CONSTRAINT_NAME TABLE_NAME
------------------------------------------------------------
SYS_C003012 EMPLYEES
EMP_HIRE_DATE_1 EMPLYEES

Define the primary key constraint PRIMARY KEY

SQL> CREATE TABLE dept (
2 deptno NUMBER (2 ),
3 dname VARCHAR2 (14 ),
4 loc VARCHAR2 (13 ),
5 CONSTRAINT dept_dname_uk UNIQUE (dname ),
6 CONSTRAINT dept_deptno_pk primary key (deptno ));

Foreign key constraint FOREIGN KEY

SQL> CREATE TABLE emp (
2 empno NUMBER (4 ),
3 ename VARCHAR2 (10) not null,
4 job VARCHAR2 (9 ),
5 mgr NUMBER (4 ),
6 hiredate DATE,
7 sal NUMBER (7,2 ),
8 comm NUMBER (7,2 ),
9 deptno NUMBER (7,2) not null,
10 CONSTRAINT emp_deptno_fk foreign key (deptno)
11 REFERENCES dept (deptno ));

Foreign key constraint keywords

FOREIGN KEY

Which column of the sub-table is defined as a foreign key constraint?

REFERENCES

Indicates the primary table and reference columns.

ON DELETE CASCADE

Delete sub-table related records when deleting a primary Table Record