Ten. SQL--constraints

A successful database must define the data so that they are realistic and complete, and the integrity here is what we call constraints.

Constraint is actually to make the data complete, that is, the stored data is consistent and correct, in order to make our data complete, SQL Server 2005 defines a corresponding check and control data complete set of mechanisms, according to the corresponding database object and scope of the different, can be divided into entity integrity, domain integrity, Refer to complete and user-defined complete 4 kinds, the meaning of these 4 kinds of integrity here I will not say more, specific people can read.
Constraints, when implemented, can be implemented by the following, mainly including primary key,check,unique,default,foreign key.
So let's take a look at how to implement the above 5 constraints in SQL server2005.

One, the establishment of the student database, there are two methods, here we use < new query > is the SQL statement to implement, the statement is as follows:
Create Database Student
After writing, press F5 to execute, the database is ready,

Two, the establishment of two tables, one is the student data sheet (stuinfo), and the Student score table (STUCJ), the SQL statement is as follows:
Stuinfo table:

CREATE TABLE Stuinfo
(
Stuid Char (8) NOT NULL,
Stuname nvarchar (10),
Stusex nvarchar (2),
Stuage tinyint,
STUADDR varchar (50),
Stutel varchar (15),
Stuintro varchar (200)
)

STUCJ table:

CREATE TABLE STUCJ
(
Stuid Char (8) NOT NULL,
Stuname nvarchar (10),
Chinese numeric (4,1),
中文版 Numeric (4,1),
Math Numeric (4,1)
)

Third, primary KEY (PRIMARY KEY constraint) is the only field that can separate all records, and the Stuid (student number) in the two tables above can be used as the primary key, and the definition statement is:

Use student
Go
ALTER TABLE Stuinfo
Add constraint Pri_id1 primary key (Stuid)
ALTER TABLE STUCJ
Add constraint Pri_id2 primary key (Stuid)

Four Check (check) constraints, which are limits on the range of values entered by one and more columns. The Shuage field in the table represents the age of the student, and for this field we can set its scope, and the SQL statement is as follows:

Use student
Go
Alter Table Stuinfo
ADD constraint ch_age Check (stuage>10 and stuage<50)

Once executed, you can add data to the table, at which point our check constraints work.
Five Default constraint, that is, when the user adds data, if the field is not added to the data, the system automatically gives it a default value, the Stusex (Student Sex) field in the table, we can give it a default value, such as male or female, then the definition of the statement is as follows:

Use student
Go
Alter Table Stuinfo
ADD constraint def_stusex default ' man ' for Stusex

Again, when adding data, you do not add stusex values to see the results.

Six Foreign key (foreign key) constraint, refers to the use of and strengthen the relationship between the two tables, when adding, modifying and deleting data, maintain the consistency of the data in the two tables. The Stuid fields in the Stuinfo and STUCJ tables above can be set, and the SQL statements are as follows:

Use student
Go
ALTER TABLE STUCJ
Add constraint Stu_f foreign key (STUID)
References Stuinfo (STUID)

After execution, you can add, delete, and modify data to the two tables and test it.

Seven. Unique (unique) constraint, refers to the data of the field is unique, the field of data can not occur from the complex, but can be empty, and the primary key can not be empty, in fact, the primary key is a uniqueness constraint. Here we have the Stuname (student name) field designed to be unique, but the actual operation, the student name can be from the complex, here we are the hypothesis, is to put this constraint finished. Next we look at the SQL statement:

Use student
Go
ALTER TABLE Stuinfo
Add constraint Un_stu unique (stuname)

Again, after the execution, you can add data to the test to see.
Above, we are one of the introduction of the use of constraints, in fact, when used, can be defined directly in the establishment of the table, but this definition we do not give every constraint named, the operation may not be very convenient. So, let's take a look at this definition, and create a new table Stuinfo1:

Use student
Go
CREATE TABLE Stuinfo1
(
Stuid Char (8) NOT null primary key,
Stuname varchar (ten) Unique,
Stusex char (2) Default ' male ',
Stuage tinyint check (stuage>10 and stuage<40),
Stutel Char (14)
)

The disadvantage of this method is that its constraint name is not very clear, so the deletion must be to the management view to operate, a little trouble. Let's take a look at the SQL statements to remove these constraints:

Use student
Go
ALTER TABLE Stuinfo
Drop constraint Un_stu

We've removed the unique constraint here, and you can try to delete the other constraints.
Finally, I will add the constraint to write a format for you to refer to:
Alter Table Table Name
ADD constraint constraint name constraint type (field name or expression)
The format of the deletion is as follows:
Alter Table Table Name
DROP CONSTRAINT constraint name

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1622673

Ten. SQL--constraints