我一直很好奇,但找不到简明的答案。有人能帮忙吗?
CREATE TABLE elements
(
atomic_number INTEGER NOT NULL UNIQUE CONSTRAINT c1_elements
CHECK (atomic_number > 0 AND atomic_number < 120),
symbol CHAR(3) NOT NULL UNIQUE CONSTRAINT c2_elements,
name CHAR(20) NOT NULL UNIQUE CONSTRAINT c3_elements,
atomic_weight DECIMAL(8,4) NOT NULL,
stable CHAR(1) DEFAULT 'Y' NOT NULL
CHECK (stable IN ('Y', 'N'))
);
<unique constraint definition>
为表指定唯一性约束。
<unique constraint definition> ::=
<unique specification> <left paren> <unique column list> <right paren>
| UNIQUE ( VALUE )
<unique specification> ::=
UNIQUE
| PRIMARY KEY
<unique column list> ::= <column name list>
<column name>
in the <unique column list>
is an operand of a grouping
operation. The Syntax Rules of Subclause 9.10, “Grouping operations”, apply.<table definition>
or <alter table statement>
. Let TN be the <table name>
of T.If <unique column list>
UCL is specified, then
a. Each <column name>
in the <unique column list>
shall identify a column of T, and the same column
shall not be identified more than once.
b. The set of columns in the <unique column list>
shall be distinct from the unique columns of any other
unique constraint descriptor that is included in the base table descriptor of T.
c. Case:
i. If the <unique specification>
specifies PRIMARY KEY, then let SC be the <search condition>
:
UNIQUE ( SELECT UCL FROM TN )
AND
( UCL ) IS NOT NULL
ii. Otherwise, let SC be the <search condition>
:
UNIQUE ( SELECT UCL FROM TN )
If UNIQUE (VALUE) is specified, then let SC be the <search condition>
:
UNIQUE ( SELECT TN.* FROM TN )
If the <unique specification>
specifies PRIMARY KEY, then for each <column name>
in the explicit or
implicit <unique column list>
for which NOT NULL is not specified, NOT NULL is implicit in the <column
definition>
.
A <table definition>
shall specify at most one implicit or explicit <unique constraint definition>
that
specifies PRIMARY KEY.
<unique constraint definition>
that specifies PRIMARY KEY is contained in an <add table constraint
definition>
, then the table identified by the <table name>
immediately contained in the containing <alter
table statement>
shall not have a unique constraint that was defined by a <unique constraint definition>
that specified PRIMARY KEY.无。
<unique constraint definition>
defines a unique constraint.<constraint name definition>
and <constraint characteristics>
”, specifies when a constraint is
effectively checked.The unique constraint is not satisfied if and only if
EXISTS ( SELECT * FROM TN WHERE NOT ( SC ) )
is True.
<column definition>
)都必须包含NOT NULL,其<column name>
包含在<unique column list>
中。注255——子句9.10“分组操作”的符合性规则也适用。
欢迎来到SQL标准的奇妙世界!似乎功能T591允许在唯一约束的列中包含可能为空的值(但主键的列不能包含空值)。结果是,当UCL(唯一列列表)中的任何列中存在NULL时,您必须理解以下查询的工作原理:
EXISTS ( SELECT * FROM TN WHERE NOT (
UNIQUE ( SELECT UCL FROM TN ) ) )
And I know I am not sure how the UNIQUE operation works in those circumstances.
<unique predicate>
指定一个测试以确保没有重复的行。
<unique predicate> ::= UNIQUE <table subquery>
<表子查询>
的结果中,每个用户定义类型的列都必须具有比较类型。<表子查询>
中的每一列都是分组操作的操作数。适用于子句9.10“分组操作”的语法规则。无。
<unique predicate>
的结果为True;否则,结果为False。1) 没有功能F291,“UNIQUE predicate”,符合SQL语言不得包含<unique predicate>
。
注193 - 子句9.10“分组操作”的一致性规则也适用。
不可以,一张表不能有多个主键。
但是,它可以有一个复合主键(一个包含多个列定义的主键)。此外,您可以拥有多个唯一约束,这些约束也可以跨越多个列。