Solved

Composite primary key

Posted on 2014-09-10
6
156 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
6 Comments
 
LVL 48

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 33

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

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 33

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using a hta file with MSSQL agent to schedule a script job 4 41
T-SQL 10 35
MS SQL Update query with connected table data 3 41
SQL Dump exec output to table 3 23
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…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

830 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