Can we Specify 2 identity column in a table.If no why ? if yes why
我们可以在表中指定2个标识列。如果没有原因?如果是,为什么
3 个解决方案
#1
"identity column" is NOT a SQL concept. You must have in mind some specific implementation with its own quirks and extension, NOT SQL in general as your tag asserts; please edit and re-tag your question. (If you happen to mean "Microsoft SQL Server", the answer is "no, because Microsoft thought it made no sense [and for once they were darn right!-)]").
“标识列”不是SQL概念。您必须记住一些具有自己的怪癖和扩展的特定实现,而不是SQL,因为您的标记断言;请编辑并重新标记您的问题。 (如果您的意思是“Microsoft SQL Server”,答案是“不,因为微软认为这没有任何意义[并且一旦他们做得对了! - ]]”)。
#2
Depends what you mean by "identity"
取决于你的“身份”是什么意思
There are several implementation specific constructs such as "rowid" "oid" etc. whose purpose is to uniquly identify a row using a value generated by the DBMS - in most dbms systems you have one and only one of these per row.
有几个特定于实现的构造,例如“rowid”“oid”等,其目的是使用DBMS生成的值单独识别行 - 在大多数dbms系统中,每行只有一个且只有一个。
The 'identity' of a row could also mean the primary key. ANSI sql supports primary keys consisting of many columns, in most DBMSes you can include any or all suitable columns in the primary key. The identity then consists of the combination of all the columns in the primary key -- but and this is a big BUT logically this still constitutes a single identity.
一行的“身份”也可能意味着主键。 ANSI sql支持由许多列组成的主键,在大多数DBMS中,您可以在主键中包含任何或所有合适的列。然后,身份由主键中所有列的组合组成 - 但这是一个很大的但在逻辑上这仍构成一个单一的身份。
You can specify a 'unique' constraint***s*** on any or all suitable columns each constaint could be considered an 'identity' in itself so the answer to your question is probably "Yes" -- but I have never seen a real life implementation of this.
您可以在任何或所有合适的列上指定“唯一”约束*** s ***,每个constaint本身可以被视为“身份”,因此您的问题的答案可能是“是” - 但我从未见过现实生活中的这一点。
#3
Other people's points about 'identity' not being a type in the SQL standard are correct.
其他人关于'身份'不是SQL标准中的类型的观点是正确的。
The IBM Informix Dynamic Server (IDS) supports types SERIAL, SERIAL8 and BIGSERIAL. These are integer types with the extra properties that if you insert a zero into them (or if you do not specify a value to be inserted), then the next higher previously unused value is applied instead. This corresponds loosely to what other DBMS call an identity column. (IDS also provides sequences.)
IBM Informix Dynamic Server(IDS)支持SERIAL,SERIAL8和BIGSERIAL类型。这些是具有额外属性的整数类型,如果向其中插入零(或者如果未指定要插入的值),则会应用下一个更高的先前未使用的值。这与其他DBMS调用标识列的内容大致相对应。 (IDS也提供序列。)
One curiosity (quirk) is that you are allowed to have both a SERIAL column and either a SERIAL8 or a BIGSERIAL column in a single table. Doing so is not recommended and is not sensible; however, it is not prohibited.
一个好奇心(怪癖)是允许您在一个表中同时拥有SERIAL列和SERIAL8或BIGSERIAL列。不建议这样做,并且不明智;但是,它并不是禁止的。
Most DBMS do not allow two separate 'identity' columns in a single table.
大多数DBMS不允许在单个表中使用两个单独的“标识”列。
#1
"identity column" is NOT a SQL concept. You must have in mind some specific implementation with its own quirks and extension, NOT SQL in general as your tag asserts; please edit and re-tag your question. (If you happen to mean "Microsoft SQL Server", the answer is "no, because Microsoft thought it made no sense [and for once they were darn right!-)]").
“标识列”不是SQL概念。您必须记住一些具有自己的怪癖和扩展的特定实现,而不是SQL,因为您的标记断言;请编辑并重新标记您的问题。 (如果您的意思是“Microsoft SQL Server”,答案是“不,因为微软认为这没有任何意义[并且一旦他们做得对了! - ]]”)。
#2
Depends what you mean by "identity"
取决于你的“身份”是什么意思
There are several implementation specific constructs such as "rowid" "oid" etc. whose purpose is to uniquly identify a row using a value generated by the DBMS - in most dbms systems you have one and only one of these per row.
有几个特定于实现的构造,例如“rowid”“oid”等,其目的是使用DBMS生成的值单独识别行 - 在大多数dbms系统中,每行只有一个且只有一个。
The 'identity' of a row could also mean the primary key. ANSI sql supports primary keys consisting of many columns, in most DBMSes you can include any or all suitable columns in the primary key. The identity then consists of the combination of all the columns in the primary key -- but and this is a big BUT logically this still constitutes a single identity.
一行的“身份”也可能意味着主键。 ANSI sql支持由许多列组成的主键,在大多数DBMS中,您可以在主键中包含任何或所有合适的列。然后,身份由主键中所有列的组合组成 - 但这是一个很大的但在逻辑上这仍构成一个单一的身份。
You can specify a 'unique' constraint***s*** on any or all suitable columns each constaint could be considered an 'identity' in itself so the answer to your question is probably "Yes" -- but I have never seen a real life implementation of this.
您可以在任何或所有合适的列上指定“唯一”约束*** s ***,每个constaint本身可以被视为“身份”,因此您的问题的答案可能是“是” - 但我从未见过现实生活中的这一点。
#3
Other people's points about 'identity' not being a type in the SQL standard are correct.
其他人关于'身份'不是SQL标准中的类型的观点是正确的。
The IBM Informix Dynamic Server (IDS) supports types SERIAL, SERIAL8 and BIGSERIAL. These are integer types with the extra properties that if you insert a zero into them (or if you do not specify a value to be inserted), then the next higher previously unused value is applied instead. This corresponds loosely to what other DBMS call an identity column. (IDS also provides sequences.)
IBM Informix Dynamic Server(IDS)支持SERIAL,SERIAL8和BIGSERIAL类型。这些是具有额外属性的整数类型,如果向其中插入零(或者如果未指定要插入的值),则会应用下一个更高的先前未使用的值。这与其他DBMS调用标识列的内容大致相对应。 (IDS也提供序列。)
One curiosity (quirk) is that you are allowed to have both a SERIAL column and either a SERIAL8 or a BIGSERIAL column in a single table. Doing so is not recommended and is not sensible; however, it is not prohibited.
一个好奇心(怪癖)是允许您在一个表中同时拥有SERIAL列和SERIAL8或BIGSERIAL列。不建议这样做,并且不明智;但是,它并不是禁止的。
Most DBMS do not allow two separate 'identity' columns in a single table.
大多数DBMS不允许在单个表中使用两个单独的“标识”列。