T-SQL Development

The reason for putting constraints and indexes together is mainly because of primary key constraints and unique key constraints. They will automatically create a corresponding index. Let's look at the constraints in the database.

1 Constraint

In relational databases, there are usually five constraints, for example:

Use tempdbgocreate table s (sid varchar (20), sname varchar (20), ssex varchar (2) check (ssex = 'male' or ssex = 'female ') default 'male', sage int check (sage between 0 and 100), sclass varchar (20) unique, constraint PK_s primary key (sid, sclass )) create table t (teacher varchar (20) primary key, sid varchar (20) not null, sclass varchar (20) not null, num int, foreign key (sid, sclass) references s (sid, sclass ))

A constraint defined separately on a column is called a column-level constraint. A constraint defined on multiple columns is called a table-level constraint.

1. Primary Key constraints

Define the primary key to uniquely identify the data rows in the table on one or more columns in the table, that is, the 2nd paradigm in the Database Design 3 paradigm;

Primary key constraints require that the key value be unique and cannot be blank: primary key = unique constraint + not null constraint

2. Unique key constraint

The difference between a unique constraint and a primary key constraint is that NULL is allowed. SQL Server only supports one-key columns. Only one row can be NULL, and multiple rows and columns in ORACLE can be NULL.

A table can only have one primary key, but it can have multiple unique keys: unique index = unique constraint

What should I do if I want to ensure the uniqueness of non-NULL values in a column that can be NULL?

From SQL Server 2008, you can use the filtered index)

use tempdbGOcreate table tb5(id int null)create unique nonclustered index un_ix_01on tb5(id)where id is not nullGO

3. Foreign key constraints

One or more columns in the Table reference the primary key or unique key of other tables. The foreign key is defined as follows:

use tempdbGO--drop table tb1,tb2create table tb1(col1 int Primary key,col2 int)insert into tb1 values (2,2),(3,2),(4,2),(5,2)GOcreate table tb2(col3 int primary key,col4 int constraint FK_tb2 foreign key  references tb1(col1))GOselect * from tb1select * from tb2select object_name(constraint_object_id) constraint_name,       object_name(parent_object_id) parent_object_name,       col_name(parent_object_id,parent_column_id) parent_object_column_name,       object_name(referenced_object_id) referenced_object_name,       col_name(referenced_object_id,referenced_column_id) referenced_object_column_name from sys.foreign_key_columnswhere referenced_object_id = object_id('tb1')

Common Problems and Solutions during foreign key development and maintenance:

(1) Some primary key/unique key columns in the primary table cannot be used as foreign keys and must be referenced together by all columns.

create table tb3(c1 int,c2 int,c3 int,  constraint PK_tb3 primary key (c1,c2));                                                                                                                                      create table tb4(c4 int constraint FK_tb4 foreign key references tb3(c1),c5 int,c6 int);/*Msg 1776, Level 16, State 0, Line 1There are no primary or candidate keys in the referenced table 'tb3' that match the referencing column list in the foreign key 'FK_tb4'.Msg 1750, Level 16, State 0, Line 1Could not create constraint. See previous errors.*/

(2) An error occurred while inserting data from the table.

Insert into tb2 values (547)/* Msg, Level 16, State 0, Line 1The INSERT statement conflicted with the foreign key constraint "FK_tb2 ". the conflict occurred in database "tempdb", table "dbo. tb1 ", column 'col1 '. */-- the foreign key can be disabled first when the slave table is in the reference master table (only the constraints check is paused) alter table tb2 NOCHECK constraint FK_tb2alter table tb2 NOCHECK constraint ALL -- enable the foreign key insert into tb2 values) alter table tb2 CHECK constraint FK_tb2

(3) An error occurred while deleting or updating data in the master table.

-- Delete the data from Table tb2 or disable the foreign key before deleting the value in Table tb1. Otherwise, the following error is returned:-insert into tb2 values (2, 2) can be directly deleted for unreferenced rows) delete from tb1GO/* Msg 547, Level 16, State 0, Line 3The DELETE statement conflicted with the REFERENCE constraint "FK_tb2 ". the conflict occurred in database "tempdb", table "dbo. tb2 ", column 'col4 '. */

(4) An error occurred while clearing/deleting the master table.

-- When clearing the master table, even if the foreign key is disabled, the foreign key relationship still exists, so it is impossible to truncatetruncate table tb1/* Msg 4712, Level 16, State 1, line 2 Cannot truncate table 'tb1 'because it is being referenced by a foreign key constraint. */-- drop table tb1/* Msg 3726, Level 16, State 1, line 2 cocould not drop object 'tb1 'because it is referenced by a foreign key constraint. */-- truncate the table first, and then truncate the main table. The truncate table tb2truncate table tb1 -- the only way to delete the foreign key, truncate will not be controlled alter table tb2 drop constraint FK_tb2truncate table tb1 -- add the foreign key at last. Note that the with nocheck option is not checked because the data in the master and slave tables are inconsistent, otherwise, the foreign key cannot be added to alter table tb2 WITH NOCHECKadd constraint FK_tb2 foreign key (col4) references tb1 (col1)

Finally, although multiple foreign keys can be created on a table, we do not recommend that you use foreign keys for performance purposes. Data integrity can be completed in a program;

4. CHECK Constraints

You can define expressions to check column values. This is generally not recommended for performance purposes.

5. NULL Constraint

Controls whether a column is allowed to be NULL. Note the following when using NULL:

(1) in SQL SERVER, Aggregate functions ignore NULL values;

(2) For a struct field, if not null, the field cannot be null, but it can be ''. This is an empty string and is different from null;

(3) NULL values cannot be directly involved in comparison/calculation;

declare @c varchar(100)set @c = nullif @c<>'abc' or @c  = 'abc'    print 'null'else    print 'I donot know'GOdeclare @i intset @i = nullprint @i + 1

During the development process, NULL will bring about a three-value logic, which is not recommended and can be replaced by a default value for a value that may be NULL.

6. DEFAULT Constraints

From the System View, default is managed by SQL Server as a constraint.

select * from sys.default_constraints

(1) constants/Expressions/scalar functions (system, custom, CLR functions)/NULL can be set to the default value;

(2) Use the default value to add a column not null to the table, as shown below:

Create table tb6 (c1 int not null) insert into tb6 select 1 alter table tb6 add c2 int default 35767 not nullselect * from tb6 -- the table remains locked until alter table is complete; -- If you want to add columns to a large table, it takes some time to perform operations on the data page. It is best to evaluate the operation in advance.

Secondary Index

When defining constraints, there is no way to define database constraints. In the current relational database system, primary keys and unique key constraints are implemented by using unique indexes. Therefore, when creating primary keys/unique keys, an index with the same name is automatically generated.

What is the relationship and difference between the unique index generated by the constraint and the unique index created separately?

1. When a primary key or unique key constraint is created, the database automatically creates a unique index.

The automatically generated index cannot be deleted because it is used to implement the constraint. When deleting the constraint, the index is also deleted. The demo script is as follows:

-- Create tableCREATE TABLE TEST_CONS (ID int, CODE varchar (100) -- insert dataINSERT INTO TEST_CONSSELECT 1, 'test1' -- add unique constraintALTER TABLE TEST_CONS add constraint unique unique NONCLUSTERED (ID) -- retrieve constraintSELECT * FROM sys. objects WHERE parent_object_id = object_id ('test _ Cons') AND type = 'uq' -- view the constraint AND return the following result:/* name object_idUQ_TEST_CONS_ID 1243151474 */-- retrieve indexSELECT * FROM sys. indexes WHERE object_id = object_id ('test _ Cons') AND type = 2 -- 2: Non-clustered index -- view the index generated by the constraint. the following result is returned: /* object_id name1227151417 UQ_TEST_CONS_ID */-- check constraintINSERT INTO TEST_CONSSELECT 1, 'test1' -- if the duplicate value is inserted, the following error is returned:/* message, level, status, the first line violates the unique key constraint 'uq _ TEST_CONS_ID '. Duplicate keys cannot be inserted in the object 'dbo. test_cons. */-- Drop index drop index UQ_TEST_CONS_ID ON TEST_CONS -- if the index generated by the constraint is deleted, the following error is returned:/* message, level, status, the row 'test _ CONS. UQ_TEST_CONS_ID 'explicitly uses drop index. This index is used to enforce the unique key constraint. */-- Drop constraint alter table TEST_CONS drop constraint UQ_TEST_CONS_ID -- if the index is deleted, the following query returns an empty result set: -- retrieve constraintSELECT * FROM sys. objects WHERE parent_object_id = object_id ('test _ Cons') AND type = 'uq' -- retrieve indexSELECT * FROM sys. indexes WHERE object_id = object_id ('test _ Cons') AND type = 2 -- 2: Non-clustered index -- drop tableDROP TABLE TEST_CONS

In addition, some attributes of the index generated by the constraint cannot be modified. For example, if IGNORE_DUP_KEY is enabled, the only method is to delete the constraint first and then redefine the constraint/index; there is no such restriction for the separately defined index, as shown in the following example:

Use tempdbGOcreate table tb_cons (ID int constraint pk_tb_cons primary key) create unique clustered index pk_tb_cons on tb_cons (id) with (DROP_EXISTING = ON, FILLFACTOR = 90) alter index pk_tb_cons on tb_cons rebuild with (IGNORE_DUP_KEY = ON)/* Msg 1979, Level 16, State 1, line 1 Cannot use index option ignore_dup_key to alter index 'pk _ tb_cons' as it enforces a primary or unique constraint. */exec sp_helpindex tb_cons -- unique index created separately. You can modify the attributes of create unique index ix_tb_cons on tb_cons (id) alter index ix_tb_cons on tb_cons rebuild with (IGNORE_DUP_KEY = ON, ONLINE = ON) drop table tb_cons

To ensure data uniqueness, there is no difference between the unique index and the unique constraint. Should we use the constraint or index?

The constraint definition usually appears in the logical structure design stage of the database, that is, when the table structure is defined, the index definition usually appears in the physical structure design/query optimization stage of the database.

In terms of functions, the unique constraint is no different from the unique index, but it is not the same in database maintenance. The unique constraint can be replaced by the unique index for convenient maintenance, however, the primary key constraint cannot be replaced.

2. Create a unique index first, and then create the unique constraint for this index Field

In this case, the database does not use an existing unique index. A prompt is displayed, indicating that an index with the same name already exists. The constraint creation fails. If different names are specified, another unique index will be generated. The demo script is as follows:

-- Create tableCREATE TABLE TEST_CONS (ID int, CODE varchar (100) -- insert dataINSERT INTO TEST_CONSSELECT 1, 'test1' -- create indexCREATE unique index UQ_TEST_CONS_IDON TEST_CONS (ID) -- retrieve constraintSELECT * FROM sys. objects WHERE parent_object_id = object_id ('test _ Cons') AND type = 'uq' -- retrieve indexSELECT * FROM sys. indexes WHERE object_id = object_id ('test _ Cons') AND type = 2 -- 2: Non-clustered index -- check IndexINSERT INTO TEST_CONSSELECT 1, 'test1'-the prompt is: unique index/* message 2601, Level 14, status 1, row 1st cannot be in the 'dbo. insert duplicate key rows in TEST_CONS. */-- Add constraintALTER TABLE TEST_CONS add constraint UQ_TEST_CONS_ID unique nonclustered (ID) -- a unique constraint with the same name as the index cannot be created because the CONSTRAINT will generate an index with the same name/* message 1913, level 16, status 1, and 2nd rows failed because the index or statistics with the name 'uq _ TEST_CONS_ID 'already exists in the table 'test _ Cons. Message 1750, level 16, status 0, 2nd rows cannot create constraints. See the preceding error message. */-- Add constraintALTER TABLE TEST_CONS add constraint UQ_TEST_CONS_ID_1 unique nonclustered (ID) -- changing the name is successful, but the UNIQUE index UQ_TEST_CONS_ID_1 -- drop tableDROP TABLE TEST_CONS

3. is the primary key clustered index?

By default, SQL Server defines the generated unique index as clustering when defining the primary key, which is easily confused when it comes to contact. Indexes corresponding to the primary key can also be non-clustered, as shown below:

use tempdbGOcreate table test_pk(id int not null)alter table test_pk add constraint PK_test_pk primary key nonclustered(id);

When a primary key is defined in SQL Server, clustered indexes are generated by default. The only advantage is that the efficiency of primary key column range scanning/searching is relatively high, but the data insertion efficiency is poor (clustered index, non-clustered index, must be maintained once), and if the primary key column is not selected well, it will affect the performance of other non-clustered indexes.

When a primary key is defined in ORACLE, non-clustered indexes are generated by default, which is not conducive to range scanning/searching of primary key columns. However, data insertion efficiency is better, which is a trade-off between different database products.

This article is from the "SQL Server DBA" blog, please be sure to keep this source http://qianzhang.blog.51cto.com/317608/1333464