空的 SQL 表是否有超键?每个 SQL 表都有一个吗? [英] Does an empty SQL table have a superkey? Does every SQL table have one?
问题描述
我知道超键在 SQL 中的含义，但我有以下问题:
I know what a superkey means in SQL but I have the following questions:
- 空的 SQL 表是否总是有一个超级键?
- 每个 SQL 表是否总是有一个超级键?
推荐答案
TL;DR是一个RM(数据关系模型)术语.SQL 中没有标准用法.SQL 表的超键可以合理地非正式地称为列集，您可以声明 primary key 或 unique not null，加上 {}最多一行(虽然你不能声明它).合理地非正式"因为 SQL 表不是 RM 关系.但是如果一个表没有重复的行也没有空值，那么我们可以合理地说它是一个关系，并且像每个关系一样，它有一个或多个超级键.基本关系或关系表达式的超键的定义考虑了它可以容纳的所有可能的值，所以它的当前值不会影响它的超键是什么.根据超键的定义，在空关系值中，每个属性子集都是一个超键.
TL;DR "Superkey" is a RM (Relational Model of Data) term. There's no standard use in SQL. The superkeys of an SQL table might reasonably informally be said to be the column sets that you could declare primary key or unique not null, plus maybe {} when a table holds at most one row (although you can't declare it). "Reasonably informally" because SQL tables are not RM relations. But if a table holds no duplicate rows and no nulls then we can reasonably say that it is a relation, and that, like every relation, it has one or more superkeys. The definition of superkey of a base relation or relation expression takes into account all possible values it can hold, so its current value doesn't affect what its superkeys are. Per the definition of superkey, in an empty relation value every subset of attributes is a superkey.
关系超级键"
在数学中，关系"的一种含义是是类似表格的一组类似行的元组"那是值列表.它代表一种关系(船舶)/关联——也称为关系"；在数学中.这就是R"所在的地方.在RM"中关系数据库"一词来自于此.来自.(Codd 1970) (2015 年日期) 同样"ERM"(实体-关系模型)来自关系"作为关系/关联.(陈 1976) 在 RM 上下文中，关系"指的是关系".也是类似表格的，但通常包含一组元组"它们是属性"对的集合名字和值.(或者它可能是一个数学关系或混合.)超级键"有两种 RM 意义——关系值 &关系变量或表达式.关系值的超键是一组属性，其中关系不包含具有该子元组的两行.关系变量或表达式的超键是一组属性，其中在每种情况/状态下它不包含具有该子元组的两行.因此，当一个变量可以保存的所有值都具有该超键时，它就有一个特定的超键.
In mathematics one meaning of "relation" is a table-like set of row-like "tuples" that are lists of values. It represents a relation(ship)/association--also called a "relation" in mathematics. That is where the "R" in "RM" comes from, which is where the term "relational database" comes from. (Codd 1970) (Date 2015) Similarly "ERM" (Entity-Relationship Model) comes from "relationship" as relation/association. (Chen 1976) In a RM context a "relation" is also table-like but usually holds a set of "tuples" that are sets of pairs of "attribute" names & values. (Or it might be a math relation or a mix.) There are two RM senses of "superkey"--of a relation value & of a relation variable or expression. A superkey of a relation value is a set of attributes where a relation does not contain two rows with that subtuple. A superkey of a relation variable or expression is a set of attributes where in every situation/state it does not contain two rows with that subtuple. So a variable has a certain superkey when all the values it can hold have that superkey.
(在已出版的学术教科书中找到定义.请注意，当定义说for each"或for all"值时，它们意味着当没有这样的值时满足这样的条件.类似地，当对于某些"和存在"是指命名的值，它们并不意味着名称必须命名不同值.)
(Find a definition in a published academic textbook. Note that when definitions say "for every" or "for all" values for a name they mean that such a condition is satisfied when there is no such value. Similarly when "for some" & "there exist(s)" refer to named values they don't mean that the names necessarily name different values.)
一个空的值恰好将每个属性子集都作为超键.变量或涉及变量的表达式的超键定义考虑了它可以评估的所有可能值，因此其当前值不会影响其超键是什么.
An empty value happens to have every subset of attributes as a superkey. The definition of superkey of a variable or expression involving variables takes into account all possible values it can evaluate to, so its current value doesn't affect what its superkeys are.
每个关系都有一个或多个超键:一个关系持有一组元组，所以一个元组值最多出现一次，所以所有属性上的一个子元组的值最多出现一次，所以所有属性的集合是超键.
Every relation has one or more superkeys: A relation holds a set of tuples, so a tuple value appears at most once, so a value for a subtuple on all attributes appears at most once, so the set of all attributes is a superkey.
SQL 与关系
SQL 表不是关系.它让人想起一堆乱七八糟的数学&允许具有重复和空值的属性关系.所以 SQL 数据库被称为关系型"数据库.但他们对 RM 的体现很差.
An SQL table is not a relation. It is reminiscent of a jumble of math & attribute relations with duplicates and nulls allowed. So SQL databases are called "relational" but they poorly embody the RM.
由于 SQL 表与关系的相似性，涉及关系的术语被草率地应用于表.但是虽然你可以借用术语并赋予它们 SQL 含义——值、表、FD(函数依赖)、superkey、CK(候选键)、PK(主键)、FK(外键)、join、和、谓词、NF(标准形式)、规范化、1NF 等——你不能仅仅用那些 SQL 含义代替 RM 定义、定理或算法中的那些词，并得到一些合理或真实的东西.此外，RM 概念的 SQL 演示几乎从未实际上告诉您如何将 RM 概念完美地应用于 SQL 数据库.他们只是模仿 RM 演示文稿，不知道他们对术语的 SQL 含义的使用是否会使事情变得荒谬或无效.(几乎"，因为我希望有一些.)
Because of the resemblance of SQL tables to relations, terms that involve relations get sloppily applied to tables. But although you can borrow terms and give them SQL meanings--value, table, FD (functional dependency), superkey, CK (candidate key), PK (primary key), FK (foreign key), join, and, predicate, NF (normal form), normalize, 1NF, etc--you can't just substitute those SQL meanings for those words in RM definitions, theorems or algorithms and get something sensible or true. Moreover SQL presentations of RM notions almost never actually tell you how to soundly apply RM notions to an SQL database. They just parrot RM presentations, oblivious to whether their use of SQL meanings for terms makes things nonsensical or invalid. ("Almost" because I hope there are some.)
如果你替换relation"通过表"(允许重复和/或空值)在 certain RM 超键 definitions 中，然后您将获得 SQL 超键的定义作为满足 主键 或 unique not null 约束.对于某些other RM 超键定义，当表最多包含一行时，您会得到这些集合加上 {}.(因为它识别"任何行.)(您可能只会发现使用 2nd-style phrasing 但认为它定义了 1st-style phrasing 的作用的人.他们不会知道他们通过误解术语来滥用定义.)有些人可能只是使用约束定义.你可能会发现英国"(唯一键)用于三个中的任何一个.
If you replace "relation" by "table" (duplicates and/or nulls allowed) in certain RM superkey definitions then you get a definition for SQL superkey as a column set that satisfies a primary key or unique not null constraint. For certain other RM superkey definitions you get those sets plus {} when a table holds at most one row. (Since it "identifies" any row.) (You will probably only find people who use a 2nd-style phrasing yet think it defines what a 1st-style phrasing does. And they won't know they are misusing definitions by misinterpreting terms.) Some might just use the constraint definition. You might find "UK" (unique key) used per any of the three.
当一个表既不包含重复行也不包含空值时，我们可以将其解释为一个关系，其中行为元组 &列作为属性.那么我们可以合理地说表的超键就是关系的超键.
When a table holds neither duplicate rows nor nulls we can interpret it as a relation, with rows as tuples & columns as attributes. Then we can reasonably say the table's superkeys are the relation's superkeys.
1NF"没有单一的含义.标准化"也没有.或非规范化"或UNF"或0NF"或就此而言，关系".
建模和归一化时如何处理空值?
"1NF" has no single meaning. Nor does "normalized" or "unnormalized" or "UNF" or "0NF" or for that matter "relation".
What to do with null values when modeling and normalizing?
PS:CK" 不要将超级键与 CK 混淆.CK 是不包含更小的超级密钥的超级密钥.(因此，我们说 CK 是最小的"或不可约的"超键.)一个关系可以有多个超键CK.PK 是一些被选择作为 PK 的 CK.SQL 主键 &unique not null 声明我们可以称之为 SQL 超级键的东西，但不一定是最小的，我们可以称之为 SQL CK 的东西.所以当你听到PK"时在 SQL 上下文中，您必须找出它是否意味着通过 primary key 声明的(SQL 超级键)列列表(可能是也可能不是 SQL CK)"；和/或可分辨的 SQL 超级键(可能或可能未通过 primary key 声明)"；和/或可分辨的 SQL CK(可能或可能未通过 primary key 声明)".而且你总是必须问什么是关键"?方法.通常，SQL 超级键——不管这意味着什么.
PS: "CK" Don't confuse superkeys with CKs. A CK is a superkey that contains no smaller superkey. (Hence, we say that a CK is a "minimal" or "irreducible" superkey.) A relation can have multiple superkeys & CKs. A PK is some CK chosen to be distinguished as PK. SQL primary key & unique not null declare what we could call a SQL superkey, but not necessarily a minimal one, what we could call an SQL CK. So when you hear "PK" in an SQL context you have to find out whether it means "(SQL superkey) column list declared via primary key (maybe or maybe not a SQL CK)" and/or "distinguished SQL superkey (maybe or maybe not declared via primary key)" and/or "distinguished SQL CK (maybe or maybe not declared via primary key)". And you always have to ask what "key" means. Usually, SQL superkey--whatever that means.
PS:关系(船)" 直接理解每个关系"的意思&关系"——关联?桌子?FK?在 RM 数据库中，每个关系值(变量或表达式的)代表一个关系(船)/关联.但是关系"(有时，关系")也(错误地)(以根深蒂固的方式)用于 FK——不是在 RM 或 ERM 中，而是在 伪 RM &-错误解释/误解/曲解它们的ERM方法，其根源早于它们.(不幸的是，数据库行业的 RM 教育非常差.)FKs、PKs、CKs、superkeys &其他约束不需要查询&更新.(他们是为了诚信.)
PS: "Relation(ship)" Get straight what you mean by each of "relation" & "relationship"--association? table? FK? In a RM database every relation value (of a variable or expression) represents a relation(ship)/association. But "relationship" (sometimes, "relation") is also (mis)used (in an entrenched way) for FK--not in the RM or ERM, but in pseudo-RM & -ERM methods that misinterpret/misunderstand/misrepresent them, whose roots predate them. (Unfortunately there is very poor RM education in the database industry.) FKs, PKs, CKs, superkeys & other constraints are not needed to query & update. (They are for integrity.)
这篇关于空的 SQL 表是否有超键?每个 SQL 表都有一个吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
