Shulou(Shulou.com)05/31 Report--

How to understand SQL Server database architecture and objects and define data integrity, I believe that many inexperienced people do not know what to do. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Database schema and objects

The database contains schemas, and schemas also contain objects. Schemas can be seen as containers for tables, views, stored procedures, and other objects. A schema is a namespace that is used as a prefix to object names. For example, in Cnblogs's schema, there is a table named Blogs. In this case, we use a schema-qualified name (that is, a two-part object name), so Blogs is represented as Cnblogs.Blogs. If we omit the schema name when we reference the object, SQL Server will check if the object exists in the user's default schema, if not, check if it exists in the dbo schema, and when we create the database, the database will automatically dbo the schema as our default schema when the user does not explicitly specify another schema. Microsoft also recommends that you always use [two-part] object names when referencing objects in your code. Based on this recommendation, when referencing objects, we recommend that you always use schema-qualified object names, that is, two-part names.

Define data integrity

The biggest advantage of relational model is that we can customize data integrity. At the same time, data integrity is an indispensable part of relational model. What is data integrity? in SQL Server, declarative constraints include primary key constraint, unique key constraint, foreign key constraint, check constraint and default constraint. Let's introduce these constraints one by one.

Primary key constraint

Let's start by creating a table:

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

Primary key constraints are used to enforce row uniqueness, which we cannot express above. Now we add constraints to enforce row uniqueness, as follows with PRIMARY KEY constraints.

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

In the key folder, the constraint on column BlogId is upgraded to the primary key, as follows:

When inserting duplicate data on the primary key, it is prompted that the insertion of the duplicate key failed, violating the constraint. In order to enforce the uniqueness of logical primary key constraints, SQL Server creates a unique index in the background, which is a physical mechanism used by SQL Server to enforce uniqueness. Indexes (not necessarily unique indexes) are designed to speed up queries and avoid unnecessary full table scans.

Unique constraint

The unique constraint enforces the uniqueness of rows, allowing us to implement the concept of alternate keys for relational models in our own databases. Unlike primary keys, you can define multiple unique constraints in the same table while allowing multiple NULL tags (similar to NULL tags that are different from each other), but SQL Server refuses to repeat NULL tags (as if two NULL tags are equal to each other) to be constrained by UNIQUE. BlogName is uniquely constrained as shown below.

ALTER TABLE dbo.BlogsADD CONSTRAINT uq_constraint_blogname UNIQUE (BlogName)

The result of adding a unique constraint at this time is as follows

One by one try to find that strings and text types can not add unique constraints, increased knowledge, do not know why can not add unique constraints (supplement: in sql 2008R2 can be established, really wonder).

Through the above explanation of primary key constraints and unique constraints, do we understand primary key constraints and unique constraints? The blogger is watching the basic SQL Server2012 tutorial, which ends here. I haven't figured out how primary key constraints and unique constraints should be used, and what's the difference between primary key constraints and unique constraints?

(1) if a primary key constraint is added to the key, can a unique constraint be added on this basis?

We add a unique constraint based on the BlogId as the primary key constraint that has been added above, as follows

ALTER TABLE dbo.BlogsADD CONSTRAINT uq_constraint_blogId UNIQUE (BlogId)

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

(2) the above basic tutorials also mentioned that columns with uniqueness constraints can allow multiple NULL tags, is that really the case? Let's look at another situation.

We 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)

At this point, let's try adding a NULL to Id. Can we insert the result or not?

INSERT INTO TEST VALUES (NULL,'B')

Is not the only constraint column can be null, is the tutorial wrong or sb translation error, we should look at the definition table when the column Id is not NULL, so here our question is over, the only constraint column can be NULL.

(3) what is the difference between primary key constraint and unique constraint?

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

Unique constraint: by enforcing uniqueness on the column, the unique key created on the column is a nonclustered index, and the unique constraint allows only 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, while the unique constraint emphasizes that the uniqueness of the column does not allow repetition.

(4) both primary key constraints and unique constraints can establish unique indexes.

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

If there is no clustered index in the table and we do not explicitly specify a nonclustered index, a unique clustered index is automatically created through the primary key constraint.

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

[2] difference between unique constraint and unique index

Let's next create a table as follows:

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

First of all, I only create a unique index on Code

CREATE UNIQUE INDEX uq_ix ON dbo.test (Code)

At this point, we add a unique constraint to the Code column:

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

At this point, under the index folder, we can see the unique index created and the unique nonclustered index created by unique constraints.

It seems that both of them are the only nonclustered index, but the icons are different, so they should be the same.

(5) what is the difference between a unique index and a unique constraint? Can unique constraints replace unique indexes?

[1] different error codes are returned

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

The error code returned by the unique constraint is 2627

[2] unique constraints cannot be filtered, but unique indexes can be filtered, as follows

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

Conclusion: the above only indicates the difference between the two in use, there is no great difference between unique constraint and unique index, and there is no great difference in query performance between unique constraint and unique index. for unique constraints, we have always emphasized data integrity, unique constraints on columns to ensure that their values can not be repeated, which will significantly improve performance when building index queries.

Foreign key constraint

Foreign key constraints are also used to enforce data integrity, and the purpose of foreign keys is to limit the values allowed in foreign key columns to exist mainly in those referenced columns. Let's demonstrate the foreign key constraint. We create the following employee and department tables:

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)

From the above, we know that the employee table depends on the department table. Which department does an employee belong to? So at this time, the department Id in the employee table should be the foreign key of the department Id in the department table, and then we make the foreign key constraint, as follows:

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

After executing at this time, you will find the following error:

Now that we know that foreign keys can't be created easily, why is there an error that we refer to the department table and use the department Id in its employee table as a foreign key constraint? Through the above error, we know that there is no matching primary key or candidate key in the reference table, that is, the department table. What does this mean? it means that the foreign key in the reference table must be the complete primary key in the referenced table, not as part of the referenced table. To be more specific, the referenced table, that is, the department Id in the department table, should be the primary key. Here we do not have a primary key constraint on the department Id in the department table, which leads to the above error. We can just add primary key constraints.

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

Only at this point can the foreign key constraint be established. There is actually a possibility that when we need to reference a primary key in the table instead of a column referenced by a foreign key, then the Id in the department table is not the primary key, and the department Id in our employee table needs to take the department Id as a foreign key constraint, at this time 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 the only 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

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

For example, if you add another salary field to the employee table, it is obvious that the salary must be positive, so we can make a Check constraint like this

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 will be rejected only if the result is false, and will be accepted if the result is True or UNKNOWN, that is, it will be inserted or modified successfully when the result is NULL.

Default constraint

The default constraint is to give a default value when creating a table. It is common that there is a date column in the table to add data. In this case, we fully give a default value and take the current date. The default constraint is represented by the DEFAULT keyword. For example, the following:

After reading the above, ALTER TABLE dbo.EmployeesADD CONSTRAINT DFT_Employees_updateTimeDEFAULT (GETDATE ()) FOR UpdateTime, have you mastered how to understand the SQL Server database architecture and objects and how to define data integrity? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.