SQLite introductory tutorial three or more constraints Constraints

Time:2019-4-19

Constraints

At the end of the last essay, I mentioned constraints, but there I translated them into qualifiers, which is not very accurate. Here I should correct them first, and they should be translated into constraints more appropriately. So what are constraints?

When we store data in the database, some data have obvious constraints. For example, in a school’s data sheet about teachers, the field columns may have the following constraints:

Age – at least over 20 years old. If you want to enter a teacher younger than 20 years old, the system will report an error.
Nationality – Default China. The so-called default is that if you do not fill in, the system automatically fills in the default value.
Name – cannot be empty. Everyone has a name.
Employee number – only. This can’t be messy. If the salary is wrong, it’s troublesome.
The constraints mentioned above are greater than, default, not empty, unique, and so on. When we create tables with CREATE TABLE, we should specify the constraints of each field column in advance (if any). When we input data into the table later, the system will automatically check whether the constraints are met or not, and if not, the system will report errors.

Common constraints for SQLite are as follows

NOT NULL – Nonempty
UNIQUE – Unique
PRIMARY KEY – Primary Key
FOREIGN KEY – Foreign Key
CHECK – Conditional Check
DEFAULT – Default

PRIMARY KEY

Let’s go into the SQLite command line environment and build a test. DB database for experiments, as follows

Copy codeThe code is as follows:
[email protected]:~/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> 

Running the. tables command did not return, indicating that the database was empty. If your database contains content that affects the following experiments, you can use the DROP TABLE we learned in the last article to delete the affected tables, or use ALTER TABLE… RENAME TO… to rename it.

——————————————————————————–

Now let’s turn right. Let’s talk about the primary key PRIMARY KEY.

First, each record in the data table has a primary key, which is like our ID card number, employee number, bank account number; conversely, each primary key corresponds to a data record. So the primary key must be unique.
Secondly, in general, the primary key is also an index, so it is faster to find records through the primary key.
Thirdly, in relational databases, the primary key of one table can be used as the external key of another table, so that the relationship between the two tables can be established through this key.
Finally, primary keys are usually integers or strings, as long as they are unique. In SQLLite, if the primary key is an integer type, the value of the column can grow automatically.

——————————————————————————–

Now let’s do the experiment.

Copy codeThe code is as follows:
sqlite> 
sqlite> CREATE TABLE Teachers(Id integer PRIMARY KEY,Name text);
sqlite> .tables
Teachers
SQLite > INSERT INTO Teachers (Name) Values (‘Zhang San’);
SQLite > INSERT INTO Teachers (Name) Values (‘Li Si’);
SQLite > INSERT INTO Teachers (Name) Values (‘Wang Ermazi’);
sqlite> SELECT * FROM Teachers;
Id          Name     
———-  ———-
1. Zhang San
2) Li Si
3) Wang Erma
SQLite > INSERT INTO Teachers (Id, Name) Values (2,’Monkey King’);
Error: PRIMARY KEY must be unique
sqlite> 

      
——————————————————————————–

We first created a new Teachers table and set up two field columns, in which the Id field is listed as the primary key column. Then, we insert three pieces of data into it and query it, and feedback everything is normal.

Note: When inserting the first three data, the command does not specify the value of Id. The system assigns the value automatically, and the value increases automatically.

When inserting the fourth data, I gave a clear ID number of 2, because the number of Li Si is already 2, so the system prompted me to make a mistake: the primary key must be unique.

Default DEFAULT

There are some special field columns whose values are basically the same in each record. Only in some cases can it be changed to other values. We can set a default value for such a field column.

Now let’s do the experiment.

Copy codeThe code is 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 (‘Zhang San’);
SQLite > INSERT INTO Teachers (Name) Values (‘Li Si’);
SQLite > INSERT INTO Teachers (Name) Values (‘Wang Ermazi’);
SQLite > INSERT INTO Teachers (Name, Country) Values (‘Monkey King’,’Heaven’);
sqlite> SELECT * FROM Teachers;
Id    Name             Country       
—-  —————  —————
1. Zhang San, China
2. Li Si China \\\\\\\\\\\\\
3. Wang Ermazi, China
4. Monkey King Heaven
sqlite>

Delete the previous Teachers table and recreate it. This time, the Teachers table has an additional Country field, and the default value is set to “China”, then we insert four pieces of data into the Teachers table.

The first three data do not clearly specify the value of Country field, only the fourth data indicates that Sun Wukong’s Country is “Tianting”.

After querying the data, we found that the first three data were filled with default values, and the experiment was successful.

——————————————————————————–
The data display is a bit out of shape. The column width set by the command. width 4 1515 can be viewed through. show. It may be due to Chinese, so it is not aligned.

IV. NOT NULL

There are some fields that we may not know what to fill in for a while, and it does not set the default value. When adding data, we leave such fields blank, and the system thinks that they are NULL values.

But there is another kind of field, which must be filled in with data. If it is not filled in, the system will report an error. Such fields are called NOT NULL non-null fields and need to be declared in advance when defining tables.

Now let’s do the experiment.

Copy codeThe code is 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            23               NULL          
2     Bob              29               NULL          
6     Jhon             36               NULL          
sqlite> INSERT INTO Teachers(Name) Values(‘Mary’);
Error: Teachers.Age may not be NULL
sqlite>

Or delete the old table and create a new one first.

This time, the Teachers table declares a NOT NULL field Age, along with a field City that can be NULL.

The first three data insertions do not specify the value of City, the query can see that all the City fields are empty.

Note: NULL here is only a display of “Nothing” and can be changed to another form by the. nullvalue command, as shown in the first article.

When you insert the fourth data without specifying the value of Age, the system will report an error: Teachers. Age cannot be empty

V. Unique UNIQUE
This constraint is well understood. In addition to the main column, there are some columns that cannot have duplicate values. Not to mention much, just look at the code.

Copy codeThe code is 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 Name column, but the Name column cannot have duplicate values. As you can see, when we insert Bob for the second time, the system will report an error.

6. Conditional Check CHECK

Certain values must satisfy certain conditions before they are allowed to be deposited, which requires the use of this CHECK constraint.

Copy codeThe code is 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>

Age field requirements must be greater than 22, when the inserted data is less than 22, the system will report an error.

VII. FOREIGN KEY

Now, there are Teachers tables in our database. If we build another Students table, we ask every student in the Students table to correspond to a teacher in the Teachers table.

Simply, you just need to create a TeacherId field in the Students table and save the corresponding teacher’s Id number, so that the relationship between students and teachers can be established.

——————————————————————————–

The problem is that we may store a TeacherId value that is not in the Teachers table, and we can’t find the 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 report an error.

Copy codeThe code is 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 is used as a foreign key to correspond to the Id column of the Teachers table.

Question: The first two data inserted are all right, because Id numbers 1 and 3 are in Teachers table, but the number 9 is not in Teachers table. Not only is there no error, but the system is saved. Why?

It is said that the foreign key constraints of SQLite are not turned on by default. If you need this function, you may need to download the source code version, set each compilation parameter, and recompile, so that you can get SQLLite that supports foreign keys.