Keys, indexes, constraints, and their differences

Keys, indexes, constraints, and their differences

This afternoon just fine, put some basic concepts straightened out, archive, save the trouble, hey

A Index

1. What is an index.

An index is a structure that sorts the values of one or more columns in a database table.

In relational database, the index is a kind of database structure related to the table, and it exists in fact. It makes it quicker to manipulate the table's select and so on, which is equivalent to the directory of a book.

For a single table, if we want to find a record of a column that matches a particular value, the first method is a full table search, matches, and then lists all the records that fit, but it consumes a lot of database system time and causes a lot of disk I/O operations; the second is to index in the table, The index values that match the query criteria are then found in the index, and the corresponding records in the table are quickly found by ROWID (equivalent to page numbers) saved in the index.

An index is a separate, physical database structure that is the collection of one or more column values in a table and the corresponding list of logical pointers to the data pages that physically identify those values in the table. Therefore, the index is to consume the database space.

Not all databases use indexes in the same way. As a general rule, you need to create an index on a table only if you frequently query the data in an indexed column. Indexes consume disk space and reduce the speed at which rows are added, deleted, and updated. In most cases, the speed advantage of indexing for data retrieval greatly exceeds its disadvantages. However, if your application updates data very frequently or has limited disk space, you may want to limit the number of indexes.

You can use a single-column as an index, or you can use a multiple-column union as an index.

2. Advantages and disadvantages of indexing

Advantages:

(1) Greatly speed up the retrieval speed of the data;

(2) Create a unique index to guarantee the uniqueness of each row of data in the database table;

(3) Accelerate the connection between table and table;

(4) When you use grouping and sorting clauses for data retrieval, you can significantly reduce the time to group and sort in a query.

Disadvantages:

(1) The index needs to occupy the physical space.

(2) When the data in the table to add, delete and modify the time, the index will also be dynamic maintenance, reduce the data maintenance speed.

Considerations when establishing an index:

(1) Consider the number of indexes that have been created on the table. It's best to avoid having many indexes on a single table

(2) Check the definition of the index that has been created on the table. It is best to avoid overlapping indexes that contain shared columns

(3) Check the number of unique data values in a column and compare the number to the number of rows in the table. For example, if there are 1000w records, a field for sex, only male, female. That means that half of the records are duplicated, so consider whether it's necessary to index them.

3. Some index categories

(1) General index

It is also an index that does not impose any restrictions. Can be understood by the following statement.

CREATE TABLE Zjj_temp_1 (ID number (), first_name char (Ten), Last_Name char (), age number (3), Val number (10,2));

INSERT into zjj_temp_1 values (1, ' Junjie ', ' Zhang ', 25,4000);

SELECT * from Zjj_temp_1;

At this point a record has been inserted into the.

Create INDEX zjj_temp_index_1 on zjj_temp_1 (first_name);

Indexing--------

INSERT into zjj_temp_1 values (1, ' Junjie ', ' Zhang ', 25,4000)

--------insert an identical record again.

SELECT * from Zjj_temp_1;

ok! two records appear, which is the role of this index is to let you find first_name for a particular value of the record faster, that's all.

(2) Unique index

An index that does not allow rows with the same index value, thereby prohibiting duplicate indexes or key values. When the index is created, the system checks for duplicate key values and checks each time the INSERT or UPDATE statement is used to add data.

Continue to analyze examples:

Drop index zjj_temp_index_1; ----Delete the normal index created earlier.

Create unique index zjj_temp_1 on zjj_temp_1 (ID);

----Establish a unique index

The database has an error.

Yes, say find duplicate keywords.

As we can see from the above, there are two records in the Zjj_temp_1 table, and the IDs are all 1.

This is not allowed for a unique index, so nature cannot be created.

Delete from zjj_temp_1 where rownum<2; --Delete a row

Create unique index zjj_temp_1 on zjj_temp_1 (ID);

----continue to create and find this success.

INSERT into zjj_temp_1 values (2, ' Junjie ', ' Zhang ', 25,4000);

----success

INSERT into zjj_temp_1 values (1, ' Kesi ', ' ma ', 25,4000); Failed...

Yes. That's it.

(3) Primary key index

Database tables often have a column or combination of columns whose values uniquely identify each row in the table. This column is called the primary key of the table. The commonality between it and a unique index is that no duplicate records are allowed, except that a unique index is unrestricted null, that is, or can have more than one null value inserted, but the primary key is not nullable.

To continue the execution of the statement:

INSERT into zjj_temp_1 values (null, ' Kesi ', ' ma ', 25,4000); ---success

SELECT * from Zjj_temp_1;

This means that the unique index is allowed to have null values.

Drop index zjj_temp_1; ---delete a unique index

ALTER TABLE ZJJ_TEMP_1 ADD constraint Zjsy_1 primary key (ID); ---Establish a primary key

We can see that the ID has a blank record, so it is not possible to establish a primary key.

Just delete that blank record.

(4) clustered index and non-clustered index

A clustered index is also called a Cluster class index, which is the actual data on disk that is organized to sort by the values of one or more of the specified columns. Because the indexed page pointer of a clustered index points to a data page, using a clustered index to find data is almost always faster than using a nonclustered index. Only one clustered index can be built per table, and a clustered index requires at least the additional space of the table 120% to hold a copy of the table and index the middle page.

Clustering is to find the physical storage location of data according to the code value, so as to quickly retrieve data. The order of the Oracle clustered index is the physical storage order of the data, and the leaf node is the data node. The order of nonclustered indexes is independent of the physical order of the data, and the leaf node is still an index node, except that a pointer points to the corresponding block of data. Because clustered indexes are sorted by index, a table can have at most one clustered index, but multiple columns are available.

A clustered table in Oracle means that two tables have exactly the same field, and that the two tables are often connected by this field in the business, and then a clustered table is established,
Two table common one field, can reduce occupy space, and can increase the connection query speed obviously.

There are practical examples of these two articles, and there is no further discussion here.

The idea of establishing clustered index

1, most tables should have a clustered index or use partitioning to reduce the competition for the end of the table, in a high transaction environment, the last page of the blockade seriously affect the system's throughput.

2. In the clustered index, the data is physically sorted on the data page, and the duplicate values are grouped together, so that in those that contain scope checking (between, <, <=, <>, >=) or queries that use GROUP by or order by, Once a row with the first key value in the range is found, rows with subsequent index values are guaranteed to be physically contiguous without further searching, avoiding a wide range of scans and greatly improving query speed.

3, in a frequent insert operation of the table to establish a clustered index, do not build in a monotonous appreciation of the column (such as identity), otherwise it will often cause blockade conflicts.

4. Do not include frequently modified columns in the clustered index, because the data rows must be moved to a new location after the code value is modified.

5, select the clustered index should be based on the WHERE clause and the type of join operation.

For specific clustered indexes, refer to the following articles:

Http://blog.sina.com.cn/s/blog_607b68cc0100f5jo.html

http://space.itpub.net/9778796/viewspace-660186

Two Key

1. What is called the key

The key (key) in the database, also known as the keyword, is an important concept in the relational model, it is the logical structure, not the physical part of the database.

2. Unique key

A unique key, or a group of columns, in which there is no duplicate record. Can uniquely mark a record.

3. Primary key

belongs to a unique key and is a special unique key. The difference is that the primary key is not nullable.

4. Foreign key

If the public keyword is the primary key in a relationship, then the public keyword is called the foreign key of another relationship. This shows that the foreign key represents the connection between the two relationships. A table that takes the foreign key of another relationship as the primary key is called the primary table, and a table with the other key is called the primary table from the table. Foreign keys are also called foreign keywords. In other words, if a property set in relational schema R is not the primary key of R, but the primary key of another relationship R1, the property set is the foreign key of the relational schema r, which is usually abbreviated to the FK in the database design.

Foreign key in the development of basic use, mainly in the database to ensure the integrity of the data

For a simple example

There are columns in table a C1

There are columns in table B C2

Point the foreign key of the C2 to C1

So when you insert data into table B, the contents of the C2 must be one of the C1

There are a few constraints you need to set up

How to work with the associated data in table B if you delete or modify the fields in table A

Examples are as follows:

CREATE TABLE Z_laopo (ID number (5) Primary Key,name char (), age number (3), Zhiye char (20)); ----Create a wife table

CREATE TABLE Z_nanren

(ID number (5) primary key,

Name Char (20),

Age Number (3),

LAOPO_ID Number (5),

Foreign KEY (laopo_id) references Z_laopo (ID)

); -----Create a man table and qualify laopo_id as a foreign key to the wife table

INSERT into Z_laopo values (1, ' Fengjie ',, ' accontant ');

INSERT into Z_laopo values (2, ' Cangjingkong ', M, ' teacher ');

----Insert a record into the wife's table

INSERT into Z_nanren values (1, ' nanren_1 ', 24, 1)

INSERT into Z_nanren values (2, ' nanren_2 ', 24,2)

INSERT into Z_nanren values (3, ' nanren_3 ', 24, 3)

----Insert 3 records into the men's table

The first two, no problem, the third error:

Because does not exist the wife that ID is 3, also namely laopo_id only then 1,2 between the choice. Unless there is a 3 ID in the Z_laopo table.

5. Parent Key

For 2 tables with foreign key relationships, the table referenced by the foreign key table is called the primary table, while the table with the foreign key is called from the table, in the example above Z_nanren from the table, z_laopo the primary table. LAO_ID is a foreign key, and the ID of the Z_laopo table is the parent key.

Three Constraints

Like the primary key, the only, and so on is actually a constraint.

Look at the statement and you'll understand.

CREATE Table Z_test1 (a char (10)); ---create a table

ALTER TABLE Z_TEST1 ADD constraint Pk_z_test1 primary key (a);

----Add a PRIMARY key

CREATE Table Z_test2 (a char (10)); ---create a table

ALTER TABLE Z_TEST2 add constraint Uq_z_test1 unique (a); ----Add a UNIQUE constraint

Where a PRIMARY key constraint is stricter than a unique constraint and cannot be empty.

Four The difference between keys, indexes, constraints

In general, we see the term "index" and "key" exchange used, but in fact the two are different. An index is a physical structure stored in a database, and the key is purely a logical concept. Keys represent the integrity constraints created to enforce business rules. The confusion of indexes and keys is usually due to the fact that the database uses indexes to enforce integrity constraints.

(1) What is the relationship between the primary key index and the primary key.

A primary key index is an index that is automatically created by the system when a primary key is created, and the primary key requirement does not repeat and is not empty, but how is he judged to be efficient. Of course it is indexed, always full table traversal is not crazy.

So establishing a primary key automatically creates a primary key index.

(2) The difference between a primary key and a unique key is that the unique key can be null and the primary key cannot be

(3) What is the difference between establishing a unique constraint and a unique index?

Similarly, unique indexes are created automatically when a unique constraint is established. Establishing a unique index can be said to be a means of a unique constraint.

Basically, it doesn't make any difference. If you can't understand it, just be as good as it is.

(4) What is the difference between clustered index and nonclustered index?

The top has been made up in detail, and there are two articles in it, not to mention here.

(5) What is the difference between a constraint and a primary key.

Constraints generally have primary key constraints, FOREIGN KEY constraints, unique constraints, and so on.

Primary Key,foreign Key,unique, where the primary KEY constraint is only one constraint.

Actually they are different concepts of things.

Ok, basically sort it out!