Tags: column, database, foreign, greetings, identity, joining, key, microsoft, mysql, oracle, primary, relationships, server, sql, tables, user_id

Identity column as a primary key with foreign key relationships

On Database » Microsoft SQL Server

13,765 words with 8 Comments; publish: Mon, 02 Jun 2008 00:51:00 GMT; (25078.13, « »)

Greetings All,

We want to have a user_id which will be a primary key and all other tables

will be joining on this user_id. My question is

Option 1: Have user_id an identity and have foreign key on this identity

table.

Option 2: Dont use identity as user_id and generate user_id with some logic.

Our group is having mixed opion and we will go with maximum number of

suggestions we get here. Also please include why you think the option you

selected is right.

Thanks,

Arshad

--

Arshad

arshadmd-nospam.sqlserver.todaysummary.com.gmail.com

All Comments

Leave a comment...

  • 8 Comments
    • IDENTITY all the way unless you have good reason for genrerating your

      own ID. What justification /resoning do you have for not using it?

      #1; Mon, 02 Jun 2008 00:52:00 GMT
    • Arshad,

      Unfortunately you will also get mixed opinions here. You may read a

      response from Celko where he will insult you and insist that you never

      use an identity column as a primary key. Others will tell you that it

      is OK. I've been through a similar issue and here is what I've learned:

      1) If your project will ever be moved to a system like Oracle, you

      don't want to use Identity columns as primary keys. If you will never

      move your db to another system other than SQL, then using an identity

      column as a primary key is OK.

      2) I personally have used identity columns as primary keys in many

      different scenarios and I have never had a problem with it.

      3) Creating a primary key with a stored procedure or some other logic

      will work but it only creates more work in coding for you and adds

      additional bulk to the table that is not necessary because you will

      still have the unique identity as well as a unique primary key.

      4) Using multiple columns to create your primary key (social sec. and

      birthday) is possible, but that can lead to confusi table relationships

      and additional bulk to each related table. I like my table

      relationships to be simple and intuitive, which is why I use identity

      columns as primary keys.

      The bottom line is that you cannot port your db to other systems if you

      do this, but if porting your code to another system is not likely to

      happen, then the decision is a matter of preference for the developers.

      I find using identity columns as primary keys keeps the design simple

      and does not add needless bulk and clutter.

      #2; Mon, 02 Jun 2008 00:53:00 GMT
    • Use an autogenerated Primary key of type integer for a primary key on which

      you build relationships. If you also want to use this primary key as a

      userId by which to find records this is fine say you want this to be a

      customer number. However having a customer 1 followed by a customer 2 etc,,

      may not be a good idea if these codes are used to allow any customer access

      to the database, say via internet. Its a better idea in such a case to have

      non-sequential customer identifications. So a combination of RecordId as the

      primary key being automatically genereated and used for relations PK-FK and

      a separate USERID field that you generate and that must also be unique is

      probably better. One way to generate a non sequential ID is by using a

      default value derived as follows. (rand((datepart(month,getdate()) * 100000

      + datepart(second,getdate()) * 1000 + datepart(millisecond,getdate()))) *

      1000000000). This generates a random number based on the seeds of the

      computer date, you can ensure that its unique by making the column a unique

      index.

      In my view the primary key should never be part of data being viewed or used

      directly by the end user and should always be created automatically by the

      database engine and should ONLY be used for that particular purpose. Other

      than that the basic most generally accepted rule is make the database engine

      itself do as much of the work as possible for ensuring data correctnes,

      there is also the fact that there are practical situations in which you may

      wnt to change a userid, if the userid is the primary key you have to do

      cascading updates and this takes time. If your PK is totally outside of what

      users may be allowed to use, there are no cascades involved for updates,

      ever.

      Also, having the database do the work, removes the onus on the individual

      programmer of having to call procedures specifically for creating

      Primarykeys or ensuring relationship constraints are correctly applied. If

      you do this in code you WILL make mistakes and you WILL forget to call the

      procedures sometime.

      I've seen many apps that do this in code (still today) and have had to

      transfer data from these to corrrectly structured relational tables and each

      time I have found things like orphan records or duplicate keys where none

      should be.

      Hope ths helps and that I haven't started anyone ranting and raving :-)

      RD

      "Arshad" <Arshad.sqlserver.todaysummary.com.discussions.microsoft.com> wrote in message

      news:19DC4A02-8C08-4F99-BB86-56CD9309892D.sqlserver.todaysummary.com.microsoft.com...

      > Greetings All,

      > We want to have a user_id which will be a primary key and all other tables

      > will be joining on this user_id. My question is

      > Option 1: Have user_id an identity and have foreign key on this identity

      > table.

      > Option 2: Dont use identity as user_id and generate user_id with some

      > logic.

      > Our group is having mixed opion and we will go with maximum number of

      > suggestions we get here. Also please include why you think the option you

      > selected is right.

      > Thanks,

      > Arshad

      > --

      > Arshad

      > arshadmd-nospam.sqlserver.todaysummary.com.gmail.com

      #3; Mon, 02 Jun 2008 00:54:00 GMT
    • On Fri, 29 Jul 2005 08:50:01 -0700, Arshad wrote:

      >Greetings All,

      >We want to have a user_id which will be a primary key and all other tables

      >will be joining on this user_id. My question is

      >Option 1: Have user_id an identity and have foreign key on this identity

      >table.

      >Option 2: Dont use identity as user_id and generate user_id with some logic.

      >Our group is having mixed opion and we will go with maximum number of

      >suggestions we get here. Also please include why you think the option you

      >selected is right.

      >Thanks,

      >Arshad

      Hi Arshad,

      In general, keys can fulfill two distinct functions.

      Their first function is to provide a link between a row in a table and

      an entity in the real world outside of the database. This is what I call

      the business key, since in most cases, the business dictates what key to

      use. If the users are employees and the HR department issues employee

      numbers, than the business key is the employee number. In an American

      tax-related database, SSN would be the business key. In a database that

      supports the upkeep of a computer network, the username assigned by the

      sysadmins for logging on to the network would be the business key. For

      my dentists' customers, last name + address + date of birth might

      qualify as the business key. And so on, and so on.

      You should only consider having the database generate the business key

      if there is at present no business key - and you'll still need to find

      who'se in charge and get him or her to sign of on your proposal, since

      it's not your job to change the business' processes.

      The second function of a key is to link a row in one table to a related

      row in (usually) another table - the well known FOREIGN KEY constraint.

      In most cases, the FOREIGN KEY will refer to the PRIMARY KEY of the

      related table. But it can also refer to any column (or combination of

      columns) that is declared as UNIQUE in the related table.

      My usual procedure is:

      - First, find the business key, This one is always needed, since there

      is no sense in storing data in a database if it can't be related back to

      the real-world entities that it's supposed to describe.

      - Second, determine of there will be any other tables referring to the

      rows in this table. If there are, then determine if the business key is

      a good condidate for implementing the FOREIGN KEY constraint. If it

      isn't (e.g. becuase it is prone to frequent change, or because it is so

      long that it would degrade performance in the database), then I'll

      introduce a surrogate key - and in 99.9% of all cases, IDENTITY serves

      fine as a surrogate key.

      That leaves me with two possible designs:

      1. Business key is suitable to be used in the FK relationship:

      CREATE TABLE Tab1 (BusinessKey some_datatype NOT NULL,

      other columns,

      PRIMARY KEY (BusinessKey)

      )

      CREATE TABLE Tab2 (BusinessKeyForOtherTable other_datetype NOT NULL,

      FK_To_Tab1 some_datetype [NOT] NULL,

      other columns,

      PRIMARY KEY (BusinessKeyForOtherTable)

      FOREIGN KEY (FK_To_Tab1)

      REFERENCES Tab1 (BusinessKey)

      ON UPDATE CASCADE

      ON DELETE NO ACTION

      )

      2. Business key is not suitable for FK relationship - use surrogate key:

      CREATE TABLE Tab1 (Tab1_ID int NOT NULL IDENTITY,

      BusinessKey some_datatype NOT NULL,

      other columns,

      PRIMARY KEY (Tab1_ID),

      UNIQUE (BusinessKey)

      )

      CREATE TABLE Tab2 (Tab2_ID int NOT NULL IDENTITY,

      BusinessKeyForOtherTable other_datetype NOT NULL,

      FK_To_Tab1 int [NOT] NULL,

      other columns,

      PRIMARY KEY (Tab2ID),

      UNIQUE (BusinessKeyForOtherTable)

      FOREIGN KEY (FK_To_Tab1)

      REFERENCES Tab1 (Tab1_ID

      ON UPDATE NO ACTION -- Note this change!!

      ON DELETE NO ACTION

      )

      Best, Hugo

      --

      (Remove _NO_ and _SPAM_ to get my e-mail address)

      #4; Mon, 02 Jun 2008 00:55:00 GMT
    • Arshad wrote:

      > Greetings All,

      > We want to have a user_id which will be a primary key and all other tables

      > will be joining on this user_id. My question is

      > Option 1: Have user_id an identity and have foreign key on this identity

      > table.

      i use this method 99% of the time and have no problems.

      the bigger issue for me is what should be the clustered index on the

      table.

      #5; Mon, 02 Jun 2008 00:56:00 GMT
    • Arshad:

      Identities do make things easier, but they do bring along some extra

      baggage that you need to be aware of.

      As Elroyskimms suggested problems regarding another vendor's DBMS

      (although there is a work around in Oracle w/ sequences), you will also

      need to consider if you ever plan on any type of replication or merging

      of data between databases, as the uniqueness of the key does not exist in

      this scope.

      If you skin is thick enough :>), I'd recommend posting this to

      microsoft.public.sqlserver.programming, and await the verbal assault from

      Joe Celko. If you can get past his insults and obtuse style, he does

      have a good insight on "some" issues.

      Jeff Clausius

      SourceGear

      =?Utf-8?B?QXJzaGFk?= <Arshad.sqlserver.todaysummary.com.discussions.microsoft.com> wrote in

      news:19DC4A02-8C08-4F99-BB86-56CD9309892D.sqlserver.todaysummary.com.microsoft.com:

      > Greetings All,

      > We want to have a user_id which will be a primary key and all other

      > tables will be joining on this user_id. My question is

      > Option 1: Have user_id an identity and have foreign key on this

      > identity table.

      > Option 2: Dont use identity as user_id and generate user_id with some

      > logic.

      > Our group is having mixed opion and we will go with maximum number of

      > suggestions we get here. Also please include why you think the option

      > you selected is right.

      > Thanks,

      > Arshad

      >

      #6; Mon, 02 Jun 2008 00:57:00 GMT
    • Jeff Clausius wrote:

      > If you skin is thick enough :>), I'd recommend posting this to

      > microsoft.public.sqlserver.programming, and await the verbal assault from

      > Joe Celko. If you can get past his insults and obtuse style, he does

      > have a good insight on "some" issues.

      I couldn't have said it better myself!

      -E

      #7; Mon, 02 Jun 2008 00:58:00 GMT
    • When I create a diagram, and thereby make constraints, it is nice to

      have a singe field to link to that is unique. If there isn't a single

      field that makes it unique then and Identity is the perfect choice.

      Since I now have this unique key, why not have it the primary key?

      This ID is never going to change, nor can it be changed because of the

      constraints, with records from other tables referencing it. What I

      really despise is when two tables are linked together by more than one

      field. If I want to make other unique key constraints I can, but for

      consistency I always make the Identity column the primary key.

      Another thing I like to do is always name the Identity column ID. That

      way I immediately know it is the identity and the primary key for the

      table I am looking at. Now lets say the name of the table is

      "Department". Now when I link it to the Department Table to the

      Employee table, I create a field in the Employee table called

      "DepartmentID", and link it to the ID column in the Department table.

      So I always know that a field that just ends in "..ID" references the

      Primary Key and Identity of another table, and I know the name of the

      table, because it is what is in front of "ID". I never have to wonder

      or look at constraints to see if a field is referencing another table.

      I always know what table and field it references just by the name of the

      field. Even those who hate identity columns admin there are times that

      they make sense. I say if you are ever going to use them, always use

      them. Consistency is the key to making things simple.

      Ken Cushing

      West Valley City

      kcushing

      ---

      Posted via http://www.mcse.ms

      ---

      View this thread: http://www.mcse.ms/message1761833.html

      #8; Mon, 02 Jun 2008 00:59:00 GMT