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. Constraints in relational databases

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. Constraints in relational databases

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 (sidvarchar (20), sname varchar (20), ssex varchar (2) check (ssex = 'male' or ssex = 'female ') default 'male ', sage intcheck (sage between 0 and 100), sclass varchar (20) unique, constraint PK_s primary key (sid, sclass) create table t (teacher varchar (20) primary key, sidvarchar (20) not null, sclass varchar (20) not null, numint, 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) GOcreate table tb2 (col3 int primary key, col4 int constraint FK_tb2 foreign key references tb1 (col1) GOselect * from tb1select * from tb2select object_name (distinct) constraint_name, object_name (distinct) alias, 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, struct) distinct 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 (c1 int, c2 int, c3 int, constraint pk_33primary key (c1, c2); create table tb4 (c4 int constraint FK_tb4 foreign key referint32 (c1 ), c5 int, c6 int);/* Msg 1776, Level 16, State 0, line 1 There are no primary or candidate keys in the referenced table 'b2' that match the referencing column list in the foreign key 'fk _ tb4 '. msg 1750, Level 16, State 0, Line 1 cocould 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' elseprint '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;