Can we Specify 2 identity column in a table.If no why ? if yes why
+1
A:
"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!-)]").
Alex Martelli
2009-05-21 05:02:53
Come on, having two identity columns with different seeds and increments would be fun! In all seriousness, maybe he is talking about primary keys?
Gromer
2009-05-21 05:04:50
Spanning pks that is.
Gromer
2009-05-21 05:05:14
+1
A:
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.
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.
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.
James Anderson
2009-05-21 05:12:03
A:
Other people's points about 'identity' not being a type in the SQL standard are correct.
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.)
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.
Most DBMS do not allow two separate 'identity' columns in a single table.
Jonathan Leffler
2009-05-21 05:54:55