Briefly introduce the constraints, main types and functions of database tables.

First of all, what are database constraints?

Database constraint is a set of mechanisms to ensure data integrity, and there are different tools according to the implementation of different databases. Therefore, when we study constraints, we will see different types of constraints, such as domain integrity, entity integrity, referential integrity and user-defined integrity. But today I'm going to talk about our students who have just come into contact with the database and started from the basics of SQL language.

1, non-empty constraint

Everyone may know this, that is, we are adding the table userinfo and adding not null after the id and name, that is, some columns cannot be set to be empty. I believe everyone understands, so I'll tell you an English passage I read in an English material: not null specification prohibits insertion.

The null value of this property. Any database modification that causes null to be inserted into an attribute declared as not null will generate an error diagnosis.

2. Unique constraint

Unique constraint does not contain null value. You can define a UNIQUE constraint by adding unique directly after the field definition.

1) A table can only create one primary key constraint, but a table can create multiple unique constraints for different columns as needed.

2) The primary key field cannot be empty, only it can be empty.

3) When the primary key constraint is created, the system automatically generates a clustered index, and the unique constraint automatically generates a nonclustered index.

3. check constraint (check clause)

By adding field-level or table-level check constraints to define database tables, specific requirements can be met.

Let me give you an example.

Create table students (

Name varchar( 15) is not empty.

student_id varchar( 15)，

Degree level varchar( 15),

Primary key (student ID),

Check(degree_level in ('Bachelor',' Master',' Doctor'));

Here, we use the check clause to simulate enumeration types by specifying that degree_level must be one of "Bachelor", "Master" and "Doctor";

4. Primary key constraint

In fact, primary key constraint means that only one primary key constraint can be established for a table, which is actually a unique constraint+non-empty constraint. Everyone discussed so many things about "dependence" in class, which is estimated that everyone can understand.

5. Foreign key constraints.

The foreign key constraint is mainly to ensure the referential integrity between the same table or different tables, so the primary key or unique constraint must be referenced, and the user must have the rights of REFERENCES to the application table; A table can have at most 3 1 external key constraints; Foreign key constraints cannot be used in temporary tables; The data types of primary key and foreign key must match exactly. I won't say much about this. I recommend you a website/s/2010-01-04/00481199365.shtml.

System extract ....

Search online and you will know, many ~ ~ ~