SQLite getting started tutorial 3 Constraints

Source: Internet
Author: User

I. Constraints

At the end of the previous article, I mentioned constraints, but I translated it into a qualifier, which is not very accurate. Here I should correct it first and translate it into constraints more appropriately. 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 least 20 years old. If you want to enter a teacher younger than 20 years old, the system reports an error.
Nationality-China by default. 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.
The data constraints mentioned above are greater than, default, cannot be blank, and unique. When we use create table to CREATE a TABLE, we should first describe the constraints of each field column (if any) and then input data into the TABLE, 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:

Copy codeThe Code is as follows:
Myqiao @ ubuntu :~ /My Documents ents/db $ sqlite3 test. db
-- Loading resources from/home/myqiao/. sqliterc
SQLite version 3.7.4
Enter ". help" for instructions
Enter SQL statements terminated with ";"
Sqlite>. tables
Sqlite>

If the. tables command is not returned, the database is empty. If your database contains content that affects the following experiment, 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, which is like every ID card number, employee number, and bank account. In turn, 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 relationship between the two tables is established through this 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.

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 'er machin ');
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, the system prompts me that 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.

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 'er machin ');
Sqlite> insert into Teachers (Name, Country) Values ('Sun wukong', 'Heaven ting ');
Sqlite> SELECT * FROM Teachers;
Id Name Country
----------------------------------
1 Zhang San China
2 Li Si China
3 Wang Erma Zi China
4 Sun Wukong tianting
Sqlite>

Delete the previous Teachers table and recreate it. This time, the Teachers table has an additional Country field and the default value is "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, 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.

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>

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 form of "nothing". You can use the. nullvalue command to change it to another form. For details, see article 1.

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
This constraint is easy to understand. Apart from the primary column, some Columns cannot have duplicate values. Not to mention, directly 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 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.

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>

The Age field must be greater than 22. When the inserted data is less than 22, the system reports an error.

VII. FOREIGN KEY

Now, our database already has the Teachers table. If we create another Students table, each student in the Students table must correspond to the teacher in the Teachers table.

It is very simple. You only need to create a TeacherId field in the Students table and save the Id of the corresponding instructor. In this way, the relationship between the student and the instructor is established.

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

The problem is: We may store the student a TeacherId value that is not in the Teachers table and cannot find this error.

In this case, the TeacherId field in the Students table can be declared as a foreign key, so that its value corresponds 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.

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 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 all in the Teachers table. However, the number 9 is not in the Teachers table and the system saves the data, 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 download the source code version, set each compilation parameter, and then re-compile, in this way, you will get SQLite that supports foreign keys.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.