none
Using Primary Keys / Indexes RRS feed

  • Question

  • Hi,
    Is it important to have a Primary Key in each table?
    In the below design SelectionID is the PK in UserTeamSelection tbl and it is the FK in
    UserDateSelection tbl.
    The only unique field in UserDateSelection tbl is SelectionID (which is the FK)
    Do I need to add a PK to this table?

    Thanks

    UserTeamSelection Table
    SelectionID int PK
    Username nvarchar(250)
    Teamname nvarchar(250)
    
    
    UserDateSelection Table
    SelectionID int FK
    TeamDate datetime
    Score nvarchar(50)

    Wednesday, December 19, 2012 1:01 PM

Answers

  • A table should ALWAYS have a primary key - unless you have a very good reason for doing otherwise.  For your UserDateSelection table, I suggest you review the purpose of this table.  Though you say that the only unique field is SelectionID, the column names suggest that the primary key might be (SelectionID, TeamDate) - and BTW I think your naming convention stinks but that is irrelevant for discussion purposes.  If what you say is true, then SelectionID is the primary key and this situation forms a 1 to 1 relationship between the two tables, with UserTeamSelection serving as the parent.  I also suggest you think twice about your datatypes.  Teamdate is datetime, not date?  Score is nvarchar(50) rather than some numeric type? 

    And Beware!  The problem with making suggestions based on simple table layouts and very little description is that the responses may be completely wrong or inappopriate for your situation. 

    Wednesday, December 19, 2012 3:18 PM
  • >>>Is it important to have a Primary Key in each table?

    Yes it is.

    >>>Do I need to add a PK to this table?

    Yes,  you can a surrogate  key (an IDENTITY property)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, December 19, 2012 1:19 PM
    Moderator

All replies

  • >>>Is it important to have a Primary Key in each table?

    Yes it is.

    >>>Do I need to add a PK to this table?

    Yes,  you can a surrogate  key (an IDENTITY property)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, December 19, 2012 1:19 PM
    Moderator
  • A table should ALWAYS have a primary key - unless you have a very good reason for doing otherwise.  For your UserDateSelection table, I suggest you review the purpose of this table.  Though you say that the only unique field is SelectionID, the column names suggest that the primary key might be (SelectionID, TeamDate) - and BTW I think your naming convention stinks but that is irrelevant for discussion purposes.  If what you say is true, then SelectionID is the primary key and this situation forms a 1 to 1 relationship between the two tables, with UserTeamSelection serving as the parent.  I also suggest you think twice about your datatypes.  Teamdate is datetime, not date?  Score is nvarchar(50) rather than some numeric type? 

    And Beware!  The problem with making suggestions based on simple table layouts and very little description is that the responses may be completely wrong or inappopriate for your situation. 

    Wednesday, December 19, 2012 3:18 PM
  • I am guessing the design. Use varchar if you don't really need nvarchar.

    CREATE TABLE UserTeamSelection (
    SelectionID int PRIMARY KEY,
    Username nvarchar(50),
    Teamname nvarchar(50),
    UNIQUE (UserName, TeamName) )
    
    
    CREATE TABLE UserDateSelection (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    SelectionID int REFERENCES UserTeamSelection (SelectionID),
    TeamDate datetime,
    UNIQUE (SelectionID, TeamDate),
    Score nvarchar(50));
    


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: SQL Server 2012 Programming

    Wednesday, December 19, 2012 6:15 PM
    Moderator