Research on constraints and database object rules and default values

Constraints, rules, and default conditions are defined in the data table. There are also differences between the three, they spent two days to study the three, summarized as follows. First, let's analyze from the perspective of the relationship: ● talking about constraints, rules, default 1. constraints, as the name suggests, are restrictions. In fact, they also serve

Constraints, rules, and default conditions are defined in the data table. There are also differences between the three, they spent two days to study the three, summarized as follows. First, let's analyze from the perspective of the relationship: ● talking about constraints, rules, default 1. constraints, as the name suggests, are restrictions. In fact, they also serve

Constraints, rules, and default values specify the restrictions for data operations in the data table. There are also differences between the three, they spent two days to study the three, summarized as follows.

First, let's analyze the relationship:

● Constraints, rules, and default values

1. constraints, as the name implies, are restrictions. In fact, they also serve the same purpose. They are used to check the data to be added, deleted, modified, and queried, check whether the data meets the conditions we want to restrict. For example, we have all gone through the college entrance examination. The examination requires that you do not bring Items unrelated to the examination. This is a constraint. Candidates for the exam may not be admitted to the exam due to some accidents, but most of them still take the exam. In the test room, there are two scenarios: Vacant Space and non-vacant space, which are two constraints. We continue our analysis. When the examinee enters the test room, the examinee must fill in his/her information on the examination paper and answer sheet before answering the question, this information must be filled in because it uniquely identifies a candidate. Here, the examinee must fill in the examinee information, that is, the primary key constraint we call (primary key keyword constraint ). Next we will use two tables to describe the foreign key constraints:

What if we use Not Null after the field? That is to say, in the gender field in our examinee information table, this column is Not allowed to be blank, so it is marked with Not Null, But if we leave it blank, our system will use the Default value constraints to help us create a value in the sky. Next, let's look at the Check constraint, which uses a conditional expression to limit the data to be filled in, as shown below:

Create table salary (name char (10) primary key, department char (20) Not Null, salary int not null check (salary> 1000 and salary <4000 ))

A table is created. This table has three fields: name, department, and salary, and constraints are set for the salary field. This table is shown in the following figure:

When you fill in the data in the table, the last two non-conforming conditions are incorrect.

There are also two types of constraints: field-level constraints and table-level constraints. Field-level constraints only constrain one column. Table-level constraints constrain multiple columns. The differences between the two are not great. We can understand the table-level constraints as field-level, that is: multiple restricted columns can be regarded as bundled into one column. The two records in the bound fields cannot be repeated, for example:

2. Rules, as the name suggests, refer to rules and regulations. When performing table operations, the role of rules is similar to the check constraints, but a table has only one rule, there are multiple check constraints, such:

Create table salary (name char (10) primary key, position char (20) Not Null, salary int not null) create rule ru_salary As @ salary> 1000 and @ salary <4000 Go Exec sp_bindrule 'ru _ salary ', 'salary. salary 'go

The table is described as follows:

3. The Default value is the same as the rule. It is similar to the Default constraint. It means that if no value is specified during data insertion in the table, the Default value automatically specifies the data value.

Next we will analyze the three in syntax, as shown in the figure:

1. Constraints: there are roughly three creation methods:

-- Method 1: CREATE the constraint create table name (column name data type ,...... CONSTRAINT name primary key (column name), -- primary key constraint name UNIQUE (column name), -- unique constraint name foreign key (column name) REFERENCES table name (column name ), -- foreign key CONSTRAINT name CHECK (Check condition) -- check constraint); -- Method 2: when creating a TABLE, define the constraint create table name directly (column name data type not null, -- non-empty 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 constraint) -- Method 3: after creating a table Alter table to define 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 );

Syntax for deleting constraints: alter table Name drop constraint name

2. Rules. To apply a rule, you must first create a rule but only create a rule that cannot be applied. Use sp_bindrule to bind the rule to a field or a custom data type.

-- Create a Create rule name as condition expression -- bind Exec sp_bindrule 'Rule name' and 'table name. field name' | 'data type' -- unbind sp_unbindrule' table name. field name '| 'data type' -- delete rule drop rule {rule_name }[,... n]

3. The default syntax is similar to the rule. You only need to change the rule in the rule to the keyword default.

The relationship between the three is clear.

The above two export map can not see how to do, Don't worry to download it here: http://163.fm/8S2n28H extraction code: 0lUKbp0z

Dc6THqqH http://163.fm/GktKqwV extraction code