Oracle _ constraints constraint and oracleconstraint

Oracle _ constraints constraint and oracleconstraint
Zookeeper

Oracle _ constraints constraint

① What are constraints?
1 constraint is mandatory at the table level
There are five constraints:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
Notes
If no constraint name is specified, Oracle server automatically specifies the constraint name in SYS_Cn format.
Create and modify constraints: when creating a table, \ after creating a table
You can define constraints at the table or column level.
You can view constraints in the data dictionary view.

2. Table-level constraints and column-level constraints
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.
Note: non-null (not null) constraints can only be defined on columns.

3. create table [schema.] table
(Column datatype [DEFAULT expr] [column_constraint],
...
[Table_constraint] [,...]
);

Column level
Column [CONSTRAINT constraint_name] constraint_type,
Table-level
Column ,...
[CONSTRAINT constraint_name] constraint_type
(Column ,...),

② Not null Constraint
Ensure that the column value cannot be blank:
It can only be defined at the column level:

③ UNIQUE constraints
Unique constraint. Multiple NULL values are allowed: NULL.
It can be defined at the table or column level:
Create table employees (
-- Column-level constraints
Employee_id NUMBER (6 ),
Last_name VARCHAR2 (25) UNIQUE,
Email VARCHAR2 (25 ),
Salary NUMBER (8, 2 ),
Commission_pct NUMBER (2, 2 ),
Hire_date date not null,
...
-- Table-level constraints
CONSTRAINT emp_email_uk UNIQUE (email ));

④ Primary key constraint
Primary key constraint: non-empty and unique
It can be defined at the table or column level:
Create table orders ments (
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 primary key (department_id ));
 
⑤ Foreign key constraint
Create table employees (
Employee_id NUMBER (6 ),
Last_name VARCHAR2 (25) not null,
Email VARCHAR2 (25 ),
Salary NUMBER (8, 2 ),
Commission_pct NUMBER (2, 2 ),
Hire_date date not null,
...
Department_id NUMBER (4 ),
CONSTRAINT emp_dept_fk foreign key (department_id)
REFERENCES parameters (department_id ),
CONSTRAINT emp_email_uk UNIQUE (email ));

Foreign key: indicates the column in the child table at the table level.
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.

⑥ CHECK Constraints
Define conditions that must be met for each row
..., Salary NUMBER (2)
CONSTRAINT emp_salary_min
CHECK (salary> 0 ),...
7. syntax for adding Constraints
Use the alter table statement:
Add or delete constraints, but they cannot be modified
Efficient or invalid Constraints
Only the not null constraint must be added using the MODIFY statement.
Alter table table
ADD [CONSTRAINT constraint] type (column );

Constraint Deletion
Alter table employees
Drop constraint emp_manager_fk;

Invalid quota constraint
Use the DISABLE clause in the alter table statement to invalidate the constraint.
Alter table employees
Disable constraint emp_emp_id_pk;

Activation Constraints
ENABLE clause can activate the currently invalid Constraint
Alter table employees
Enable constraint emp_emp_id_pk;

Note: When the UNIQUE or primary key constraint is defined or activated, the system automatically creates a UNIQUE or primary key index.

Query Constraints
Query the data dictionary view USER_CONSTRAINTS
SELECT constraint_name, constraint_type,
Search_condition
FROM user_constraints
WHERE table_name = 'ployees ';

Query columns with definition Constraints
Query the data dictionary view USER_CONS_COLUMNS
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'ployees ';

Oracle view Constraints

Select constraint_name from dba_constraints where owner =... and table_name =...
... The owner and table names of tables in uppercase with single quotes.

A keyword in oracle

Constraint: Constraints
In Oracle, several constraint types are used to constrain the table to ensure its data integrity. For example:
Check Constraint, Not Null Constraint, Primary Key Constraint, References Constraint.
Example: create table student (
STUDENT _ id number (3) CONSTRAINT S_ID CHECK (STUDENT _ ID> 0). Note: CHECK IDs must be greater than zero.
STUDENT _ name char (30) CONSTRAINT S_NAME not null, note: Name cannot be blank
MARKS_COUNT NUMBER (6), constraint student _ prime primary key (STUDENT _ ID) Note: ID is the primary key)