Re: SQL Identity column

Hi Doug,

it's "best practice" to always use the identity column as the primary key. But it's also best practice to always add an unique business key to your table by creating an unique index over that column or set of columns.

The ID column is perfect for foreign keys from "child tables" and all kinds of joins, as you only have to use one column. And the unique business key is used for the access by users.

But theoretically - the ID column doesn't have to be the primary key - but as Charles pointed out, then you should add a unique index of the ID column to guarantee the only unique IDs are generated and stored.

But as I said - that's not best practice.

HTH and kind regards,
Daniel

Am 10.02.2025 um 23:19 schrieb Charles Wilt <charles.wilt@xxxxxxxxx>:

IIRC you can do what you are suggesting...

But it's important that you do create that unique index as the DB doesn't
automatically ensure uniqueness on an identity column.

Should be easy enough to test.

HTH,
Charles

On Mon, Feb 10, 2025 at 3:00 PM DEnglander--- via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

In all of the examples and presentations I have seen, when a DDL table has
an Identity column, it seems to be the Primary key on the table.

My question is: When a table has an Identity column, does the identity
column always have to be defined as the Primary key, or can another column
in the table be defined as a Primary key, and an Index built on the table
specifying the Identity column as being unique?

Thank you,

Doug



"CONFIDENTIALITY NOTICE: This e-mail transmission (and/or the attachments
accompanying it) contain confidential information belonging to the sender.
The information is intended only for the use of the intended recipient. If
you are not the intended recipient, you are hereby notified that any
disclosure, copying, distribution or the taking of any action in reliance
on the contents of the information is strictly prohibited. Any
unauthorized interception of this transmission is illegal under the law.
If you have received this transmission in error, please promptly notify the
sender by reply e-mail, and then destroy all copies of the transmission."
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.