t-sql 唯一约束_SQL唯一约束-终极指南

傷城~ 2023-02-28 06:08 30阅读 0赞

t-sql 唯一约束

Hello! Hope you guys are doing well. In this article, we will be unveiling one of the interesting and important topic in [SQL ][SQL]— **SQL UNIQUE Constraint**.

你好！ 希望你们一切都好。 在本文中，我们将揭露[SQL中][SQL]有趣且重要的主题之一**-SQL UNIQUE Constraint** 。

--------------------

## 什么是UNIQUE约束？ **(****What is a UNIQUE constraint?****)** ##

[SQL Constraints][] is a set of rules that are applicable to the schema or the data that is fed to the structure of a database. One such constraint is UNIQUE. The name ‘UNIQUE’ itself speaks about its nature and function.

[SQL约束][SQL Constraints]是一组适用于架构或馈入数据库结构的数据的规则。 这样的约束之一就是UNIQUE。 “ UNIQUE”这个名称本身就说明了其性质和功能。

SQL UNIQUE constraint enables the column to be free from redundant values. It helps restrict the input values to be unique. Thus, by applying a UNIQUE constraint to a column or a set of columns, we ensure that the data contains no Duplicate entries within it.

SQL UNIQUE约束使列中没有多余的值。 它有助于将输入值限制为唯一。 因此，通过将UNIQUE约束应用于一列或一组列，我们确保数据中不包含重复项。

Let us understand the importance and functionality of UNIQUE constraint through a real time application/example.

让我们通过实时应用程序/示例了解UNIQUE约束的重要性和功能。

Consider an online e-learning business portal. The owner of the business portal announces that he would grant free access to Data science courses in the coming time. In order to access the course material, the candidates would have to enter their email address and phone number.

考虑一个在线电子学习业务门户。 商业门户网站的所有者宣布，他将在未来一段时间内授予免费访问数据科学课程的权限。 为了访问课程资料，候选人必须输入其电子邮件地址和电话号码。

Now, to make sure that a single candidate receives a single material kit at a time, the database engineer has to make sure that no repeated email ids and phone numbers must be accepted in the database.

现在，要确保单个候选人一次能收到一个材料包，数据库工程师必须确保数据库中不再需要重复的电子邮件ID和电话号码。

This is where SQL UNIQUE constraint comes into the picture. Using UNIQUE constraint, no duplicate entries would be allowed. Thus, making sure that a single candidate does not grant access to multiple course material kits.

这是SQL UNIQUE约束出现的地方。 使用UNIQUE约束，不允许重复条目。 因此，确保单个候选人不会授予访问多个课程资料包的权限。

Having understood the working of SQL UNIQUE constraint, let us now understand the syntax of the same.

了解了SQL UNIQUE约束的工作原理后，现在让我们了解一下它的语法。

--------------------

## SQL UNIQUE约束的语法 **(****Syntax of SQL UNIQUE constraint****)** ##

SQL UNIQUE constraint works with the structure as well as the data of the database.

SQL UNIQUE约束适用于数据库的结构和数据。

-- Any data definition language command(CREATE/ALTER/DROP)
    column-name UNIQUE;

The SQL UNIQUE constraint is applied to one or multiple columns while defining the commands in the table or even can be applied while modifying the table(eg.ALTER command)

SQL UNIQUE约束在表中定义命令时应用于一列或多列，甚至可以在修改表时应用（例如ALTER命令）

By applying UNIQUE rule to a column, the database accepts only ‘unique’ values for the specified column.

通过将UNIQUE规则应用于列，数据库将仅接受指定列的“唯一”值。

--------------------

## 通过示例实现UNIQUE约束 **(****Implementing UNIQUE constraint through examples****)** ##

We’ll use the SQL UNIQUE constraint in multiple different ways.

我们将以多种不同方式使用SQL UNIQUE约束。

### **具有CREATE表命令SQL UNIQUE CONSTRAINT** 。 **(****SQL UNIQUE CONSTRAINT with CREATE table command.****)** ###

In this example, we create a table using [SQL Create query][] and define the following columns:

在此示例中，我们使用[SQL Create查询][SQL Create query]创建表并定义以下列：

*  **id** 
    
    **ID**
 *  **acc\_no**
    
    **acc\_no**
 *  **name**
    
    **名称**

CREATE TABLE INFO(
       ID   INT              NOT NULL,
       ACC_NO  INT           UNIQUE,
      NAME VARCHAR (20),   
       PRIMARY KEY (ID)
    );

Here, the column ‘id’ is specified as [NOT NULL][](constraint), which means that the data values passed to this column can never be empty or null. Further, the column ‘ID’ is defined to behave as a PRIMARY KEY. SQL [PRIMARY KEY][] too makes sure that the values passed to the table are non-redundant.

在这里，列“ id”被指定为[NOT NULL][] （约束），这意味着传递给该列的数据值永远不能为空或null。 此外，列“ ID”被定义为充当主键。 SQL [PRIMARY KEY][]也可以确保传递到表的值是非冗余的。

We have set the column ‘acc\_no’ to UNIQUE by which the duplicate entries for this column would be forbidden. If we try to enter duplicate values for ‘acc\_no’, duplicate value error would be raised.

我们已将“ acc\_no”列设置为UNIQUE，通过该列，该列的重复条目将被禁止。 如果我们尝试为“ acc\_no”输入重复值，则会出现重复值错误。

We can set UNIQUE constraint for multiple columns of the table as shown below–

我们可以为表的多个列设置UNIQUE约束，如下所示–

CREATE TABLE Sales (
        ID int NOT NULL,
        NAME varchar(255) NOT NULL,
        Age int,
        Salary INT,
        CONSTRAINT Unique_sales UNIQUE (NAME,Age)
    );

In the above example, we have set UNIQUE constraint to two columns — ‘NAME’ and ‘Age’ and have provided a constraint name as ‘Unique\_sales’.

在上面的示例中，我们将UNIQUE约束设置为两列-“ NAME”和“ Age”，并将约束名称提供为“ Unique\_sales”。

### **带有ALTER table命令SQL UNIQUE CONSTRAINT** **(****SQL UNIQUE CONSTRAINT with ALTER table command****)** ###

At times, if we wish to set the UNIQUE constraint to any data column after defining it, we can club it with SQL Alter table query.

有时，如果我们希望在定义后将UNIQUE约束设置为任何数据列，则可以将其与SQL Alter表查询合并。

[SQL ALTER Table][] query along with UNIQUE constraint helps us modify the rules of the column and set unique rule/constraint to the specified column or columns.

[SQL ALTER表][SQL ALTER Table]查询以及UNIQUE约束可帮助我们修改列的规则，并为指定的一个或多个列设置唯一的规则/约束。

Here, we have modified the above-created table – ‘Sales’ and have set Unique constraint to the column ‘Age’.

在这里，我们修改了上面创建的表“ Sales”，并将“ Unique”约束设置为“ Age”列。

ALTER TABLE Sales
       MODIFY Age INT UNIQUE;

### **带有SQL DROP命令SQL UNIQUE CONSTRAINT** **(****SQL UNIQUE CONSTRAINT with SQL DROP command****)** ###

We can remove the rule/constraint from the columns with the help of [SQL DROP command][] as shown below-

我们可以借助[SQL DROP命令][SQL DROP command]从列中删除规则/约束，如下所示-

ALTER TABLE Sales
    DROP CONSTRAINT Unique_sales;

--------------------

## 指向突出显示！ **(****Point to Highlight!****)** ##

As I had mentioned, that Primary key also ensures non-duplicate entries in the database.

如前所述，该主键还可以确保数据库中的条目不重复。

Then, why has the need of using UNIQUE constraint arised??

那么，为什么需要使用UNIQUE约束呢？

Let me answer this question to the simplest terms.

让我用最简单的术语回答这个问题。

SQL Primary Key serves the same purpose (like Unique constraint) of ensuring nonredundant values in the table. But, **a table can have a SINGLE primary key while there can be multiple unique constraints applied to various columns of the table**.

SQL主键具有确保表中非冗余值的相同目的（如唯一性约束）。 但是， **一个表可以有一个SINGLE主键，同时可以对表的各个列应用多个唯一约束** 。

--------------------

## 结论 **(****Conclusion****)** ##

That’s all for this topic. Please feel free to comment below in case you come across any doubt.

这就是本主题的全部内容。 如果您有任何疑问，请随时在下面发表评论。

For more such posts related to SQL, do visit [SQL JournalDev][].

有关与SQL有关的更多此类帖子，请访问[SQL JournalDev][] 。

--------------------

## 参考资料 **(****References****)** ##

[SQL UNIQUE Constraint — Documentation][SQL UNIQUE Constraint _ Documentation]

[SQL UNIQUE约束-文档][SQL UNIQUE Constraint _ Documentation]

> 翻译自: [https://www.journaldev.com/41704/sql-unique-constraint][https_www.journaldev.com_41704_sql-unique-constraint]

t-sql 唯一约束

[SQL]: https://www.journaldev.com/16767/sql
[SQL Constraints]: https://www.journaldev.com/28821/sql-constraints
[SQL Create query]: https://www.journaldev.com/24795/sql-create-table
[NOT NULL]: https://www.journaldev.com/28821/sql-constraints#1-not-null-constraint
[PRIMARY KEY]: https://www.journaldev.com/28821/sql-constraints#3-primary-key-constraint
[SQL ALTER Table]: https://www.journaldev.com/25422/sql-alter-table
[SQL DROP command]: https://www.journaldev.com/25476/sql-drop-table
[SQL JournalDev]: https://www.journaldev.com/sql
[SQL UNIQUE Constraint _ Documentation]: https://docs.microsoft.com/en-us/sql/relational-databases/tables/unique-constraints-and-check-constraints?view=sqlallproducts-allversions#Unique
[https_www.journaldev.com_41704_sql-unique-constraint]: https://www.journaldev.com/41704/sql-unique-constraint