why cant a table have more then one primary key ?

Question

1.00/5 (2 votes)

See more:

i was just thinking why Microsoft does not allow a user to create more then one primary key and what is the difference between creating a primary key and a unique key not null

Posted 1-Jul-15 22:52pm

Grt.Net

Add a Solution

Comments

Tomas Takac 2-Jul-15 5:09am

I'm not sure if you can create foreign key referencing a non-primary key column. Otherwise there is no difference I guess. In SQL server PK is by default the clustering key but doesn't need to be. You can use any column(s) for clustered key.

Grt.Net 2-Jul-15 5:19am

Yes you can refer to a foreign key which is not a primary key but basically i need to understand the purpose of Microsoft behind making the user create a primary key

Tomas Takac 2-Jul-15 6:51am

IMO PK is a logical concept but physically there is no difference to unique index, honestly why would there be. Using PK you just say that this is how you identify your rows, hence it needs to be unique. There maybe other unique columns but they are no used for identification. I still think the major difference is that PK is clustered by default. For example you cannot create indexes on table variables but you can define a PK so actually you can have a index there.

3 solutions

Solution 1

"Primary" is in the name of the key, you only have one.

You can have as many other keys as you want.

The difference is how the RDBM handles the indexing of those keys.

Posted 1-Jul-15 23:05pm

Mehdi Gholam

Comments

Tomas Takac 2-Jul-15 5:12am

"The difference is how the RDBM handles the indexing of those keys." - please could you elaborate on that? If this is about clustered vs. non-clustered then you can have a non-clustered PK with clustered key on some other column(s).

Mehdi Gholam 2-Jul-15 5:28am

https://en.wikipedia.org/wiki/Database_index

Tomas Takac 2-Jul-15 6:45am

The question was about primary vs. unique keys. I fail to see how the index is different unless of course you assume PK is clustered and the unique AK is non-clustered. But maybe there is something I'm missing.

Grt.Net 2-Jul-15 5:25am

I agree with you @Tomas

phil.o 2-Jul-15 5:36am

What could be useful in having several primary keys, except swallowing up system resources while inserting/deleting?

Mehdi Gholam 2-Jul-15 5:42am

Exactly! you only need on primary key, other keys are a different matter and depend on your usage.

Grt.Net 2-Jul-15 5:42am

as such i don't want to create several primary key i want understand why cant we create it ?

phil.o 2-Jul-15 5:43am

So the answer would be: because the creators of the DBMS did not see any useful reason for that :)

ButtonMoon 6-Jul-15 16:11pm

A key is used for data integrity and identification. On those grounds - integrity and identification - there may be good reasons why you might need more than one. However, there is no difference between a key that is deemed "primary" and one that isn't. Contrary to the suggestion here, a primary key isn't necessarily indexed any differently just because it is a primary key.

Solution 4

a Table cant have more than one primary key but it cant have more than one forein keys.if there are more than one primary keys an error of more than one identity will occur.
this is the solution:

C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace Template.Data
{
   public class CustomerDB
    {
        [Key]
        public int id { get; set; }

        [Required(ErrorMessage = "Enter Num:")]
       // [RegularExpression(@"^\$?\d+(\.(\d{2}))?$")]
        [Display(Name = "Customer name:")]
        public string Name { get; set; }
       [Required]
       [Display(Name = "Customer surname:")]
        public string Surname { get; set; }
         [Required]
       [Display(Name = "Email:")]
        public string email { get; set; }
         [Required]
       [Display(Name = "Cellphone No:")]
        public string cellphone { get; set; }

         [Required(ErrorMessage = "Choose Car Number:")]
        // [RegularExpression(@"^\$?\d+(\.(\d{2}))?$")]
        public int mID { get; set; }
        [ForeignKey("mID")]
        public virtual Moto moto { get; set; }

       // [RegularExpression(@"^\$?\d+(\.(\d{2}))?$")]
        // [Required]
        //[Display(Name = "Insured Customer:")]
        public string insured { get; set; }
         [Required]
       [Display(Name = "Licence Number:")]
       [RegularExpression(@"^\$?\d+(\.(\d{2}))?$")]
        public string licenceNo { get; set; }
         [Required]
        [Display(Name = "Company Name:")]
        public string companyName { get; set; }

       [Display(Name = "Fax No:")]
       [RegularExpression(@"^\$?\d+(\.(\d{2}))?$")]
        public string faxNo { get; set; }

      // [Display(Name = "Uinsured Customer")]
       public string unisured { get; set; }
         [RegularExpression(@"^\$?\d+(\.(\d{2}))?$")]
       [Display(Name = "Report NO:")]
        public string accidentReportNo { get; set; }
       [Display(Name = "Place of accident:")]
        public String placeOfAccident { get; set; }
       [Display(Name = "Police station name:")]
        public string policeStationName { get; set; }
       [Display(Name = "Police station phone:")]
        public string policeStationTelNo { get; set; }
    }
}

Posted 6-Jul-15 4:00am

Member 11799686

Updated 6-Jul-15 4:09am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

F-ES Sitecore · Accepted Answer · 2015-07-01T23:56:00

What can't you have more than one social security number? More than one passport number? More than one driving licence number? The primary key is mainly a logical device it allows anyone looking at the data to know what field is the field that uniquely identifies that record, and when referencing that table that is the field to hold has your reference value. This concept then drives UI design, database diagrams, logical design of the database, code that wants to analyse the database such as Entity Framework. It also lets SQL give you a helping hand when designing your tables as SQL will automatically make that the clustered index too. This allows of easier (lazier) database designs as as long as you define your primary keys a large chunk of performance optimisation is done for you, and you only need to build your own indexes if you have particular requirement too.

A table doesn't have to have a primary key though, you can design one without and SQL doesn't care (ORMs might but that's a different issue). Also while SQL Server does automatically make the PK a clustered index, you have to try and keep keys and indexes separate concepts, they are not related...the PK doesn't have to be a clustered index.