locked
Primary key is not defined in the table? RRS feed

  • Question

  • Hello team,

    I have a table as below:

    CREATE TABLE [Rates]
    (
     [VestedIndividualRate] [float] NULL,
     [VestedFamilyRate] [float] NULL,
     [NonVestedIndividualRate] [float] NULL,
     [NonVestedFamilyRate] [float] NULL
    )

    GO

    Would it be ok not to have a Primary Key defined for some of the tables?

    Regards,

    CloudsInSky


    CloudsInSky

    Tuesday, May 5, 2020 8:32 PM

Answers

  • a true permanent table should have either a Primary key, a unique constraint, or a unique index.

    it might be ok for a intermediate/working table , but not a permanent table

    it looks like your table just has values, nothing to uniquely identify a row, in which case you could end up with ugly SQL to properly identify a specific (unique) row


    jchang

    • Marked as answer by cloudsInSky Wednesday, May 6, 2020 5:55 PM
    Tuesday, May 5, 2020 8:43 PM
  • I have a table as below:
    CREATE TABLE [Rates]
    (
     [VestedIndividualRate] [float] NULL,
     [VestedFamilyRate] [float] NULL,
     [NonVestedIndividualRate] [float] NULL,
     [NonVestedFamilyRate] [float] NULL
    )


    GO

    Would it be ok not to have a Primary Key defined for some of the tables?

    Only if you like to get yourself in trouble. How would you be able to tell the rows in that table apart?

    There is one possibly exception though: the table is supposed to only have one row. But in that case I would consider to add an id column, make that a primary key and then add a CHECK constraint saying that it must be 1, so that you don't get extra rows by happenstance.

    One more thing in that table looks very funny: all columns are nullable. What would a row with all NULL mean?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by cloudsInSky Wednesday, May 6, 2020 5:55 PM
    Tuesday, May 5, 2020 9:59 PM
  • Hi cloudsInSky,

    A primary key is a field in a table which uniquely identifies each row/record in a database table. It contains unique values and cannot have NULL values.

    It is not necessary, but it has many benefits in a relational database, like:

    Searching for records are faster.
    Easily identify and find unique rows in the database table.
    The table’s data is sorted based on the primary key.
    They help you avoid inserting duplicate records in a table.
    The allow creating referential integrity constraints (i.e. use of foreign keys).

    If you will have hundreds of millions of rows in your table, I recommend you to use it to avoid messy structure and organization.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by cloudsInSky Wednesday, May 6, 2020 5:55 PM
    Wednesday, May 6, 2020 2:27 AM
  • Would it be ok not to have a Primary Key defined for some of the tables?

    My 2 cents...

    This question makes no sense before we understand “OK" by "what","whom","which rules","for what"...

    Well, before I go forward, I must clarify something. The discussion can be over should we have a primary key or not, but it cannot be about the question is a primary key mandatory, since it is a simple fact that in all common tabular databases, we can create a table without a primary key! Meaning that it is a fact that it’s not mandatory (by the databases).

    A more practical question can be: Is a primary key mandatory in our organization, mandatory according to our boss’s rules, or mandatory according to any other subjective conditions/limitation/restriction… On this question we cannot answer since it depends and different from one to other.

    We can answer that it is mandatory according to specific standards.

    So what about the answer to the better question: should we always have primary key or "Would it be ok not to have a Primary Key defined for some of the tables"?

    There are tens of people who considered as top experts, which give the direct answer "yes", but probably most if not all of those will agree that there are cases where we should not use primary key (like some cases of log table, single row table, Some case of heap tables, and so on). Some say that it is really rare scenario and some speak about it as common scenario, but most will agree on these "exceptions", with talks about "it might be ok for...", "There is one possibly exception..."

    If this is true, then the answer to "do we need Primary Key" is that it depends.

    therefore, the answer is simple – no! If someone can find a single scenario where the answer is no, then by the definition of the word “always” we are getting the answer “no! we should not always have a primary key!”. So this question is not well asked as well.

    The only discussion we can have is a general discussion on the question when should we have primary, or when it is not a must to add primary key.

    >> How would you be able to tell the rows in that table apart?

    Physical location of a row in SQL Server: %%physloc%%

    I AM NOT SAYING THAT YOU SHOULD NOT HAVE PK BUT THAT IT IS NOT A MUST.
    Primary should be the default for all table unless you have a very good reason not the add primary key.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    • Edited by pituachMVP Wednesday, May 6, 2020 2:56 AM
    • Marked as answer by cloudsInSky Wednesday, May 6, 2020 5:59 PM
    Wednesday, May 6, 2020 2:54 AM

All replies

  • a true permanent table should have either a Primary key, a unique constraint, or a unique index.

    it might be ok for a intermediate/working table , but not a permanent table

    it looks like your table just has values, nothing to uniquely identify a row, in which case you could end up with ugly SQL to properly identify a specific (unique) row


    jchang

    • Marked as answer by cloudsInSky Wednesday, May 6, 2020 5:55 PM
    Tuesday, May 5, 2020 8:43 PM
  • I have a table as below:
    CREATE TABLE [Rates]
    (
     [VestedIndividualRate] [float] NULL,
     [VestedFamilyRate] [float] NULL,
     [NonVestedIndividualRate] [float] NULL,
     [NonVestedFamilyRate] [float] NULL
    )


    GO

    Would it be ok not to have a Primary Key defined for some of the tables?

    Only if you like to get yourself in trouble. How would you be able to tell the rows in that table apart?

    There is one possibly exception though: the table is supposed to only have one row. But in that case I would consider to add an id column, make that a primary key and then add a CHECK constraint saying that it must be 1, so that you don't get extra rows by happenstance.

    One more thing in that table looks very funny: all columns are nullable. What would a row with all NULL mean?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by cloudsInSky Wednesday, May 6, 2020 5:55 PM
    Tuesday, May 5, 2020 9:59 PM
  • Hi cloudsInSky,

    A primary key is a field in a table which uniquely identifies each row/record in a database table. It contains unique values and cannot have NULL values.

    It is not necessary, but it has many benefits in a relational database, like:

    Searching for records are faster.
    Easily identify and find unique rows in the database table.
    The table’s data is sorted based on the primary key.
    They help you avoid inserting duplicate records in a table.
    The allow creating referential integrity constraints (i.e. use of foreign keys).

    If you will have hundreds of millions of rows in your table, I recommend you to use it to avoid messy structure and organization.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by cloudsInSky Wednesday, May 6, 2020 5:55 PM
    Wednesday, May 6, 2020 2:27 AM
  • Would it be ok not to have a Primary Key defined for some of the tables?

    My 2 cents...

    This question makes no sense before we understand “OK" by "what","whom","which rules","for what"...

    Well, before I go forward, I must clarify something. The discussion can be over should we have a primary key or not, but it cannot be about the question is a primary key mandatory, since it is a simple fact that in all common tabular databases, we can create a table without a primary key! Meaning that it is a fact that it’s not mandatory (by the databases).

    A more practical question can be: Is a primary key mandatory in our organization, mandatory according to our boss’s rules, or mandatory according to any other subjective conditions/limitation/restriction… On this question we cannot answer since it depends and different from one to other.

    We can answer that it is mandatory according to specific standards.

    So what about the answer to the better question: should we always have primary key or "Would it be ok not to have a Primary Key defined for some of the tables"?

    There are tens of people who considered as top experts, which give the direct answer "yes", but probably most if not all of those will agree that there are cases where we should not use primary key (like some cases of log table, single row table, Some case of heap tables, and so on). Some say that it is really rare scenario and some speak about it as common scenario, but most will agree on these "exceptions", with talks about "it might be ok for...", "There is one possibly exception..."

    If this is true, then the answer to "do we need Primary Key" is that it depends.

    therefore, the answer is simple – no! If someone can find a single scenario where the answer is no, then by the definition of the word “always” we are getting the answer “no! we should not always have a primary key!”. So this question is not well asked as well.

    The only discussion we can have is a general discussion on the question when should we have primary, or when it is not a must to add primary key.

    >> How would you be able to tell the rows in that table apart?

    Physical location of a row in SQL Server: %%physloc%%

    I AM NOT SAYING THAT YOU SHOULD NOT HAVE PK BUT THAT IT IS NOT A MUST.
    Primary should be the default for all table unless you have a very good reason not the add primary key.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    • Edited by pituachMVP Wednesday, May 6, 2020 2:56 AM
    • Marked as answer by cloudsInSky Wednesday, May 6, 2020 5:59 PM
    Wednesday, May 6, 2020 2:54 AM