SQLite Introductory Tutorials three many constraints Constraints_sqlite

I. Constraint Constraints

At the end of the last essay, I mentioned the constraint, but there I translated it as a qualifier, not too accurate, here to correct it, it should be translated into a more appropriate constraint. So what is a constraint?

When we store data in a database, there are some obvious constraints on the data. such as a school data table about teachers, where the field columns may have the following constraints:

Age-at least more than 20 years old. If you want to input a teacher less than 20 years old, the system will complain
Nationality-default China. The so-called default, is that if you do not fill out, the system automatically fill in the default value
Name-cannot be empty. Everyone has a name.
Employee number-Unique. This is not a mess, the wages are wrong on the trouble
The above mentioned above, default, cannot be empty, unique and so on, is the data constraints. We use CREATE table when creating tables, should be the constraints of each field column in advance (if any), and then enter data in the form, the system will automatically check whether we meet the constraints, if not satisfied with the system will be an error.

SQLite common constraints are as follows

Not null-non-empty
Unique-Unique
PRIMARY Key-PRIMARY key
FOREIGN Key-FOREIGN key
Check-condition Check
Default-Defaults

Second, the primary key PRIMARY keys

Let's go into the SQLite command line environment and build a test.db database to do the experiment, as follows

Copy Code code as follows:

Myqiao@ubuntu:~/my documents/db$ sqlite3 test.db
--Loading Resources From/home/myqiao/.sqliterc
SQLite version 3.7.4
Enter '. Help ' for instructions
Enter SQL statements terminated with a ";"
Sqlite>. Tables
Sqlite>

The run. Tables command does not return, indicating that the database is empty. If you have content in your database that affects the following experiments, you can use the drop table of our last study to delete the affected table, or use ALTER table ... RENAME to ... To change the name.

--------------------------------------------------------------------------------

The following statement is turning, we say the primary key PRIMARY keys.

First, each record in the datasheet has a primary key, which is like each of our ID numbers, employee numbers, bank accounts, and conversely, each primary key corresponds to a data record. Therefore, the primary key must be unique.
Second, in general, the primary key is also an index, so the primary key to find records faster.
Third, in a relational database, a table's primary key can act as a foreign key to another table, so that the relationship between the two tables is established through this key.
Finally, the primary key is usually an integer or a string, as long as the only guarantee is the only line. In SQLite, if the primary key is an integer type, the value of the column can grow automatically.

--------------------------------------------------------------------------------

Now let's do the experiment.

Copy Code code as follows:

Sqlite>
Sqlite> CREATE TABLE Teachers (Id integer PRIMARY key,name text);
Sqlite>. Tables
Teachers
Sqlite> INSERT into Teachers (Name) Values (' John ');
Sqlite> INSERT into Teachers (Name) Values (' Dick ');
Sqlite> INSERT into Teachers (Name) Values (' King II-Flaxseed ');
Sqlite> SELECT * from Teachers;
Id Name
----------  ----------
1 sheets of three
2 Dick
3 King and two hemp
Sqlite> INSERT into Teachers (id,name) Values (2, ' Monkey King ');
Error:primary KEY must be unique
Sqlite>

--------------------------------------------------------------------------------

We created a new Teachers table and set up two field columns with the Id field listed as the primary key column. Then, we insert three data into it and query, feedback all normal.

Note: When inserting the first three data, the command does not explicitly indicate the value of the Id, the system is automatically assigned, and the numeric value is automatically increased.

Inserting the fourth data, I gave a clear Id number of 2, because the number of dick is already 2, so the system prompts me to error: The primary key must be unique.

Third, default value defaults

There are special field columns, and in each record, his values are basically the same. We can set a default value for this field column only if it is changed to a different value in some cases.

Now let's do the experiment.

Copy Code code as follows:

Sqlite>
sqlite> DROP TABLE Teachers;
Sqlite>. Tables
Sqlite>
Sqlite> CREATE TABLE Teachers (Id integer PRIMARY key,name text,country text DEFAULT ' China ');
Sqlite>. Tables
Teachers
Sqlite> INSERT into Teachers (Name) Values (' John ');
Sqlite> INSERT into Teachers (Name) Values (' Dick ');
Sqlite> INSERT into Teachers (Name) Values (' King II-Flaxseed ');
Sqlite> INSERT into Teachers (name,country) Values (' Monkey King ', ' heaven ');
Sqlite> SELECT * from Teachers;
Id Name Country
----  ---------------  ---------------
1 Zhang three China
2 Dick China
3 Wang-Pock China
4 Monkey King Heaven
Sqlite>

First delete the previous Teachers table and recreate it. This time the Teachers table has one more Country field, and the default value is "China", and then we insert four data into the Teachers table.

The first three data did not specify the value of the Country field, only the fourth data indicated that "Monkey King" Country for "heaven".

Query data, found that the first three data are filled with the default value, the experiment was successful.

--------------------------------------------------------------------------------
The data shows a bit out of shape, the command. Width 4 15 15 The width of the column can be passed. Show view, probably because of Chinese reasons, so there is no alignment.

Four, non-null not NULL

There are some fields we may not know what to fill in, and it does not set the default value, when adding data, we leave such a field blank, the system that he is a null value.

But there is another type of field, must be filled with data, if not filled, the system will be an error. Such a field is called a NOT null non-empty field and needs to be declared in advance when the table is defined.

Now let's do the experiment.

Copy Code code as follows:

Sqlite>
sqlite> DROP TABLE Teachers;
Sqlite>. Tables
Sqlite>
Sqlite> CREATE TABLE Teachers (Id integer PRIMARY key,name text,age integer not null,city text);
Sqlite>. Tables
Teachers
Sqlite> INSERT into Teachers (name,age) Values (' Alice ', 23);
Sqlite> INSERT into Teachers (name,age) Values (' Bob ', 29);
Sqlite> INSERT into Teachers (id,name,age) Values (6, ' Jhon ', 36);
Sqlite> SELECT * from Teachers;
Id Name Age City
----  ---------------  ---------------  ---------------
1 Alice NULL
2 Bob NULL
6 Jhon NULL
Sqlite> INSERT into Teachers (Name) Values (' Mary ');
Error:Teachers.Age May is NULL
Sqlite>

or delete the old table first, create a new table.

This time the Teachers table declares a not NULL field age and also a nullable field city

Insert the first three data does not specify city value, the query can see the City field is all empty

Note: The NULL here is only a display form for "Nothing," which can be changed by the. Nullvalue command to another form, see the first article

When you insert the fourth data without specifying the value of age, the system complains: Teachers.age cannot be empty

V. the only unique
This constraint is well understood, and there are some columns that cannot have duplicate values in addition to the main column. Don't say much, just look at the code

Copy Code code as follows:

Sqlite>
sqlite> DROP TABLE Teachers;
Sqlite>. Tables
Sqlite>
Sqlite> CREATE TABLE Teachers (Id integer PRIMARY key,name text UNIQUE);
Sqlite>. Tables
Teachers
Sqlite> INSERT into Teachers (Name) VALUES (' Alice ');
Sqlite> INSERT into Teachers (Name) VALUES (' Bob ');
Sqlite> INSERT into Teachers (Name) VALUES (' Jane ');
Sqlite> INSERT into Teachers (Name) VALUES (' Bob ');
Error:column Name is not unique
Sqlite>

This time the Teachers table has only the Name column, but the name column cannot have duplicate values. As you can see, when we insert Bob the second time, the system complains.

Vi. Condition Checking Check

Some values must meet certain conditions to allow deposit, which is required to use this CHECK constraint.

Copy Code code as follows:

Sqlite>
sqlite> DROP TABLE Teachers;
Sqlite>. Tables
Sqlite>
Sqlite> CREATE TABLE Teachers (Id integer PRIMARY key,age integer CHECK (age>22));
Sqlite>. Tables
Teachers
Sqlite> INSERT into Teachers (age) VALUES (45);
Sqlite> INSERT into Teachers (age) VALUES (33);
Sqlite> INSERT into Teachers (age) VALUES (23);
Sqlite> INSERT into Teachers (age) VALUES (21);
Error:constraint failed
Sqlite>

The age field requirement must be greater than 22 and the system will complain when the inserted data is less than 22 o'clock.

Seven, foreign key to FOREIGN key

Now that we have a Teachers table in our database, if we build a Students table, we require that every student in the Students table correspond to a teacher in a Teachers table.

Simply create a Teacherid field in the Students table and save the corresponding teacher's Id number so that the student and the teacher have a relationship.

--------------------------------------------------------------------------------

The problem is that we might be able to deposit a Teacherid value in a table that is not in the Teachers, and not find this error.

In this case, you can declare the Teacherid field in the Students table as a foreign key so that its value corresponds to the Id field in the Teachers table.

In this way, once a non-existent teacher Id is stored in the Students table, the system will complain.

Copy Code code as follows:

Sqlite>
Sqlite>. Tables
Teachers
Sqlite> CREATE TABLE Students (Id integer PRIMARY KEY, Teacherid Integer, FOREIGN KEY (Teacherid) REFERENCES Teachers ( ID));
Sqlite>. Tables
Students Teachers
Sqlite> SELECT * from Teachers;
Id Age
----  ---------------
1 40
2 33
3 23
Sqlite> INSERT into Students (Teacherid) VALUES (1);
Sqlite> INSERT into Students (Teacherid) VALUES (3);
Sqlite> INSERT into Students (Teacherid) VALUES (9);
Sqlite> SELECT * from Students;
Id Teacherid
----  ---------------
1 1
2 3
3 9
Sqlite>

The Students table is established here and the Teacherid as a foreign key corresponds to the Id column of the Teachers table.

The problem: the first two data inserted is fine, because Id number 1 and 3 are in the Teachers table, but the number 9 is not in the Teachers table, not only does not have the error, the system also saved in, this is why?

It is said that SQLite's foreign KEY constraint is not turned on by default, and if you need this feature, you may need to download the source version, set each compilation parameter, and recompile, so you get a SQLite that supports the foreign key.