SQL Server 2008 from getting started to mastering--20180629

Constrain PRIMARY KEY constraint (Primary key Constraint)

The values used to specify a column or combination of columns in a table are unique in the table. The purpose of establishing a primary key is to have the foreign key referenced.

How Primary key is created

Create a primary Key when creating a table

CREATE TABLE table1(    t_id VARCHAR(12) ,    t_name VARCHAR(20),    t_phone VARCHAR(20),    CONSTRAINT t_idss PRIMARY KEY(t_id));

Create a primary key for the t_id column with the constraint named T_idss.

Delete Primary Key

ALTER TABLE table1DROP CONSTRAINT t_idss;

Constraint name is inconsistent with column name, where the constraint name is filled

Add primary Key to an existing table

ALTER TABLE table1ADD CONSTRAINT t_idssPRIMARY KEY(t_id);

Another example of adding a primary key

ALTER TABLE ProductsADD PRIMARY KEY(prod_id);

Although the above code runs no problem, viewing the table design can also see that the primary key setting is successful, but you will be prompted when you delete the primary key operation:
Msg 3728, Level 16, State 1, line 1th
' prod_id ' is not a constraint.
Msg 3727, Level 16, State 0, line 1th
Failed to delete constraint. See the previous error message.
The reason is that the add primary key statement does not use constraint to indicate the constraint name, the system automatically generated the primary key name and constraint name, to first look at the primary key name and constraint name, the deletion is also filled with the constraint name.
The correct way to remove this condition

ALTER TABLE ProductsDROP CONSTRAINT CK__Products__prod_p__1A14E395;ALTER TABLE ProductsDROP CONSTRAINT PK__Products__56958AB222AA2996;

Add a PRIMARY KEY constraint to a multiple-column combination

CREATE TABLE table1(    t_id VARCHAR(12),    s_id VARCHAR(20),    score FLOAT,    CONSTRAINT ts_id PRIMARY KEY(t_id,s_id));

External keyword constraint (Foreign key Constraint)

Defines the relationships between tables to maintain a consistent relationship between the two tables.
Create a foreign Key Constraint when creating a table

CREATE TABLE table2(    s_id VARCHAR(20),    s_name VARCHAR(12),    s_tellphone VARCHAR(11),    s_address VARCHAR(20),    CONSTRAINT PK_s_id PRIMARY KEY(s_id),);--首先新建table2，设置s_id为主键CREATE TABLE table1(    t_id VARCHAR(12),    s_id VARCHAR(20),    score FLOAT,    CONSTRAINT pk_ts_id PRIMARY KEY(t_id,s_id),--新建table1，对t_id和s_id设置联合主键，键名pk_ts_id    CONSTRAINT fk_s_id FOREIGN KEY(s_id)--对s_id设置外键fk_s_id    REFERENCES table2(s_id)--外键fk_s_id外键关联table2的列s_id    ON DELETE CASCADE--设置在table1的s_id删除时table2的s_id同时删除    ON UPDATE CASCADE--设置在table1的s_id更新时table2的s_id同时更新);

Note: To set the Foreign Key association table2 for Table1, you need to insert the table2 data before inserting the data to successfully insert the Table1 data. Change the table2.s_id data and the TABLE1.S_ID data will change automatically. But change the table1.s_id data, perform the Times foreign key conflict will be reported. In short, after the Table1 Set Foreign Key association table2, table1 data follow table2 go, can not be reversed.

Add and remove foreign key constraints with primary key.

Unique constraint

A type that can define a unique constraint other than the primary key, allowing null values. Add a unique method above, here is just a simple example.

USE testGOALTER TABLE table2ADD CONSTRAINT uk_s_tellphoneUNIQUE(s_tellphone);

Check Constraint

is divided into table constraints and column constraints, which are used to limit field values to a range.

Add a CHECK Constraint

ALTER TABLE table2ADD sex CHAR(2);--在table表中添加sex，数据类型为CHAR，用来存放性别GOALTER TABLE table2ADD CONSTRAINT ck_sex CHECK(sex in(‘男‘,‘女‘));

Note: The Sex column data type can not be bit, if fill bit, can only store 0 and 1, with CHECK constraint limit results for both male and female will be error.

Add a check constraint when creating a table

CREATE TABLE table3(    t3_id VARCHAR(12),    t3_name VARCHAR(20),    t3_class VARCHAR(12),    t3_type VARCHAR(12),    CONSTRAINT ck_t3_type CHECK(t3_type in(‘类型1‘,‘类型2‘,‘类型3‘)) )

If you insert data that does not conform to the constraint after you add the constraint

INSERT INTO table3(    t3_id,    t3_name,    t3_class,    t3_type)VALUES(‘2018038219‘,‘李建‘,‘社会与科学‘,‘任何数据‘);

MSG 547, Level 16, State 0, line 1th
The INSERT statement conflicts with the CHECK constraint "Ck_t3_type". The conflict occurred in the database "test", table "Dbo.table3", column ' T3_type '.
Statement has been terminated.

Delete a check constraint

ALTER TABLE table3DROP CONSTRAINT ck_t3_type;

Default constraint

Specify the default value for the column by defining the default value for the column or by using the database's default value object binding table column.

Add a DEFAULT constraint when building a table

CREATE TABLE table3(    t3_id VARCHAR(12),    t3_name VARCHAR(20),    t3_class VARCHAR(12),    t3_type VARCHAR(12) DEFAULT ‘类型1‘ )

Delete the default constraint

ALTER TABLE table3DROP CONSTRAINT DF__table3__t3_type__3D5E1FD2;--DF__table3__t3_type__3D5E1FD2是DEFAULT约束的约束名

Add constraint does not specify a constraint name

ALTER TABLE table3ADD DEFAULT ‘类型2‘ FOR t3_type;GO

Add constraint Specify constraint name

ALTER TABLE table3ADD CONSTRAINT df_t3_typeDEFAULT ‘类型2‘ FOR t3_type;GO

NOT NULL constraint

The constraint field value is not empty.

Set NOT NULL constraint when table is built

CREATE TABLE table3(    t3_id VARCHAR(12) NOT NULL,    t3_name VARCHAR(20) NOT NULL,    t3_class VARCHAR(12) NOT NULL,    t3_type VARCHAR(12) NOT NULL )

Add a NOT NULL constraint for an existing column

ALTER TABLE table3ALTER COLUMN t3_type VARCHAR(12) NOT NULL;

Remove NOT NULL constraint

ALTER TABLE table3ALTER COLUMN t3_type VARCHAR(12) NULL;GO

Custom default value objects maintain data integrity

CREATE DEFAULT date_today AS GETDATE();--新建默认值对象名date_today,默认值为getdate()函数，获取当前日期GOEXEC sp_addtype date_time,‘date‘,‘NULL‘;--利用存储过程新建自定义数据类型date_time,参照系统数据类型dateGOEXEC sp_bindefault ‘date_today‘,‘date_time‘;--将默认值对象date_today绑定到自定义数据类型date_time上GOCREATE TABLE table3(--新建table3，设置字段t3_date的数据类型为date_time    t3_id VARCHAR(12),    t3_name VARCHAR(20),    t3_class VARCHAR(12),    t3_type VARCHAR(12),    t3_date date_time);GOINSERT INTO table3(--为新建表table3插入一条数据，不指定t3_date的值，看默认值是否有效    t3_id,    t3_name,    t3_class,    t3_type)VALUES(‘2018038220‘,‘李建‘,‘社会与科学‘,‘类型1‘);GOSELECT * FROM table3;--查询table3数据，看t3_date是否有默认值为当前日期

The query results are as follows

To bind a default object directly to a column

ALTER TABLE table3ADD t3_date1 DATE;--在table3表中新增一列t3_date1，数据类型为DATEGOEXEC sp_bindefault ‘date_today‘,‘table3.t3_date1‘;--直接将默认值对象date_today绑定到table3的t3_date1列GOINSERT INTO table3(--为新建表table3插入一条数据，不指定t3_date和t3_date1的值，看默认值是否有效    t3_id,    t3_name,    t3_class,    t3_type)VALUES(‘2018038221‘,‘李建‘,‘社会与科学‘,‘类型‘);GOSELECT * FROM table3;GO

The query results are as follows

The owner of the stored procedure query default value object

USE testEXEC sp_help date_today;GO

Results

Delete a default value object

DROP DEFAULT date_today;

Deletion is unsuccessful, prompting the following information:
Msg 3716, Level 16, State 3, line 1th
The default value ' Date_today ' cannot be deleted because it is bound to one or more columns.
Then we know that when a default value object is bound to a column, it cannot be deleted and, if you want to delete it, you must first unbind it. In the above operation, our default object Date_today is bound to the T3_date1 field of the test Database Table3 table.

Unbind a default value object from a column

USE testGOEXEC sp_unbindefault ‘table3.t3_date1‘;

At this point we try to delete the default object again, we find that it is not possible, it is important to note that in the above operation, the default value object Date_today not only bound the T3_date1 column, but also bound the custom data type date_time, and the data type is defined to the T3_date column, We also need to unbind the default value object for Date_time.

EXEC sp_unbindefault ‘date_time‘;

Delete the default object again at this time, and you can delete it successfully. When you delete a default value object, the previously bound field no longer has a default value.

Custom rules maintain data integrity

Rules are rules and restrictions on the values of columns or custom data types. The expression of a custom rule must return a value of type Boolean, and the expression cannot contain more than one variable.

CREATE RULE score_rule AS @math_score>=0;GO--新建规则score_rule,参数@math_scoreEXEC sp_addtype ‘score_type‘,‘float‘,‘NULL‘;GO--新建自定义数据类型score_typeCREATE TABLE table_score(--新建表table_score，预设mt_score和at_score字段用于绑定规则s_id VARCHAR(4),s_name VARCHAR(10),mt_score float,--该字段将用于规则score_rule绑定到列at_score score_type--该字段将用于规则score_rule绑定到自定义数据类型);GOEXEC sp_bindrule ‘score_rule‘,‘score_type‘;GO--将score_rule规则绑定到自定义数据类型score_typeEXEC sp_bindrule ‘score_rule‘,‘table_score.mt_score‘;GO--将score_rule规则绑定到table_score表的mt_score列----以下进行规则测试INSERT INTO table_score(s_id,s_name,mt_score,at_score)VALUES(‘0001‘,‘张华‘,‘-1‘,‘-1‘);GO

Database error after inserting a violation of the rules
MSG 513, Level 16, State 0, line 1th
The insertion or update of a column conflicts with the rule specified by the previous CREATE RULE statement. The statement has been terminated. The conflict occurred in database ' Test ', table ' Dbo.table_score ', column ' Mt_score '.
Statement has been terminated.
Obviously, the insertion value of Mt_score is-1, violates the rule that must be greater than or equal to 0, the database error. Change the insertion value of Mt_score to the data that conforms to the rule, run the INSERT statement again, the database will still get an error, because the insertion value of the At_score field is also a violation of the rules. Change two data to a rule-compliant return and execute successfully.

Note: When you create a new rule, the expression must return a value of type Boolean, otherwise it will be an error.

Msg 4145, Level 15, State 1, procedure Sum_score, line 1th
An expression of a non-Boolean type is specified in the context where the condition should be used (near '; ').

Delete a custom rule

As with custom default value objects, deleting a custom rule requires that the rule be first unbound from fields and custom data types. In the above operation, the Score_rule rule is bound to the custom data type Score_type and the column Mt_score. So execute the following statement:

EXEC sp_unbindrule ‘score_type‘;GO--解除规则score_rule与score_type之间的绑定EXEC sp_unbindrule ‘table_score.mt_score‘;GO--解除规则score_rule与表table_score的mt_score列的绑定DROP RULE score_rule;--删除score_rule规则

Note: After testing, a column can only bind 1 rules, if a column is bound to 2 rules, the previous rule will be replaced by the latter rule.

View Custom Rules

EXEC sp_help ‘score_rule‘;

Results

To view the definition information for a custom rule

EXEC sp_helptext ‘score_rule‘;GO

Results

SQL Server 2008 from getting started to mastering--20180629