A little bit of database-----Day 8 indexes and constraints

A little bit of database-----Day 8 indexes and constraints

----Reprint Please specify the Source: Coder-pig

1. Index

The index in this SQL, as we mentioned earlier, uses the index to improve the retrieval speed of the data.

And the index is more complex, some concepts, such as table organization, heap, data, data, B-tree, etc., there are two

Types of indexes: "Clustered index" and "nonclustered index" and so on, because this article is a few beginners, but also just understand

Some common knowledge of the following databases, here only Create indexes and delete indexes , friends with deeper needs

Can be self-baidu ~

① Creating an index:

The index is for a field, and you need to specify that the index is created on that field when you create the index.

You can also create one for multiple fields indexes, such as:

CREATE INDEX idx_person_name on T_person (FName)

or create indexes for multiple fields

CREATE INDEX idx_person_union on T_person (fname,fage,fsex)

② Deleting an index

The next step is to delete the previously created index, which is simple:

DROP index T_person.idx_person_name;drop index t_person.idx_person_union;

2. Constraints

In general, when creating a database table, a data type is defined for the field, although a field is specified

Can be stored in the data type, but it is not enough, for example, in the entry of personnel information, due to negligence forgot to fill in

Name field, the result is a record with no name, that is, the data in the database is contaminated, in order to avoid

The correct data is saved to the database table, and we need to set some constraints in the database to ensure that the data

correctness , such as the age can not be negative!

① non-null constraint:

is to set a field that is not allowed to be empty, just add it after the field definition: notNULL

For example, create a test table:

CREATE TABLE t_test (FOne INTEGER not NULl)

Next, execute the following SQL to insert a piece of data:

INSERT into T_test (FOne) VALUES (NULL);

execute the above sql:

PS: In addition to insert null value will fail, update = NULL will also fail!

② Unique constraint:

Is that the value in the field can only be unique, just add it after the field definition:unique

1) Single Field

Let's drop the T_test table you created earlier! Then execute the following build statement:

CREATE TABLE t_test (FOne INTEGER unique,ftwo VARCHAR (20))

insert data into the table:

Insert into T_test (fone,ftwo) VALUES (1, ' Test 1 '), insert into t_test (fone,ftwo) VALUES (1, ' Test 2 ');

run the above sql:

2) Composite UNIQUE constraint:

is to have multiple fields at the same time, in the entry of employee information, there may be a duplicate name phenomenon, such as two people named Tom, but they are

Different departments, this time can be used in accordance with the unique constraints, only two of the same, only counted duplicate records!

You can use it at this time:

CONSTRAINT compound UNIQUE constraint name UNIQUE (department field, name field)

When the fields in parentheses are the same, they are duplicates, that is, the values of the fields in parentheses are the same when inserting and updating.

As for the constraint name, you can write anything you want.

③check constraints:

is that Determine if the field meets the criteria before writing the data , if the condition is not met, then this modification to the database will not be successful!

For example, here is a simple example: (Drop the front table first!)

CREATE TABLE sql:

CREATE TABLE t_test (FOne integer,ftwo INTEGER CHECK (ftwo > 0))

Insert sql:

INSERT into T_test (fone,ftwo) VALUES (1,-1)

run the above sql:

Another practical example: Whether it is a mobile phone number, that is, the number of digits needed equals one

Build Table sql:

CREATE TABLE t_test (FOne integer,fnum VARCHAR CHECK (LEN (fnum) =11))

Insert sql:

Insert into T_test (Fone,fnum) VALUES (1, ' 13765653324 ') and insert into T_test (Fone,fnum) VALUES (1, ' 137656533245 ');

Run sql:

④ PRIMARY KEY constraints:

The importance of primary key constraints is needless to say, the previous introduction, is to add the PRIMARY key keyword after the field can!

How to set the primary key at the time of creation, which has been said many times before, does not write, this shows only how to set the primary key for the table you have created

The SQL statements are as follows:

ALTER TABLE t_test ADD PRIMARY KEY (FOne)

⑤ FOREIGN KEY constraint:

As with the primary key, creating a database table in Day2 already shows how to create a foreign key!

Example:

Of course, as with the primary key, we may not initially create a foreign key from a table, but also add a foreign key to the table by using ALTER :

ALTER table name ADD CONSTRAINT FOREIGN KEY constraint name FOREIGN key (column name) REFERENCES Reference foreign key table (column name)

Finally say two words:

The knowledge points about indexes and constraints in SQL are introduced here ~

A little bit of database-----Day 8 indexes and constraints