Research on constraints and database object rules and default values

Research on constraints and database object rules and default values

Constraints, rules, default values these are the restrictions that govern the operation of data in a data table. The three of them have a relationship and there is a difference, took two days to study the three of them, summarized as follows.

First let's analyze from a relational perspective:

On constraints, rules, default values

1, constraints, the restriction as the name implies is the meaning of the restriction, in fact, it is the role of the same, it is to be carried out by the deletion of the data to conduct a check to check whether these data meet the conditions we want to constrain. For example: We have all experienced the college entrance examination, which stipulates that candidates are not allowed to take items unrelated to the test which is a constraint. Candidates for the exam may not have entered the examination room because of some accidents, but most of the candidates have taken the exam. Then in the examination room there are two cases of vacancy and non-vacancy, which is the two conditions of our constraints. We continue to analyze, in the case of candidates entering the examination room, the examinee before the answer sheet must be in the test paper and answer cards to fill in their own information, this information must be filled out because they have only identified a candidate, here candidates must fill in the candidate information is what we call the PRIMARY KEY constraint (PRIMARY KEY constraint). Next we use two tables to illustrate the following foreign key constraints:

So what if we use the NOT NULL rule after the field? That is, the gender field in our Examinee information table, the column is not allowed to be empty, so we use the NOT null identifier, but if we do not fill it, our system will use default defaults constraint to help us a value in the sky. Next we look at the check constraint, which is to use conditional expressions to qualify the data you want to fill, as follows:

[SQL] view plaincopy

Create Table Payroll (

Name Char (Ten) primary key,

Department char () not Null,

Salary int NOT NULL check (salary>1000 and salary<4000)

)

A table has been created with three fields in the table, name, department, salary, and constraints are set in the Salary field. The table is as follows:

The following two non-conforming conditions are incorrect when the data is filled in the table.

There are two types of constraints: field-level constraints and table-level constraints. Field-level constraints are only one of the columns, table-level constraints are constrained multiple columns, their two difference is not big, we can understand the table-level as a field-level, that is: The constrained multi-column can be considered bundled into a column, the bound field of two records are not repeatable, such as:

2, rules, the rules as the name implies is the meaning of the system, in the table operation of its role and check constraints are similar, but there is only one rule in a table, can have multiple check constraints, such as:

[SQL] view plaincopy

Create Table Salary (

Name Char (Ten) primary key,

1. Position char () not Null,

Salary int NOT NULL

)

Create Rule Ru_salary

As @salary >1000 and @salary <4000

Go

Exec sp_bindrule ' ru_salary ', ' salary. Salary '

2. Go

The table illustrates the following:

3, the default value, the use of the same rule, its role is similar to the default constraint, that is, when inserting data in a table, if no value is specified, the default value automatically specifies the data value.

Next we analyze the three in the syntax, look at the picture:

1, the constraint, the creation method roughly has three kinds:

[SQL] view plaincopy

1. --Method One: Create a constraint in the same way that you create a table

CREATE Table Table name (

Column name data type,

......

<span style= "color: #009900;" > CONSTRAINT constraint name PRIMARY key (column name),--PRIMARY KEY constraint

CONSTRAINT constraint name unique (column name),--Unique constraint

CONSTRAINT Constraint name FOREIGN key (column name) REFERENCES table name (column name)--FOREIGN KEY constraint

CONSTRAINT Constraint name check (check condition)--check constraint

</span>);

--Method Two: Define constraints directly when creating a table

CREATE Table Table name (

<span style= "color: #009900;" > Column name data type not null,--non-null constraint

Column name data type not NULL PRIMARY key,--PRIMARY KEY constraint

Column name data type not NULL unique,--unique constraint

Column name data type [not NULL] REFERENCES table name (column name),--FOREIGN KEY constraint

Column name data type [not NULL] Check (check condition)--check constraints </span>

）

--Method Three: After the table is created, the table is changed to define

<span style= "color: #009900;" > ALTER TABLE name ALTER COLUMN name set not NULL;

ALTER table name ADD PRIMARY KEY (column name 1, column name 2 ...);

ALTER table name ADD UNIQUE (column name 1, column name 2,......);

ALTER table name ADD FOREIGN KEY (column name) REFERENCES table Name 2 (column name 2);

ALTER Table name ADD check (check condition);

</span>

The syntax for deleting a constraint is: ALTER table table name drop CONSTRAINT constraint name

2, rules. The application of the rule, first created but only created, cannot be applied to bind the rule to a field or custom data type in order to work with sp_bindrule.

[SQL] view plaincopy

--Create

<span style= "color: #009900;" > Create rule name as conditional expression

</span>

--binding

<span style= "color: #009900;" > Exec sp_bindrule ' rule name ', ' Table name '. Field name ' | ' Data type '

</span>--Unbind

<span style= "color: #009900;" > sp_unbindrule ' table name. Field name ' | ' Data type '

</span>

--delete rule

<span style= "color: #009900;" >drop RULE {rule_name} [,... N]

</span>

3, the default value of the syntax and rules are similar, simply change the rule in the rules to the keyword default can

Constraints vs. database object rules, default values (GO)