0

So in my work environment we don't use a 'primary key' as defined by SQL Server. In other words, we don't right click a column and select "set as primary key".

We do however still have primary keys, we just use a unique ID column. In stored procedures we use these to access the data like you would in any relational database.

My question is, other than the built in functionality that comes with defining a primary key in SQL Server like Entity Framework stuff etc. Is there a good reason to use the 'primary key' functionality over just using a unique ID column and accessing your tables with that in your own stored procedures?

The biggest drawback I see (again other than being able to use Entity Framework and things like that) is that you have to mentally keep track or otherwise keep track of what ID relates to what tables.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
D. Cain
  • 3
  • 1
  • 5
  • Are you *enforcing* the uniqueness of the ID column in the database? – Damien_The_Unbeliever Oct 27 '17 at 13:24
  • 3
    If you don't define a column as a primary key you don't have a primary key. You guys are kidding yourselves. You have logical primary keys but are for some reason circumventing referential integrity. There is no drawback of using primary keys. There shouldn't be any memorization either if you use reasonable naming conventions. That means a column does not change its name between tables. – Sean Lange Oct 27 '17 at 13:25
  • https://stackoverflow.com/questions/840162/should-each-and-every-table-have-a-primary-key – SQL_M Oct 27 '17 at 13:29
  • Uniqueness isn't enforced per se, our DBA just uses the MSSQL UID() (or whatever it is) to generate a unique ID. I don't believe though that that ID is guaranteed unique. It's just a randomly generated big key. – D. Cain Oct 27 '17 at 13:34
  • 4
    If your database has no PK/unique indexes, it means you have no FK at all? – sepupic Oct 27 '17 at 13:38
  • 2
    I would not want to be a new developer in this environment that had to learn all the "primary keys" that aren't actually keys. The biggest drawback is that if someone leaves, you are in big trouble. – Jacob H Oct 27 '17 at 13:57
  • The database technically has no PK or FK per se. The primary key is just a column like "EmployeeID" or "ContactID" etc. Each table has an ID column of some kind that is just a unique ID generated by SQL (NEWID()) etc. These are then used in queries to relate the data. The only relation is simply that an EmployeeID column exists in the employees table, and an EmployeeID column exists in the say "JobTitle" table. So the EmployeeID relates an employee to a JobTitle. – D. Cain Oct 27 '17 at 14:59
  • 1
    Having no Foreign Keys is a _big problem_ as you aren't enforcing referential integrity. As for the PKs there are many tools that assume that you will use PKs and won't work as nicely without them. EF will work without PKs but you have to declare which key is the Entity Key yourself. – David Browne - Microsoft Oct 27 '17 at 21:10

3 Answers3

3

There is nothing "special" about the PRIMARY KEY constraint. It's just a uniqueness constraint and you can achieve the same results by using the UNIQUE NOT NULL syntax to define your keys instead.

However, uniqueness constraints (i.e. keys in general, not "primary" keys specifically) are very important for data integrity reasons. They ensure that your data is unique which means that sensible, meaningful results can be derived from your data. It's extremely difficult to get accurate results from a database that contains duplicate data. Also, uniqueness constraints are required to enforce referential integrity between tables, which is another very important aspect of data integrity. Poor data integrity is a data management problem that costs businesses billions of dollars every year and that's the bottom line of why keys are important.

There is a further reason where unique indexes are important: query optimization and performance. Unique indexes improve query performance. If your data is supposed to be unqiue then creating a unique index on it will give the query optimizer the best chance of picking a good execution plan for your queries.

nvogel
  • 24,981
  • 1
  • 44
  • 82
2

I think the drawback is not using the primary key at all and using a unique key constraint for something it wasn't intended to do.

Unique keys: You can have many of them. They are meant to offer a way to determine uniqueness among rows.

Primary key: like the Highlander, there can only be one. It's intended use is to identify the rows of the table.

I can't think of any good reason not to use a primary key. My opinion is that without a primary key, your table isn't actually a table. It's just a lump of data.

Follow Up: If you don't believe me, check out this guy who asked a bunch of DBA's if it was OK not to use a primary key.

Is it OK not to use a Primary Key When I don't Need one

Adam Vincent
  • 3,281
  • 14
  • 38
  • From a purist perspective, taking one key and declaring it as "above" the other keys seems unnecessary. Each candidate key could be declared as a unique key and all other SQL functionality works fine without declaring a PK. – Damien_The_Unbeliever Oct 27 '17 at 13:35
  • 1
    A real primary key enforces the rule that all of the key columns do not allow `NULL` values. Unique constraints and indexes allow nullable columns. – Dan Guzman Oct 27 '17 at 13:39
  • The linked question seems to be along the lines of "PK vs no key". As I said, from the purist perspective, if the question is "PK vs other keys", [this answer](https://stackoverflow.com/a/3466460/15498) discusses this in a bit more detail. – Damien_The_Unbeliever Oct 27 '17 at 14:15
  • *Just a lump of data* - other people would refer to it as a *steaming pile of .......* :-) – marc_s Oct 27 '17 at 14:39
  • Every superkey of a table identifies rows in that table. Keys are the sets of attributes that are irreducibly unique and non-nullable and fundamentally there is nothing special about a "primary" key per se - it is just one of the keys. What is important is that a table has *at least one* key. – nvogel Oct 28 '17 at 09:44
1

There are philosophical and practical answers to your question.

The practical answer is that using the primary key constraint enforces "not null", and "unique". This protects you from application-level bugs.

The philosophical answer is that you want developers to operate at the highest possible level of abstraction, so that they don't have to stuff their brain full of detail when trying to solve problems.

Primary and foreign keys are abstractions that allow us to make assumptions about the underlying data model. We can think in terms of (business) entities, and their relationships.

In your workplace, you're forcing developers to think in terms of tables and indexes and conventions. You no longer think about "customers" and "orders" and "line items", but about software artefacts that represent those business entities, and the "we always represent uniqueness by a combination of a GUID and unique index" rule. That mental model is already complicated enough in most applications; you're just making it harder for yourselves, especially when bringing new developers into the team.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
  • A key is no more of an abstraction than any other attribute. In many/most cases a key is a very direct, natural implementation of a business rule. An invoice table with a key of InvoiceNumber implements a business rule that each invoice must have a unique number; a user table keyed on EmailAddress implements a rule that a user must have a unique email address. If developers are only interested in entities and their relationships then they just need to know that each entity has at least one key. The "primality" of any one key over another is basically irrelevant in logical/conceptual terms. – nvogel Nov 02 '17 at 10:49