Solved

Composite primary key

Posted on 2014-09-10
6
157 Views
Last Modified: 2014-09-10
I have the following table
EmployeeTable
EmpId int
CompanyId int
EmpType char(20)
Name varchar(50)
Address varchar(50)
Dob Date
JoinDate Date
.....,,,,
EmpId, companyid, EmpType are the composite primary keys of the table. Should composite primary keys always be at top of the column list and one next to one OR can it be in the any position ? What are the advantages of making composite primary key in top of the column list one next one?
0
Comment
Question by:Varshini S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40314147
As much as I know the order is not important at all so I can't say there is any advantage or disadvantage in having columns ordered but for view purpose only.
0
 
LVL 34

Accepted Solution

by:
ste5an earned 300 total points
ID: 40314164
I'd prefer my key columns to be in top of the column layout. But this is not necessary at all. I makes reading easier, as you can see the relevant columns at a glance.

btw, I don't think that your composite key of EmpId, companyid, EmpType is "correct".
0
 

Author Comment

by:Varshini S
ID: 40314264
ste5an,
Why the composite  key is not correct ?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 total points
ID: 40314280
"correct" and not correct :)

Means that you aren't following the good practices for PK definition. You should avoid description fields. In this case EmpType.
Why you have a CompanyID and don't have a EmpTypeID?
0
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 100 total points
ID: 40314290
the shorter your primary key is, the best it is. You normally select just what it takes to make it unique
0
 
LVL 34

Expert Comment

by:ste5an
ID: 40314346
The table is named Employee. Thus I would expect that each row descibes exactly one employee. But your composite key allows this:

CREATE TABLE #Employees
    (
      EmpId INT ,
      CompanyId INT ,
      EmpType CHAR(20) ,
      Name VARCHAR(50) ,
      Dob DATE ,
      JoinDate DATE ,
      CONSTRAINT PK_Employees PRIMARY KEY ( EmpId, CompanyId, EmpType )
    );

INSERT  INTO #Employees
VALUES  ( 1, 1, 'Manager', 'I am Weasle', GETDATE(), GETDATE() ),
        ( 1, 2, 'CEO', 'I am Weasle', GETDATE(), GETDATE() ),
        ( 1, 3, 'Trainee', 'I are Baboon', GETDATE(), GETDATE() );

DROP TABLE #Employees;

Open in new window


So derived from the naming, I think it's a violation of at least 2NF.

As you're using SQL Server, you should take a look at the HumanResources schema of AdventureWorks.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question