SQL中的键和约束或唯一键与唯一约束之间有什么区别？

两者相似，但不完全相同。也不会在constraints文件夹中自行创建约束。在SQL Server中的表上使用主键时，可以添加约束子句。如果不这样做，SQL Server将自动创建约束;认为这是一个内部捷径。 SQL Server将通过在主键上使用聚簇索引来为您管理此快捷方式;它将自动对主键设置唯一约束，并且不允许主键为空。

The two are similar, but not quite the same. Neither will create constraints, on their own in the constraints folder. When you use a primary key on a table in SQL Server, you can either add the constraint clause or not. If you do not, SQL Server will automatically create the constraint; consider it an internal short-cut. SQL Server will manage this short-cut for you by using a clustered index on the primary key; it will automatically put a unique constraint on the primary key, and not allow the primary key to be null.

CREATE TABLE Employee
(
  EmployeeID INT PRIMARY KEY,
  SSN varchar(9) NOT NULL
);

如果添加约束条款，则有更多权力来控制主要键;例如，在主键上具有多列主键或甚至具有不同类型的索引。现在，如果使用索引（群集或非群集）添加约束，则约束将进入keys文件夹。如果创建不使用索引的约束，则约束进入约束文件夹。

If you add the constraint clause, you have more power to control the primary key; such as having a multi-column primary key or even having a different type of index on the primary key. Now if you add a constraint with an index, clustered or non-clustered, then the constraint goes into the keys folder. If you create a constraint that does not use an index, then the constraint goes into the constraints folder.

CREATE TABLE Employee
(
  EmployeeID INT NOT NULL,
  SSN varchar(9) NOT NULL,
  CONSTRAINT pk_EmployeeID_SSN
    PRIMARY KEY CLUSTERED (EmployeeID, SSN)
);

如果你查看SQL Server，你现在在keys文件夹下有一个pk_EmployeeID_SSN主键的显示。



现在你想知道为什么没有显示在约束文件夹中？原因是SQL Server将索引约束耦合到keys文件夹中。提供没有键的约束的示例。请看下面。

If you look in SQL Server, you now have a display for the pk_EmployeeID_SSN primary key, under the keys folder.

So now you wonder why nothing is showing in the constraints folder? The reason is SQL Server couples indexed constraints into the keys folder. To provide an example of a constraint that has no key. Please see below.

CREATE TABLE Employee
(
  EmployeeID INT NOT NULL,
  SSN varchar(9) NOT NULL,
  AddDate datetime NOT NULL
);
ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [DF_Employee_AddDate]  DEFAULT (getdate()) FOR [AddDate];

insert into employee(employeeid, ssn) values(1, '111111111');
insert into employee(employeeid, ssn) values(2, '222222222');

如果你运行它，你会看到DF_Employee_AddDate已被添加到约束文件夹。这个约束会自动将当前日期时间添加到进入表格的任何新记录中。



如果您对它们如何感兴趣，我在下面列出了几个例子。工作。

If you run this, you'll see the DF_Employee_AddDate has been added to the constraints folder. This constraint will automatically add current datetime to any new record that goes into the table.

I have put together a few more examples below, if you are interested on how they work.

CREATE TABLE Employee1
(
  EmployeeID1 INT PRIMARY KEY,
  SSN varchar(9) NOT NULL
);

CREATE TABLE Employee2
(
  EmployeeID2 INT NOT NULL,
  SSN varchar(9) NOT NULL,
  CONSTRAINT pk_EmployeeID2_SSN
    PRIMARY KEY CLUSTERED (EmployeeID2, SSN)
);

CREATE TABLE Employee3
(
  EmployeeID3 INT PRIMARY KEY,
  SSN varchar(9) NOT NULL,
  CONSTRAINT unq_SSN
    UNIQUE NONCLUSTERED (SSN)
);

嗨Gaurav



主键是唯一的表上的字段，每个表只能分配一个，这通常类似于EmployeeID，CustomerID或您尝试制作主键的任何字段。这意味着其他表可以使用此字段为自己创建外键关系。



例如查看下表：



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

/ *创建供应商表* /

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

创建表供应商（

SupplierID int not null主键，

SupplierName varchar（50）null，

ContactName varchar（50）null，

地址varchar（50）null，

城市varchar（50）null，

PostalCode varchar（50）null，

国家varchar（20）null，

电话varchar（10）null，

）

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

/ *创建产品表* /

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

创建表产品（

ProductID int not null主键，

ProductName varchar（50）null，

SupplierID int not null，

CategoryID int not null，

单位int null，

价格int null，

外键（SupplierID）参考供应商（SupplierID）



您可以看到两个表都有一个唯一的主键，但Products表有一个引用supplier表的外键。这会在两个表之间建立关系。



唯一约束只是意味着特定字段必须是唯一的。



希望帮助

Hi Gaurav

A primary key is a unique field on a table and can only be assigned one per table usually this is something like EmployeeID, CustomerID or whatever field you are trying to make your primary key. That means that other tables can use this field to create foreign key relationships to themselves.

for example look at the below tables:

-------------------------------
/* Create Supplier Table*/
-------------------------------
Create table Supplier(
SupplierID int not null primary key,
SupplierName varchar(50) null,
ContactName varchar(50) null,
Address varchar(50) null,
City varchar(50) null,
PostalCode varchar(50) null,
Country varchar(20) null,
Phone varchar(10) null,
)
---------------------------------
/* Create Products Table*/
---------------------------------
create table Products(
ProductID int not null primary key,
ProductName varchar(50) null,
SupplierID int not null,
CategoryID int not null,
Units int null,
Price int null,
foreign key (SupplierID) References Supplier (SupplierID)

you can see both tables have a primary key that is unique but the Products table has a foreign key that references supplier table. This creates a relationship between the two tables.

A unique constraint simply means that a particular field must be unique.

hope that helps

嘿兄弟，感谢你的响应。这意味着主键意味着组合

[

1.）键（在物理文件夹中生成）+

2.）index（clustered）或创建的非聚集）+

3.）唯一约束（应用于索引）]。



和主要约束只表示第3点的约束

我是否正确。

你真的很好地描述了男人。谢谢。

Hey bro , thanx for your response . this mean primary key mean combination of
[
1.) key(that generated in folder physically)+
2.) index(clustered or nonclustered that created)+
3.) Unique constraints(which is applied on index )] .

and primary constraint mean only that constraint which is point No. 3
Am i getting right .
you describe really in a good manner man . Thank You.