Database constraints and views

Database constraints and views [SQL] -- constraints --***************************** * ********************************** -- non-empty constraints :( notnull) * ensure that the field value cannot be blank. * compared with other constraints, the field value can only be defined at the field level. The createtableEMPLOYEESNOTNULL (EMPLOYEE _

Database constraints and views [SQL] -- constraints --***************************** * ********************************** -- non-empty constraints :( not null) * Make sure that the field value is not allowed to be blank * compared with other constraints, it is unique and can only be defined at the field level-create table EMPLOYEESNOTNULL (EMPLOYEE _

Database constraints and views

[SQL]

-- Constraint

--*************************************** *************************

-- Non-null constraint: (not null)

* Make sure that the field value cannot be blank.

* Compared with other constraints, it is unique and can only be defined at the field level.

-- Column-Level Definition

Create table EMPLOYEESNOTNULL

(

EMPLOYEE_ID NUMBER (6 ),

FIRST_NAME VARCHAR2 (20) not null, -- column-Level Definition

LAST_NAME VARCHAR2 (25)

)

Www.2cto.com

-- Define external constraints in the table

Create table EMPLOYEESNOTNULL_01

(

EMPLOYEE_ID NUMBER (6 ),

FIRST_NAME VARCHAR2 (20 ),

LAST_NAME VARCHAR2 (25)

)

Alter table EMPLOYEESNOTNULL_01

Modify FIRST_NAME not null

--*************************************** **************************************** ***********

-- UNIQUE)

* The uniqueness constraint ensures that the field or field combination does not contain repeated values.

* The field of the uniqueness constraint condition allows null values (1 or more ).

* Oracle will create a unique index for the uniqueness constraint.

Note: If a field has a value, it must be unique, but the null value can contain multiple

-- Method 1 is defined at the column level

Create table emp_un_01

(

EMPLOYEE_ID NUMBER (6 ),

FIRST_NAME VARCHAR2 (20) unique, -- column-Level Definition

LAST_NAME VARCHAR2 (25)

)

-- Method 2 define constraints at the table level

-- Define the syntax format of the constraint amount at the table level

Type of the name constraint of the constraint (Field 1, Field 2)

* Custom constraints

* Constraint type (unique, primary key)

* (Field 1, Field 2) if multiple fields exist, they are separated by commas (,).

Create table emp_un_02

(

EMPLOYEE_ID NUMBER (6 ),

FIRST_NAME VARCHAR2 (20 ),

LAST_NAME VARCHAR2 (25 ),

Constraint un_emp_un_02 unique (first_name) -- define constraints at the table level

)

Www.2cto.com

-- Method 3: Define external constraints in the table

-- Syntax structure:

Alter table table_name

Type of the name constraint of the add constraint (Field 1, Field 2)

* Custom constraints

* Constraint type (unique, primary key)

* (Field 1, Field 2) if multiple fields exist, they are separated by commas (,).

Create table emp_un_03

(

EMPLOYEE_ID NUMBER (6 ),

FIRST_NAME VARCHAR2 (20 ),

LAST_NAME VARCHAR2 (25)

)

Alter table emp_un_03

Add constraint un_emp_un_03 unique (first_name)

Www.2cto.com

-- Method 4 (Table-Level Definition of union uniqueness)

Create table emp_un_04

(

EMPLOYEE_ID NUMBER (6 ),

FIRST_NAME VARCHAR2 (20 ),

LAST_NAME VARCHAR2 (25 ),

Constraint un_emp_un_04 unique (first_name, LAST_NAME) -- define constraints at the table level

)

-- Method 5 (external definition of the table)

Create table emp_un_05

(

EMPLOYEE_ID NUMBER (6 ),

FIRST_NAME VARCHAR2 (20 ),

LAST_NAME VARCHAR2 (25)

)

Www.2cto.com

Alter table emp_un_05

Add constraint un_emp_un_05 unique (first_name, LAST_NAME)

--*************************************** **************************************** ***********

-- PRIMARY KEY)

* The primary key function is equivalent to a non-empty and unique one.

* Only one primary key is allowed in a table.

* A primary key is a field in the table that uniquely identifies a row of data.

* The primary key field can be a single field or a combination of multiple fields.

* Oracle creates a unique index for the primary key.

-- Method 1 is defined at the column level

Create table emp_pk_01

(

EMPLOYEE_ID NUMBER (6) primary key,

FIRST_NAME VARCHAR2 (20 ),

LAST_NAME VARCHAR2 (25)

)

-- Method 2: Table-Level Definition

Create table emp_pk_02

(

EMPLOYEE_ID NUMBER (6 ),

FIRST_NAME VARCHAR2 (20 ),

LAST_NAME VARCHAR2 (25 ),

Constraint pk_emp_pk_02 primary key (EMPLOYEE_ID)

)

Www.2cto.com

-- Method 3 is defined externally

Create table emp_pk_03

(

EMPLOYEE_ID NUMBER (6 ),

FIRST_NAME VARCHAR2 (20 ),

LAST_NAME VARCHAR2 (25)

)

Alter table emp_pk_03

Add constraint pk_emp_pk_03 primary key (EMPLOYEE_ID)

-- Method 4: Table-Level Definition

-- Account table

Create table account_01

(

Accounid varchar2 (18) primary key, -- account

Balance number () -- balance

)

-- Deposit Info table

Create table inaccount_01

(

Accounid varchar2 (18), -- account

Inbalance number (), -- deposit amount

Indate timestamp, -- deposit time

Constraint pk_inaccount_01 primary key (accounid, indate)

)

Insert into inaccount_01 (accounid, inbalance, indate) values ('201312', 12, sysdate );

Insert into inaccount (accounid, inbalance, indate) values ('000000', 10, sysdate );

Www.2cto.com

-- Method 5: defined externally

Create table account_02

(

Accounid varchar2 (18) primary key, -- account

Balance number () -- balance

)

-- Deposit Info table

Create table inaccount_02

(

Accounid varchar2 (18), -- account

Inbalance number (), -- deposit amount

Indate timestamp -- deposit time

)

Alter table inaccount_02

Add constraint pk_inaccount_02 primary key (accounid, indate)

--*************************************** **************************************** ***

-- Foreign key constraint (foreign key)

* A foreign key is built on two fields in a table or the relationship between two fields in two tables.

* The foreign key ensures the relationship between the two fields:

* The value of the foreign key column of the sub-table must be within the value range of the reference column of the master table or be empty.

* When the primary key value of the master table is referenced by the quilt table, the records of the master table cannot be deleted.

* The foreign key constraints refer to the values of one or more fields in the master table. Normally, the foreign key references
Primary Key or unique key of the primary table

-- Define the syntax format of foreign key constraints at the table level

Name of the constraint foreign key (foreign key field) references table (primary key)

* Custom constraints

* Type of the constraint (foreign key)

* For a references table (Primary Key), the fields in the reference table are generally the primary key of the primary table.

Www.2cto.com

-- Method 1 is defined at the table level

Create table deptfk_01

(

DEPARTMENT_ID NUMBER (4) primary key,

DEPARTMENT_NAME VARCHAR2 (30 ),

MANAGER_ID NUMBER (6 ),

LOCATION_ID NUMBER (4)

)

Create table EMPFK_01

(

EMPLOYEE_ID NUMBER (6 ),

FIRST_NAME VARCHAR2 (20 ),

DEPARTMENT_ID NUMBER (4 ),

Constraint fk_EMPFK_01 foreign key (DEPARTMENT_ID) references deptfk_01
(DEPARTMENT_ID)

)

Www.2cto.com

-- Method 2: External definition of the table

-- Syntax format for defining foreign key constraints externally

Alter table table_name

Add constraint name foreign key (foreign key field) references table (primary key)

* Custom constraints

* Type of the constraint (foreign key)

* For a references table (Primary Key), the fields in the reference table are generally the primary key of the primary table.

Create table deptfk_02

(

DEPARTMENT_ID NUMBER (4) primary key,

DEPARTMENT_NAME VARCHAR2 (30 ),

MANAGER_ID NUMBER (6 ),

LOCATION_ID NUMBER (4)

)

Create table EMPFK_02

(

EMPLOYEE_ID NUMBER (6 ),

FIRST_NAME VARCHAR2 (20 ),

DEPARTMENT_ID NUMBER (4)

)

Alter table EMPFK_02

Add constraint fk_EMPFK_02 foreign key (DEPARTMENT_ID) references deptfk_02
(DEPARTMENT_ID)

-- Method 3 (the primary foreign key acts on two fields in a table)

Create table emp_two

(

EMPLOYEE_ID NUMBER (6) primary key,

FIRST_NAME VARCHAR2 (20 ),

MANAGER_ID NUMBER (6) -- foreign key

)

Www.2cto.com

Alter table emp_two

Add constraint fk_emp_two foreign key (MANAGER_ID) references emp_two
(EMPLOYEE_ID)

--*************************************** **************************************** *****

-- Check Constraints

* Check constraints are special constraints defined by check,

* Every record that is forcibly defined on a field must meet the conditions defined in check.

* Define the condition expression for check in check. The data in the table to be accessed must meet the conditions set in check.

Create table empck

(

EMPLOYEE_ID NUMBER (6) primary key,

FIRST_NAME VARCHAR2 (20 ),

Salary number (6000) -->

)

Alter table empck

Add constraint ck_empck check (salary> 6000)

--*************************************** **************************************** *****

Www.2cto.com

-- Delete Constraints

* Deleting constraints does not affect tables and data.

* Delete the constraint emp_manager_fk.

Alter table employees

Drop constraint emp_manager_fk;

-- Delete ck_empck

Alter table empck

Drop constraint ck_empck

-- Www.2cto.com ************************************** ****************************

-- Application Cases of constraints:

Create table F_ADDRESS

(

Address_id number (6) primary key,

Province_name varchar2 (20 ),

City_name varchar2 (20 ),

District_name varchar2 (20 ),

Street_name varchar2 (20 ),

Street_nbr varchar2 (20 ),

Detail varchar2 (20 ),

Postcode varchar2 (10)

)

Www.2cto.com

Create table f_cust

(

Cust_id number (6) primary key,

Cust_name varchar (50 ),

Address_id number (6 ),

State varchar (10)

)

Alter table f_cust

Add constraint fk_f_cust foreign key (address_id) references F_ADDRESS
(Address_id)

Alter table f_cust

Add constraint ck_f_cust check (state in ('in use', 'voided '))

-- Define the unique cust_name In the constraint f_cust

Alter table f_cust

Add constraint un_f_cust unique (cust_name)

-- Delete Constraints

Alter table f_cust

Drop constraint un_f_cust

Www.2cto.com

--*************************************** **************************************** *******

-- View: -- a query statement on the table for the alias from the SQL statement

-- Syntax:

-- Add the subquery after the create view statement.

CREATE [or replace] VIEW view_name

[(Alias [, alias]...)]

AS subquery

[With read only];

Create or replace view v_emp

As

Select * from employees

-- Query view

Select * from v_emp;

-- View Function

-- * Select statements are complex.

-- * Select statements can be used multiple times in a developed program

-- * Directly use the view select * from v_emp in the program

Create or replace view v_emp

As

Select "EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "EMAIL", "PHONE_NUMBER ",

"HIRE_DATE", "JOB_ID", "SALARY", "COMMISSION_PCT", "MANAGER_ID ",
"DEPARTMENT_ID" from employees

Www.2cto.com

-- Describe the view structure (command line execution)

Desc v_emp

Describe v_emp

-- Create a complex view

Create or replace view v_emp_dept

As

Select d. department_name, min (salary) mins, max (salary) mass, avg (salary) avgs,
Sum (salary) sums, count (salary) counts

From employees e, departments d

Where e. department_id = d. department_id

Group by d. department_name

-- Query view www.2cto.com

Select * from v_emp_dept

-- Insert data to a table through a view

Create or replace view v_dept

As

Select deptno, dname, loc from dept

-- Query view

Select * from v_dept

-- Insert data to the dept table through v_dept View

Insert into v_dept (deptno, dname, loc) values (89, 'xxx', 'ss ')

-- You can disable DML operations on the view by setting the with read only option.

Create or replace view v_dept

As

Select deptno, dname, loc from dept

With read only

-- Delete the view www.2cto.com

-- Deleting a view does not cause data loss, because the view is a query definition based on a database table.

Drop view view_name;

-- Delete the v_dept View

Drop view v_dept

-*************************************** **************************************** ***********