Alternate names for a surrogate key/sequence number/ID column

I have a legacy table that has as a part of its natural key a column named <table_name>_IDENTIFIER and it seems like it would be confu开发者_如何学Pythonsing to create a surrogate key named <table_name>_ID or ID so I'm leaning towards naming it SURROGATE_KEY. All my other tables use the <table_name>_ID syntax. Any better suggestions?

Don't call it SURROGATE_KEY. That is meaningless in any other context. I'd stick with <table_name>_ID. Yes it's a little confusing. But, given your established convention, anything else would be confusing too.

I might suggest that you go with your standard: <table_name>_ID

Eventually, the legacy table will not be the driving force, and it will be the IDENTIFIER column that will look odd, which is what you want, as opposed to that - 'oh yeah, i need to use surrogate_key for that thing instead of id...' moment.

First, I would not include the table name in my columns. A column is an attribute which requires the context of the entity to which it belongs. Having a "name" for example without the context to which it belongs is of no use. You need to know it is a Person's name or a Company name etc. and you have that in the name of the entity itself. Thus, I would not prefix columns with the name of the table in which it is declared.

That leaves you with choices like "Id", "Key", "SurrogateKey", or perhaps "SystemId" which are all equally vague. At least "SurrogateKey" describes what it is which is a bonus. That name will make sense to a DBA but perhaps not a developer (although they should understand the concept). Of those choices, I'd be inclined to use "Id" and find a way to change <table_name>_Identifier to something more descriptive.

In Data Modelling world during drawing ER model, Surrogate key like SURROGATE_KEY (or SURROGATE_ID) will definitely cause pain side-effects when creating Foreign Key Constraint.

I.e. linking parent with child in majority of DM tools via dragg-n-dropping primary key will automatically create identical column in a child generating dups in column names.

To avoid that as a rule of thumb, naming Surrogate key like Table_name.Table_name_ID or Table_name._ID can be good option.

Agreed . . . SURROGATE_ID is not recommended. What all the suggestions seem to be lacking is at the very heart of data management & data modelling best practices: establishing (& consistently using!) naming conventions & value domains. Suggestions:
1. If the database or programming protocol (like .NET which abhors natural primary keys as I've been lead to understand) requires a single, meaningless, integer assigned as a primary key -- a surrogate -- key, then create a value domain of "Id" & define it as data type integer with description of surrogate primary key. 2. When naming attributes/columns, the ONLY columns using the domain "Id" would be surrogate (primary) key columns populated with assigned integer values. No other attributes/columns would be allowed to use the domain "Id", so it would be absolutely clear from the attribute/column name the nature of the values stored AND how those values are begin utilized. Thanks for the opportunity!