Composite primary key

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?
Varshini SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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
ste5anSenior DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Varshini SAuthor Commented:
ste5an,
Why the composite  key is not correct ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
"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
Éric MoreauSenior .Net ConsultantCommented:
the shorter your primary key is, the best it is. You normally select just what it takes to make it unique
0
ste5anSenior DeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.