Oracle database table constraints, views, indexes-this record is an article of my previous Weibo blog

Source: Internet
Author: User
Tags create index dba dname

I. Common operation of Oracle database create a constraint when creating a table
1. Add constraints when creating a table
----constraints are rules that define what data in a table should follow or conditions that meet
----constraints are built on columns, so that there is a constraint between a column or columns of data
----Constraints Create a constraint at the same time as the table you create, or create a constraint after you create the table
----constraints are divided into table-level and column-level constraints, defined in different ways, but with the same effect
----Column-level constraints: column-name data type "default Defaults" "Column-level constraint 1 column-level constraint 2 ..."
----Table-level constraint: Constraint constraint name constraint type (column name)
----Table-level constraints can customize the name of the constraint, and the name of the column-level constraint is automatically assigned by the system SYS_CN
----If you have data constraints between multiple columns, you can only use table-level constraints to define
----NOT NULL a non-null constraint can only define column levels and cannot define table-level constraints
/*
PRIMARY KEY constraint--primary key
FOREIGN KEY constraint--foreign key
Non-null constraint--not NULL
Unique constraint--unique
Check Constraint--check
2. Column-level constraints and table-level constraints
Column-level constraint for----foreign key: References table name (column name)
Table-level constraint for----FOREIGN key: Constraint constraint name foreign key (column name) references table name (column name)
Testdeptno number (3) Constraint ADSFA foreign key (Testdeptno) References Dept (DEPTNO)
---How to create or delete a constraint after you create the table:
----ALTER TABLE name modify column name not NULL; ----Add a non-empty constraint
----ALTER TABLE name ADD CONSTRAINT constraint name constraint type (column name);----Add additional constraints
----ALTER TABLE name modify column name null;-----Delete non-null constraint
----ALTER TABLE name DROP CONSTRAINT constraint name;----Remove additional constraints
Example:
Student table:
Column name data type
ST_ID (primary key) number (8)
St_name (non-empty) varchar2 (14)
St_age (over 18 yrs) Number (2)
St_phno (cannot repeat) number (11)
St_date (default current system time) date
St_gender (non-empty, can only be male or female, default is male) char (2)
St_grade (foreign key, associated class table primary key) number (4)
CREATE TABLE Student
(
ST_ID Number (8) primary key,
St_name VARCHAR2 (+) NOT NULL,
St_age Number (2) check (st_age>=18),
St_phone number (one) unique,
St_date Date Default Sysdate,
St_gender char (2) Default ' m ' NOT null check (St_gender in (' m ', ' F ')),
St_grade Number (4) references grade (GRADE_ID)
/*constraint Constraint name foreign key (column name) references table name (column name)
St_grade number (4) Constraint Adsad foreign key (St_grade) references grade (GRADE_ID)

Second, Orcale database Operation continuation View index trigger
1. Create a View
CREATE View Empview as
SELECT * FROM EMP where comm is null;
Prompt when creating view does not have permission to operate so enter Isqlplus to authorize the Scott user by logging in with the SYS DBA account: Grant Ada to Scott, who authorizes Scott to be a DBA;
Grant CREATE view to Scott;
2. Modify the View
Update Empview set comm=comm+200 where ename= ' Tengjiang '; The basic operation and modification tables are the same;
Note: Modifying data in a view is equivalent to modifying the data in the source table;
3. Delete View
Delete Empview where empno=1001;
4, modify the view structure operation and modify the table structure, see above content, here is not repeat;
5. Joint Inquiry
(1), multi-table query see Cartesian product
Select Conut (e.ename) from EMP e,dept D
where E.deptno=d.deptno amd D.dname= ' Tengjiang ';
(2), sub-query the query results as a child table, and then query in the child table
Select COUNT (ename) from the EMP where deptno= (select Deptno from dept where Dname= ' SALES ');
(3), multi-table query and sub-table query differences:
A, the number of queries: Multi-table query query more than the number of queries:
Multi-table query: M*n times
Sub-table query: M+n times
(4), create view in query
A. Create a view:
CREATE View emp_dept as
Select E.*,d.deptno dno,d.dname,d.loc from emp e,dept D
where E.deptno=d.deptno;
Creates a emp_dept view: Links the EMP table and the Dept table;
b, then query in the view
Select COUNT (ename) from emp_dept where dname= ' sales '; the number of people with the department name ' Sales ' is counted;
6, create the index;
----index, which is a directory built on a column of data, can improve query efficiency
----A column that is frequently used as a query condition, creating an index can greatly improve query efficiency
The----index is divided into two parts: rowID and Key values
----ROWID is the physical address of each row of data storage, and the key value is the value of the specific data

There are two main types of----indexes: B-Tree index and bitmap index
---(1), B-tree index: Based on binary tree structure. The B-Tree index structure has 3 basic components: a root structure, a branch node, and a leaf node.
---Where the root node is at the top of the index structure, and the leaf node is at the lowest end of the index structure, and the middle is a branch node.
----B-Tree index is the default index type in Oracle and is a tree-like directory
----CREATE index name on table name (column name);
CREATE INDEX Index_ename on EMP (ename);
----Confirm that the index creation can be viewed here
SELECT * from User_indexes;
----indexes are created, indexes automatically help query statements to improve efficiency when querying data using related columns
---The index is automatically updated when the data in the table changes, no manual update maintenance is required
---When a large amount of data changes, the index produces some fragmented files that occupy space and affect efficiency
---can periodically rebuild the index to clean up fragmented files
(2), bitmap index
----B-Tree index is suitable for a large number of different data in a column
----when there is a large amount of duplicate data in a column, you should use a bitmap index
----Bitmap indexes are not suitable for columns with too frequent data changes
----Create bitmap index index name on table name (column name)
Create bitmap index Index_deptno on EMP (DEPTNO);
7. Trigger
----trigger trigger, actions bound to certain operations are not actively implemented,
----But when the condition is met, the operation is synchronized when the bound action is triggered.
/*
Create (or replace) trigger trigger name
Before/after
Insert or UPDATE or delete
(of column) on table
(For each row)
(When (condition range))
Begin
Trigger action (INSERT or update or delete);
End

Before and after define whether the trigger time is before or after the action
Insert or update or delete defines the action that is triggered
(of column) on table: defines which column of the table is to be triggered when the action is
For each row: Defines whether each row of data is triggered once
When: A column in the middle as a condition must be qualified with old or new, indicating
Pre-modified or modified columns
*/

-----First create a table to store the corresponding record information
CREATE TABLE Record_dept30
(
ename VARCHAR2 (20),
Old_job varchar2 (20),
New_job varchar2 (20),
Rec Date
);
SELECT * FROM Record_dept30

Create Trigger Tri1
After
Update
of job on EMP
For each row
When (old.deptno=30)
Begin
INSERT INTO Record_dept30
VALUES (: old.ename,:old.job,:new.job,sysdate);
End

----in the BEGIN section if you want to invoke data from a table, use: Old and: New to qualify

8. Block operation
----do not allow changes to the manager's payroll
/*
Trigger time: Before, after
Trigger action: UPDATE, DELETE, insert
Trigger object: The SAL column in the EMP table
Trigger Range: Position is manager
*/
/*
Block operation using Raise_application_error (as an ID, error hint) method to force an error to be committed
Error ID is custom, recommended use number between -20000~29999, error prompt custom text
*/
Create or Repaley trigger Tri2
Before
Update
of Sal on EMP
For each row
When (old.job= ' MANAGER ')
Begin
Raise_application_error ( -20001, ' Ddssfadfadsfas ');

Oracle database table constraints, views, indexes-this record is an article of my previous Weibo blog

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.