t-sql 唯一限制_SQL唯一限制-終極指南

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 UNIQUE Constraint.

你好！ 希望你們一切都好。 在本文中，我們将揭露SQL中有趣且重要的主題之一-SQL UNIQUE Con​​straint 。

什麼是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限制是一組适用于架構或饋入資料庫結構的資料的規則。 這樣的限制之一就是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查詢建立表并定義以下列：

• 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表查詢以及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指令從列中删除規則/限制，如下所示-

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限制-文檔

翻譯自: https://www.journaldev.com/41704/sql-unique-constraint

t-sql 唯一限制