locked
Is it necessary to have a primary key in each table RRS feed

  • Question

  • Hi Friends

    I am a practicing and learning to make application in JAVA but the backend i am using is SQL Server 2008 R2. I never got chance to develop database. I am developing my Db related to Studentenrollment just to practice.

    My question is that is it necessary to make Primary key in every table.?

    Secondly If no column is elidgible for the Primary key then how should i make a primary key?

    Thanks in advance


    Fighttillend_DBA/DEV

    • Changed type SQL_BOSS Sunday, July 22, 2012 1:27 PM
    Sunday, July 22, 2012 1:03 PM

Answers

  • My question is that is it necessary to make Primary key in every table.?

    It is a best practice in normalized database design to have a primary key on each table along with corresponding foreign key relationships.  You might have exceptions, such as a staging table or similar that is not really part of the normalized design.

    Secondly If no column is elidgible for the Primary key then how should i make a primary key?

    In SQL Server, one typically uses an IDENTITY column to auto-generate primary key values in cases where it's undesirable to use the natural key (which may consist of multiple columns) or when no natural key exists.  Later versions of SQL Server also provide a SEQUENCE object that may be used for this purpose.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, July 22, 2012 2:04 PM
    Answerer

All replies

  • Hi,

    It is not mandatory to have primary key in all tables, however normally it will  be necessary to have primary key in almost all tables and this come handy when you perform update/delete operations where you may need to identify each records uniquely.

    And When you cant find a primary key in a table (either a column or set of columns), you can create a surrogate  as a primary key

    read more about surrogate key here

    http://www.databasejournal.com/features/mssql/article.php/3926216/Generating-Surrogate-Keys-Using-an-Identity-Column-in-SQL-Server.htm

    Regards
    Satheesh

    Sunday, July 22, 2012 1:56 PM
  • My question is that is it necessary to make Primary key in every table.?

    It is a best practice in normalized database design to have a primary key on each table along with corresponding foreign key relationships.  You might have exceptions, such as a staging table or similar that is not really part of the normalized design.

    Secondly If no column is elidgible for the Primary key then how should i make a primary key?

    In SQL Server, one typically uses an IDENTITY column to auto-generate primary key values in cases where it's undesirable to use the natural key (which may consist of multiple columns) or when no natural key exists.  Later versions of SQL Server also provide a SEQUENCE object that may be used for this purpose.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, July 22, 2012 2:04 PM
    Answerer