Detailed SQL Server database schemas and objects, defining data integrity _mssql

Objective

In this section, we continue our trip to SQL, this section of the title to say some basic knowledge and need to pay attention to the place, if there is something wrong, but also hope that the short content, in-depth understanding.

Database Schemas and objects

The database contains schemas, and schemas contain objects, and schemas can be viewed as 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 name of an object, such as a table named Blogs in the Cnblogs schema, 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 to see if the object exists in the user's default schema, and if not, check 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 when referencing objects in code, always use the "two-part" object name, based on this recommendation we recommend when referencing an object: Always use the schema-qualified object name in your code, which is a two-part name.

Defining data integrity

The best thing about relational models is that we can customize data integrity, at the same time, data integrity is an integral part of the relational model, what is data integrity, the transparent point is that the data is declarative constraints, in SQL Server declarative constraints include: PRIMARY KEY constraints, unique key constraints, foreign KEY constraints, CHECK constraints, DEFAULT constraints. Here we Yi Yilai introduce these constraints.

PRIMARY KEY constraint

The following is the first 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 uniqueness of the row, we cannot represent the uniqueness of the row, and now we add the constraint to enforce the uniqueness of the row, with the primary key constraint as follows.

ALTER TABLE dbo. Blogs
ADD CONSTRAINT pk_constraint_blogid PRIMARY KEY (blogId)

A constraint that generates a column blogID in the key folder is upgraded to the primary key, as follows:

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

Unique constraint

Unique constraints enforce the uniqueness of the row, allowing us to implement the fallback key concept of the relational model in our own database. Unlike a primary key, it is possible to define multiple unique constraints in the same table while allowing multiple null tags (similar to null tags to differ from each other), but SQL Server denies repeating null tags (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. Blogs
ADD CONSTRAINT uq_constraint_blogname UNIQUE (blogname)

Adding a unique constraint at this point results in the following

Try to find that you can't add a unique constraint to strings and text types, and you don't know why you can't add a unique constraint (add: In SQL 2008r2 can be built, really wonder).

By explaining the primary key constraints and the unique constraints mentioned above, do we understand the PRIMARY KEY constraint and the unique constraint? The blogger is looking at the basic SQL Server2012 tutorial, which is the end of the tutorial, so I haven't figured out how the primary KEY constraint and the unique constraint should be used, and what is the difference between a primary KEY constraint and a unique constraint?

(1) To add a primary KEY constraint on the key, then can you add a unique constraint on this basis?

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

ALTER TABLE dbo. Blogs
ADD CONSTRAINT uq_constraint_blogid UNIQUE (blogId)

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

(2) A column in the above basic tutorial that also mentions uniqueness constraints can allow multiple null tags, really? Let's look at a different 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 test
ADD CONSTRAINT unq UNIQUE (Id)

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

INSERT INTO TEST VALUES(NULL,'B')

Can a column that is not a unique constraint be null, if the tutorial goes wrong, or if SB's translation goes wrong, we should see that the column ID cannot be NULL when the table is defined, so here our question is over, and the column with the unique constraint 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 enforcing 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 is that the PRIMARY KEY constraint emphasizes the uniqueness of the row to identify the row, does not allow duplicates, and the unique constraint emphasizes that the uniqueness of the column does not allow duplication.

(4) A PRIMARY KEY constraint and a unique constraint can all establish a unique index

The ' 1 ' unique index can be created by 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 a PRIMARY key constraint.

When a unique constraint is created, a nonclustered index is created by default 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 difference

We next create a table, as follows:

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

First, I only create a unique index on code.

CREATE UNIQUE INDEX uq_ix ON dbo.test(Code)

Now we add a unique constraint to the code column:

ALTER TABLE StudyTest.dbo.test
ADD CONSTRAINT uq_nonclster_ix UNIQUE (Code)

At this point we can see the unique nonclustered index created by the unique index and UNIQUE constraint created under the Index folder

It seems that both are the only 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" returns different error codes

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

The error code returned by the unique constraint is 2627

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

CREATE UNIQUE nonclustered INDEX uq_code_filter on
Test (code) WHERE code was not null;

Summary: the above just means the difference between the two in use, there is no big difference between the unique constraint and the unique index, and the query performance is not very different for the unique constraint and the unique index, we always emphasize the data integrity for the unique constraint. A unique constraint on a column guarantees that its value cannot be duplicated, which can significantly improve performance when indexing queries are established.

FOREIGN KEY constraint

Foreign KEY constraints are also used to enforce data integrity, and the purpose of foreign keys is to limit the allowable values in the outer key columns to those that are referenced in the column. Let's demonstrate the foreign key constraint by creating the following employee table and Department table:

Use Sqlstudy;
IF object_id (' dbo. Department ', ' U ') is not NULL
 DROP TABLE dbo. Department
CREATE TABLE [dbo].[ Department] (
 [DepartmentID] INT not NULL IDENTITY,
 [departmentname] VARCHAR
) go
CREATE TABLE [dbo]. [Employee] (
 [EmployeeID] INT not NULL IDENTITY,
 [FirstName] VARCHAR (m),
 [LastName] VARCHAR ()
 [ DepartmentID] INT
)

From the above we know that the employee table is dependent on the departmental table, which department is an employee in the end? So the department ID in the employee table should be the foreign key of the department ID in the Department table, and then we do the foreign KEY constraint, as follows:

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

At this point you will find the following error:

Now that we know that foreign keys are not built casually, why do we have errors that refer to the department table and use the departmental IDs in their employee tables as foreign KEY constraints? We know from the above error that there is no matching primary key or candidate key in the reference table, the Department table, what this means is that the foreign key in the reference table must be a full primary key in the referenced table, not as part of the referenced table, and it is more explicit that the department ID in the department table should be the primary key, Here we do not have a PRIMARY KEY constraint on the departmental ID in the departmental table, resulting in an error. We can add a PRIMARY KEY constraint

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

At this point the foreign KEY constraint is not established. There is actually a possibility that when we need to refer to a table that already has a primary key, not a column referenced by a foreign key, the ID in the departmental table is not a primary key, and the department ID in our employee table needs to constrain the department ID as a foreign key. This time we just need to create a unique or UNIQUE constraint on the departmental ID in the departmental table.

CREATE UNIQUE INDEX [Ix_departmentid] on
[dbo].[ Department] ([DepartmentID])
go
ALTER TABLE [dbo].[ Employee]
ADD CONSTRAINT [fk_employee_department]
FOREIGN KEY ([DepartmentID]) REFERENCES [dbo].[ Department] ([DepartmentID]) go

or UNIQUE constraint

CREATE UNIQUE INDEX [Ix_departmentid] on
[dbo].[ Department] ([DepartmentID])
go
ALTER TABLE [dbo].[ Employee]
ADD CONSTRAINT [fk_employee_department]
FOREIGN KEY ([DepartmentID]) REFERENCES [dbo].[ Department] ([DepartmentID]) go

Check Constraint

A CHECK constraint defines a predicate, and the rows to be inserted into the table or the rows that are modified must meet this requirement.

For example, if you add a pay field to the employee table, it's clear that the salary must be positive, and then we can do a check constraint like this

ALTER TABLE dbo. Employees
ADD CONSTRAINT chk_employees_salary
CHECK (Salary > 0.00)

If you attempt to insert a non positive value, it will be rejected by the database. What we need to be aware of is that the check constraint is rejected only for false results, or if the result is true or unknown is accepted, that is, if the result is null, it will be inserted or modified successfully.

Default constraint

The default constraint is simply that when a table is created, a default value is given, and it is common to have a column of dates added to the table, at which point we completely give a default value and take the current date. The default constraint is represented by the default keyword. For example, as follows:

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

Summarize

In this section we explain in detail the primary KEY constraint and the unique constraint. This one, the rest relatively simple, is slightly skipped, to this end, see you in the next section.

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, if there are questions you can message exchange, but also hope that a lot of support cloud Habitat community!