SQLite getting started tutorial (3) Constraints

I. Constraints At the end of the previous article, I mentioned constraints, However, I have translated it into a qualifier, which is not very accurate. Here I want to correct it first. It should be more appropriate to translate it into a constraint. So what are constraints? When we store data in databases, some data has obvious constraints. For example, for a school's instructor data table, the field column may have the following constraints: Age-at leastGreater20 years old. If you want to enter a teacher younger than 20 years old, the system reports an error. Nationality-DefaultChina. The default value is automatically filled in if you do not enter the default value. Name-Cannot be blank. Everyone has a name. Employee ID-Unique. This cannot be messy. If the salary is wrong, it will be troublesome. As mentioned aboveGreater,Default,Cannot be blank,UniqueAnd so on. When using create table to create a table, we should first describe the constraints of each field column (if any ), When you input data into the table later, the system automatically checks whether the constraints are met. If the constraints are not met, an error is returned.

SQLite common constraints:

• Not null-not empty
• Unique-unique
• Primary Key-primary key
• Foreign key-foreign key
• Check-condition check
• Default-Default

Ii. primary key

We still enter the SQLite command line environment and create a test. DB database for experiment, as shown below:

 




 
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>  

If the. Tables command is not returned, the database is empty. If your database contains content that affects the following experiments,
You can use the drop table we learned in the previous article to delete the affected table,
Or use alter table... Rename to change the name.

The following describes the conversion of primary key.

• First, each record in the data table has a primary key,
  This is like every ID card number, employee number, and bank account;
  In turn, it can be said that each primary key corresponds to a data record.
  Therefore, the primary key must be unique.
• In general, the primary key is also an index, so it is faster to query records through the primary key.
• Third, in a relational database, the primary key of a table can be used as the foreign key of another table,
  In this way, the two tables are connected by the key.
• Finally, the primary key is generally an integer or string, as long as it is unique.
  In SQLite, if the primary key is of the integer type, the value of this column can automatically increase.

Next let's do the experiment.

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 erma');
sqlite> SELECT * FROM Teachers;
Id Name
---------- ----------
1 zhang san 
2 li si
3 Wang Erma
sqlite> INSERT INTO Teachers (Id, Name) Values (2, 'Sun Wukong');
Error: PRIMARY KEY must be unique
sqlite> 

We created a new teachers table and set two field columns. The ID field column is the primary key column.
Then, we insert three pieces of data into the data and query the data to report that everything is normal.

Note: when inserting the first three data items, the command does not explicitly specify the id value. The system automatically assigns a value and the value increases automatically.

When inserting the fourth piece of data, I gave a clear ID number of 2, because the number of Li Si is already 2,
So the system prompts me an error: the primary key must be unique.

3. Default Value

There are some special field columns. In each record, their values are basically the same. It is changed to another value only in some cases. We can set a default value for such a field column.

Next let's do the experiment.

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 erma');
sqlite> INSERT INTO Teachers (Name, Country) Values ('Sun Wukong', 'Heaven')
sqlite> SELECT * FROM Teachers;
Id Name Country
---- --------------- ---------------
1 zhang san China
2 Li Si China
3 Wang Erma  China
4 Sun Wukong Heaven
sqlite> 

Delete the previous teachers table and recreate it. This time, the teachers table has an additional country field,
And set the default value to "China". Then we insert four data records to the teachers table.

None of the first three data items explicitly specify the country field value. Only the fourth data item specifies the Country Value of "Sun Wukong" as "Heaven ".

The data is queried and the default value is found in the first three data items. The experiment is successful.

The data display is a little out of shape. You can view the column width set by the command. Width 4 15 15 through. Show,
It may be because of Chinese characters, so there is no alignment.

4. non-null not null

We may not know what to fill in some fields at the moment, and it does not set the default value,
When adding data, we leave this field empty and the system considers it a null value.

However, there is another type of field that must be filled with data. If it is not filled, the system reports an error.
Such a field is called a not null non-empty field and must be declared before defining the table.

Next let's do the experiment.

 
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> 

Delete the old table and create a new table first.

This time, the teachers table declares a not null field age and a null field city.

No city value is specified for the first three data inserts. The city field is empty.

Note: NULL is only a display of "nothing,
You can use the. nullvalue command to change it to another form. For details, see the first article.

If no age value is specified when the fourth data entry is inserted, the system reports an error: teachers. Age cannot be blank.

5. Unique unique

This constraint is easy to understand. Apart from the primary column, some Columns cannot have duplicate values. Let's just look at it.Code

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 only has the name column, but the name column cannot have duplicate values. We can see that the system reports an error when we insert bob for the second time.

Vi. condition check

Some values must meet certain conditions before they can be saved. This check constraint is required.

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 must be greater than 22. When the inserted data is less than 22, the system reports an error.

VII. Foreign key foreign key

Now, our database already has the teachers table. If we create another students table,
Each student in the students table is required to correspond to a teacher in the teachers table.

It is very simple. You only need to create a teacherid field in the students table to save the ID of the corresponding instructor,
In this way, the relationship between students and teachers is established.

The problem is: We may store the student a teacherid value that is not in the teachers table,
This error cannot be found.

In this case, the teacherid field in the students table can be declared as a foreign key,
Set the value to the ID field in the teachers table.

In this way, once a nonexistent instructor ID is saved in the students table, the system reports an error.

 
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 created here, and teacherid is used as the foreign key to correspond to the ID column of the teachers table.

The problem is that the first two inserted data items are correct, because Id 1 and ID 3 are in the teachers table;
But the number 9 is not in the teachers table. Not only is there no error, but the system also saves it. Why?

It is said that the external key constraint of SQLite is not enabled by default. If you need this function, you may need to downloadSource codeVersion, set each compilation parameter, and re-compile, so that you can get SQLite that supports foreign keys.

// ================================================ ==========================================================