sql server 中的列级与表级约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9494129/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Column level vs table level constraints in sql server?
提问by mr_eclair
a. Column Level
一种。列级
GO
CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,
ProductName VARCHAR(25)
);
GO
b. Table Level
湾 表级
CREATE TABLE Products
(
ProductID INT,
ProductName VARCHAR(25),
CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
);
GO
Is their any difference between Column level and table level constraints?
它们在列级别和表级别约束之间有什么区别吗？
回答by marc_s
No. It's just a matter of personal taste how you apply the constraint.
不。这只是您如何应用约束的个人品味问题。
The primary key constraint is just a primary key constraint - it always applies to the table(after all: it could contain multiple columns - it cannot be "at the column level").
主键约束只是一个主键约束 - 它始终适用于表（毕竟：它可以包含多个列 - 它不能“在列级别”）。
It's not "at the column level" once or at the "table level" in the other case - it's the same always.
在另一种情况下，它不是“在列级别”一次或在“表级别” - 它始终相同。
Just for fun - you can also create the primary key constraint a third way:
只是为了好玩 - 您还可以通过第三种方式创建主键约束：
(CREATE TABLE statement)
GO
ALTER TABLE dbo.Products
ADD CONSTRAINT PK_Products_pid PRIMARY KEY(ProductID)
and that again would be identical to the two other options you already have.
这与您已有的其他两个选项相同。
回答by onedaywhen
Your first example declares the constraint in line, the second does not. Only simplekeys (involve one attribute) can be declared in line, compoundkeys (involving multiple columns) cannot. But both are table-levelconstraints!
您的第一个示例在 line 中声明了约束，第二个示例没有。只能在行中声明简单键（涉及一个属性），不能声明复合键（涉及多列）。但两者都是表级约束！
There are four logical levels of constraint:
约束有四个逻辑级别：
1) Column level:
1) 列级：
CHECK ( ProductID > 0 )
2) Row level:
2) 行级：
CHECK ( Product_start_date < Product_end_date )
3) Table level (the following example is not yet supported in SQL Server):
3）表级别（SQL Server尚不支持以下示例）：
CHECK ( NOT EXISTS ( SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY ProductID ) AS Tally
FROM Products AS P ) AS DT1
WHERE Tally > 1 ) )
4) Database level (not yet supported in SQL Server):
4) 数据库级别（SQL Server 尚不支持）：
CREATE ASSERTION EnterpriseUniqueIds
CHECK ( NOT EXISTS ( SELECT *
FROM ProductID AS P
JOIN Components AS C
ON C.ComponentID = P.ProductID ) );
A key constraint involves comparing different rows within the same table, therefore it is a table-level constraint.
键约束涉及比较同一表中的不同行，因此它是表级约束。
回答by kalki
回答by siri
There are two ways to define constraints one is at column level and the other is at table level.one can use any of these methods to apply constrains.
有两种方法可以定义约束，一种是在列级别，另一种是在表级别。可以使用这些方法中的任何一种来应用约束。
