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.

Improve this question
8
  • Are you enforcing the uniqueness of the ID column in the database? Commented Oct 27, 2017 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. Commented Oct 27, 2017 at 13:25
  • 4
    If your database has no PK/unique indexes, it means you have no FK at all? Commented Oct 27, 2017 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. Commented Oct 27, 2017 at 13:57
  • 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. Commented Oct 27, 2017 at 21:10

3 Answers 3

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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

Improve this answer

5 Comments

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.
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.
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 discusses this in a bit more detail.
Just a lump of data - other people would refer to it as a steaming pile of ....... :-)
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.
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.

Improve this answer

1 Comment

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.