Explain the SQL Server database architecture and objects, define data integrity, SQL Server

Explain the SQL Server database architecture and objects, define data integrity, SQL Server

Preface

In this section, we will continue our SQL journey. In this section, we will talk about some basic knowledge and precautions. If there are any mistakes, I hope to point out the short content, in-depth understanding.

Database architecture and objects

The database contains the architecture and the architecture contains objects. The architecture can be seen as a container for objects such as tables, views, and stored procedures. A schema is a namespace that is used as the prefix of the object name. For example, in a Cnblogs architecture, a table named Blogs is used, in this case, we use the schema-type qualified name (that is, the two-part type object name), so Blogs is represented as Cnblogs. blogs. If the schema name is omitted when an object is referenced, SQL Server checks whether the object exists in the user's default schema. If not, SQL Server checks whether the object exists in the dbo schema. When we create a database, when you do not explicitly specify another architecture, the database automatically uses the dbo architecture as our default architecture. Microsoft also recommends that you always use the [two-part formula] Object Name When referencing an object in code. Based on this recommendation, we recommend that you reference the object: the schema-qualified object name is always used in the code.

Define data integrity

The biggest benefit of a relational model is that we can customize data integrity. At the same time, data integrity is an indispensable part of a relational model. What is data integrity, one aspect of transparency is declarative constraints on data. Explicit Constraints in SQL Server include: primary key constraints, unique key constraints, foreign key constraints, check constraints, and default constraints. We will introduce these constraints one by one.

Primary key constraint

Create a table first:

CREATE TABLE Blogs( BlogId INT NOT NULL, BlogName VARCHAR(max) NOT NULL);

The primary key constraint is used to force the uniqueness of a row. We cannot represent the uniqueness of the row above. Now we add a constraint to force the uniqueness of the row. The following is the constraint with the primary key.

ALTER TABLE dbo.BlogsADD CONSTRAINT pk_constraint_blogId PRIMARY KEY(BlogId)

In the key folder, the constraint on column BlogId is generated to upgrade to the primary key, as shown below:

When you insert duplicate data into a primary key, a message is displayed, indicating that duplicate keys cannot be inserted, which violates the constraints. To enforce the uniqueness of logical primary key constraints, SQL Server creates a unique index in the background. A unique index is a physical mechanism used by SQL Server to force uniqueness, an index (not necessarily a unique index) is used to accelerate queries and avoid unnecessary full table scans.

Unique Constraint

The unique constraint forces the uniqueness of rows, allowing us to implement the standby key concept of the relational model in our own database. It is different from the primary key. Multiple unique constraints can be defined in the same table, and multiple NULL tags are allowed at the same time (similar to the NULL tags are different from each other ), however, SQL Server rejects repeated NULL tags (similar to two NULL tags that are equal to each other) through UNIQUE. The following is a unique constraint on BlogName.

ALTER TABLE dbo.BlogsADD CONSTRAINT uq_constraint_blogname UNIQUE(BlogName)

The result of adding a unique constraint is as follows:

I tried to find that I could not add a unique constraint to the string and text types, but I am not sure why I cannot add a unique constraint. (I am wondering if I can create a constraint in SQL 2008R2 ).

Through the above explanation of the primary key constraints and unique constraints, we will understand the primary key constraints and unique constraints? The blogger looks at the basic tutorial of SQL Server2012. The tutorial ends here and I haven't figured it out yet, what are the differences between primary key constraints and unique constraints?

(1) Can I add a primary key constraint on the key?

We add a unique constraint based on the above-mentioned BlogId constraint as the primary key, as shown below:

ALTER TABLE dbo.BlogsADD CONSTRAINT uq_constraint_blogId UNIQUE(BlogId)

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

(2) In the basic tutorial above, we also mentioned that the column with the uniqueness constraint can allow multiple NULL tags. Is that true? Let's look at another situation.

Create the following table:

create table test (Id INT NOT NULL,NAME VARCHAR(max) NOT NULL)

Next, the Id constraint is unique.

ALTER TABLE testADD CONSTRAINT UNQ UNIQUE(Id)

Now let's add a NULL value to the Id. Can the result be inserted or not?

INSERT INTO TEST VALUES(NULL,'B')

Can the column with the unique constraint be NULL? Is there an error in the tutorial or an sb translation error? We should check that the column Id cannot be NULL when defining the table, so here our question is over. The column with the unique constraint can be NULL.

(3) What are the differences between primary key constraints and unique constraints?

Primary Key constraints:By making uniqueness to the power system, a clustered index is created for the primary key in the column and the primary key cannot be empty.

Unique constraint:The unique key created on the column is a non-clustered index. The unique constraint only allows one NULL value.

The biggest difference between the two is that the primary key constraint emphasizes the uniqueness of the row to identify the row and does not allow repetition. The unique constraint emphasizes that the uniqueness of the Column cannot be repeated.

(4) A unique index can be created for both the primary key constraint and the unique constraint.

[1] A unique index can be created through both primary key constraints and unique constraints.

If no clustered index exists in the table and we do not specify a non-clustered index, a unique clustered index will be automatically created through primary key constraints.

When a unique constraint is created, a non-clustered index is created by default to force a unique constraint. If the clustered index does not exist in the table, we can specify a clustered index.

[2] differences between unique constraints and unique Indexes

Create a table as follows:

CREATE TABLE test( Id INT NOT NULL PRIMARY KEY, Code INT)

First, I only create a unique index for the Code.

CREATE UNIQUE INDEX uq_ix ON dbo.test(Code)

Then we add a unique constraint to the Code column:

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

In this case, we can see the unique non-clustered index created by the unique index and unique constraint in the index folder.

It seems that both of them are unique non-clustered indexes, but the icons are different. They should be the same.

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

[1] different error codes are returned.

When we insert data, the unique Index Returns Error Code 2601.

Error Code returned by the unique constraint is 2627.

[2] The unique constraint cannot be filtered, but the unique index can be filtered, as shown below:

CREATE UNIQUE NONCLUSTERED INDEX uq_code_filterON test(Code) WHERE Code is not null;

Summary:The above only indicates the difference in use between the two. There is no big difference between the unique constraint and the unique index, at the same time, the query performance of the unique constraint and the unique index are not very different. For the unique constraint, we always emphasize data integrity. The unique constraint on the column ensures that its values cannot be repeated, this also significantly improves the performance of index queries.

Foreign key constraint

Foreign key constraints are also used to force data integrity. The purpose of Foreign keys is to restrict the allowed values in foreign key columns to exist in those referenced columns. The following is a demonstration of foreign key constraints. We create the following table of employees and departments:

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(50))GOCREATE TABLE [dbo].[Employee] ( [EmployeeID] INT NOT NULL IDENTITY, [FirstName] VARCHAR(50), [LastName] VARCHAR(50), [DepartmentID] INT)

We know that the employee table depends on the department table. Which department is an employee in? In this case, the Department Id in the employee table should be the foreign key of the Department Id in the department table. Next, we apply the foreign key constraint as follows:

ALTER TABLE [dbo].[Employee]ADD CONSTRAINT [FK_Employee_Department]FOREIGN KEY ( [DepartmentID] ) REFERENCES [dbo].[Department] ( [DepartmentID] )

After the execution, you will find the following error:

Now we know that foreign keys can not be created at will. Why do we see the error of referencing the Department table and using the Department Id in the employee table as the foreign key constraint? Through the above error, we know that there is no matched primary key or candidate key in the referenced table, that is, the Department table. What does this mean, it means that the foreign key in the referenced table must be the complete primary key of the referenced table, instead of being a part of the referenced table, it is more clear that the Department Id in the referenced table is the primary key. Here, we do not impose a primary key constraint on the department Id in the department table, leading to the above error. We can add a primary key constraint.

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

The foreign key constraint is created. In fact, there is another possibility. When the table to be referenced already has a primary key instead of a column referenced by a foreign key, the Id in the department table is not used as the primary key, the Department Id in our employee table must be used as a foreign key constraint. In this case, 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 Constraints

The Check constraint defines a predicate. the row to be inserted into the table or the row to be modified must meet this requirement.

For example, to add another salary field to the employee table, it is clear that the salary must be a positive value. In this case, we can perform the Check constraint as follows:

ALTER TABLE dbo.EmployeesADD CONSTRAINT CHK_Employees_salaryCHECK(salary > 0.00)

If you try to insert a non-positive value, it will be rejected by the database. We need to note that the Check constraint is rejected only when the result is false. If the result is True or UNKNOWN, it will be accepted. That is, when the result is NULL, it will be inserted or modified successfully.

Default Constraint

The default constraint is nothing more than a default value given when a table is created. It is common that the column "date" for adding data exists in the Table. At this time, a default value is given completely, and the current date is used. The DEFAULT constraint is represented by the DEFAULT keyword. For example:

ALTER TABLE dbo.EmployeesADD CONSTRAINT DFT_Employees_updateTimeDEFAULT(GETDATE()) FOR UpdateTime

Summary

This section describes the primary key constraints and unique constraints in detail. The rest is relatively simple, but it is skipped. At this point, we will continue later.

The above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message and share it with us!