SQL server-Database schema and objects, defining data integrity

Objective

This section we continue the SQL tour, this section we title to say some basic knowledge and need to pay attention to the place, if there is something wrong, also hope to point out, short content, in-depth understanding, always to review the basics.

Database Schemas and objects

A database contains schemas, and schemas contain objects, and schemas can be considered containers for objects such as tables, views, stored procedures, and so on. A schema is a namespace that is used as a prefix to the object name, such as a table in the Cnblogs schema named Blogs, where we use the schema-qualified name (that is, the two-part object name) so that Blogs is represented as cnblogs.blogs. If we omit the schema name when we reference the object, SQL Server checks whether the object exists in the user's default schema, if it is not checked for the existence of the DBO schema, when we create the database, when the user does not explicitly specify a different schema, The database automatically takes the DBO schema as our default schema. Microsoft also recommends that you always use the "two-part" object name when referencing objects in your code, based on our recommendation when referencing objects: always use schema-qualified object names in code, which are two-part names.

Defining data integrity

The biggest benefit of the relational model is that we can customize the data integrity, while data integrity is an integral part of the relational model, what is data integrity, the more transparent is the declarative constraints on the data, in SQL Server declarative constraints include: PRIMARY KEY constraints, unique key constraints, foreign KEY constraints, CHECK constraints, DEFAULT constraints. Below we hit introduce these constraints.

PRIMARY KEY constraint

Here's the first one to create a table:

CREATE TABLE Blogs (BlogId INT not NULL, Blogname VARCHAR (max) is not null);

The primary KEY constraint is used to enforce the uniqueness of the row, we cannot represent the uniqueness of the row, and now we add a constraint to enforce the uniqueness of the row, with the primary key constrained as follows.

ALTER TABLE dbo. Blogsadd CONSTRAINT pk_constraint_blogid PRIMARY KEY (blogId)

Creating a constraint on a column blogid in the key folder is promoted to the primary key, as follows:

When you insert duplicate data into a primary key, you are prompted to insert a duplicate key failure, which violates the constraint. To enforce the uniqueness of a logical PRIMARY KEY constraint, SQL Server creates a unique index in the background, which is a physical mechanism used by SQL Server to enforce uniqueness, and the index (not necessarily a unique index) is designed to speed up the query and avoid unnecessary full-table scans.

Unique constraint

Unique constraints enforce the uniqueness of rows, allowing us to implement the concept of alternate keys for relational models in our own database. Unlike a primary key, you can define multiple unique constraints in the same table and allow multiple null tokens (similar to the null tags to each other), but SQL Server rejects duplicate null tokens (similar to two null tokens equal to each other) and is constrained by unique. The Blogname is uniquely constrained as shown below.

ALTER TABLE dbo. Blogsadd CONSTRAINT uq_constraint_blogname UNIQUE (blogname)

The result of adding a unique constraint is as follows

Try to find that the string and text types can not add a unique constraint, up knowledge, do not know why can not add a unique constraint ( learned to later found that the maximum index byte is 900, so here we take Max will be error, not for the type).

With the above explanation of the primary KEY constraint and the unique constraint, we are clear about the primary KEY constraint and the unique constraint? Bloggers are looking at the basic SQL Server2012 tutorial, the tutorial is on the end, so far I have not figured out how the primary KEY constraints and unique constraints should be used and the primary KEY constraints and UNIQUE constraints what is the difference?

(1) Adding a PRIMARY KEY constraint to the key, can you add a unique constraint on this basis?

We add a unique constraint on the basis of the above already added blogID as the primary KEY constraint, as follows

ALTER TABLE dbo. Blogsadd CONSTRAINT uq_constraint_blogid UNIQUE (blogId)

Through the above we know that you can add a primary key constraint to the same column or you can add a unique constraint .

(2) The above basic tutorial also mentions that the Uniqueness constraint column can allow multiple null tokens, is that true? Let's take a look at another situation.

We create the following table

CREATE TABLE Test (Id INT not null,name VARCHAR (max) is not NULL)

Next, the ID constraint is a unique constraint.

ALTER TABLE testadd CONSTRAINT UNQ UNIQUE (Id)

At this point we will add a null to the ID to try, the result can be inserted or not?

INSERT into TEST VALUES (NULL,'B')  

The column that is not the only constraint can be null, does the tutorial go wrong or SB translation is wrong, we should look at the definition table when the column ID is not NULL, so here we are the end of the question, the only constraint column can be null.

(3) What is the difference between a primary KEY constraint and a unique constraint?

PRIMARY KEY constraint: by enforcing uniqueness on a column, the primary key creates a clustered index on the column and the primary key cannot be empty.

Unique constraint: By forcing uniqueness on a column, the unique key created on the column is a nonclustered index, and the unique constraint only allows a null value.

The biggest difference between the two is that the primary KEY constraint emphasizes the uniqueness of the row to identify the row, does not allow duplication, and the unique constraint emphasizes that the uniqueness of the column does not allow duplication.

(4) Both primary KEY constraints and UNIQUE constraints can create unique indexes

The "1" unique index can be created through both primary KEY constraints and unique constraints.

If a clustered index does not exist in the table and we do not explicitly specify a nonclustered index, a unique clustered index is automatically created by the primary KEY constraint.

When a unique constraint is created, by default a nonclustered index is created to enforce a unique constraint, and if the clustered index does not exist in the table, we can specify a clustered index.

"2" unique constraint and unique index differences

We then create a table, as follows:

CREATE TABLE Test (Id int not NULL PRIMARY KEY, Code int)

First, I'll just create a unique index on code.

CREATE UNIQUE INDEX Uq_ix on Dbo.test (Code)

At this point we add a unique constraint on the code column:

ALTER TABLE StudyTest.dbo.testADD CONSTRAINT uq_nonclster_ix UNIQUE (Code)

In this case, we can see the unique and unique indexes created by the unique index created by the index folder.

It seems that both are unique nonclustered indexes, but the icons are different.

(5) What is the difference between a unique index and a unique constraint? Can a unique constraint override a unique index?

"1" Return error code is different

When we insert data, the unique index returns an error code of 2601

The unique constraint returns an error code of 2627

The "2" unique constraint cannot be filtered, and a unique index can be filtered as follows

Null

Summary: The above only represents the difference between the two in use, for a unique constraint and a unique index there is no big difference, and for the unique constraints and unique indexes in the query performance is not very different, for the unique constraints we have been emphasizing the data integrity, the column is unique constraints to ensure that its value can not be duplicated, This also significantly improves performance when indexing queries are established.

FOREIGN KEY constraints

Foreign KEY constraints are also used to enforce data integrity, the purpose of which is to restrict the values allowed in the foreign key column to exist in those referenced columns. Let's demonstrate the foreign key constraint, we create the following employee table and Department table:

Use sqlstudy;if object_id ('dbo. Department','U') is not NULL DROP TABLE dbo. Departmentcreate TABLE [dbo]. [Department] ([DepartmentID] INT not NULL IDENTITY, [Departmentname] VARCHAR ()) gocreate TABLE [dbo].[ Employee] ([EmployeeID] INT not NULL IDENTITY, [FirstName] varchar (+), [LastName] varchar (), [Dep Artmentid] INT)            

From the above we know that the employee table is dependent on the department table, which department is an employee in exactly? So at this point the Department ID in the employee table should be the foreign key for the department ID in the Department table, and then we'll do the foreign KEY constraint, as follows:

ALTER TABLE [dbo]. [Employee] ADD CONSTRAINT [Fk_employee_department]foreign KEY ([DepartmentID]) REFERENCES [dbo]. [Department] ([DepartmentID])

When you're done, you'll see the following error:

Now that we know that the foreign key is not easy to build, why does it appear that we are referencing the department table and the department ID in its employee table as a FOREIGN KEY constraint error? With the above error we know that there is no primary key or candidate key in the reference table that is the departmental table, what this means is that the foreign key in the reference table must be the full primary key in the referenced table, not as part of the referenced table, and more specifically, that the department ID in the referenced table, the departmental table, should be the primary key. Here we do not have a PRIMARY KEY constraint on the department ID in the Department table, resulting in the error as above. We can add a PRIMARY KEY constraint

ALTER TABLE [dbo]. [Department] ADD CONSTRAINT [pk_department] PRIMARY KEY ([DepartmentID]) GO

The FOREIGN KEY constraint is now set to complete. There is also a possibility that when we need to refer to a table where a primary key already exists, rather than a column referenced by a foreign key, the ID in the department table is not the primary key, and the department ID in our employee table needs to be the foreign key constraint. At this point we only need to create a unique or UNIQUE constraint on the department ID in the Department table.

CREATE UNIQUE INDEX [Ix_departmentid]on [dbo]. [Department] ([DepartmentID]) Goalter TABLE [dbo]. [Employee] ADD CONSTRAINT [Fk_employee_department]foreign KEY ([DepartmentID]) REFERENCES [dbo]. [Department] ([DepartmentID]) GO

or a UNIQUE constraint

CREATE UNIQUE INDEX [Ix_departmentid]on [dbo]. [Department] ([DepartmentID]) Goalter TABLE [dbo]. [Employee] ADD CONSTRAINT [Fk_employee_department]foreign KEY ([DepartmentID]) REFERENCES [dbo]. [Department] ([DepartmentID]) GO

Check Constraint

A CHECK constraint defines a predicate that the row to insert into the table or the modified row must satisfy.

For example, if you add another salary field to the employee table, it is clear that the salary must be positive, and then we can do check constraints like this

0.00)

If you attempt to insert a non-positive value, it will be rejected by the database. It is important to note that the CHECK constraint is rejected only for false results, or if the result is true or unknown is accepted, that is, when the result is null, it is also inserted or modified successfully.

Default Constraints

The default constraint is simply to give a default value when creating a table, and it is common to have a column with the date of adding data in the table, at which point we are given a default value, whichever is the current date. The default constraint is represented by the defaults keyword. For example, the following:

ALTER TABLE dbo. Employees ADD CONSTRAINT Dft_employees_updatetimedefault (GETDATE ()) for UpdateTime

Summarize

In this section we explain in detail the primary key constraints and the unique constraints of this piece, the rest is relatively simple, is skipped, to this end, the next section farewell.

SQL server-Database schema and objects, defining data integrity