Primary key or unique constraint?

Question

I am currently developing a completely new database. At school, we always learned to place a primary key in each table.

I read a lot of posts about articles / discussions / newsgroups that said it was better to use a unique constraint (also a unique index for some db) instead of PK.

What is your point of view?

Answer 1

Can you provide links to these articles?

I see no reason to change tried and tested methods. After all, primary keys are a fundamental design feature of relational databases.

Using UNIQUE to serve the same purpose sounds very hacky to me. What is their rationale?

Edit: My attention has just turned to this old answer. Perhaps the discussion you read about PC versus UNIQUE was about people doing something PC solely for the purpose of being unique on it. The answer to this: if it is a key, then make it a key, otherwise make it UNIQUE.

Answer 2

A primary key is simply a candidate key that is not NULL. Thus, in terms of SQL, this is no different from any other unique key.

However, for our non-theoretical RDBMSs, you must have a primary key - I never heard him say otherwise. If this primary key is a surrogate key , then you must also have unique restrictions on the natural key (s) .

The important bit to get away is that you must have unique constraints for all candidates (regardless of whether they are natural or surrogate). Then you should choose the one that most easily refers in your Foreign Key to your primary key *.

You must also have a clustered index *. it may be your primary key or natural key, but it is also not required. You must select your clustered index based on the use of the query in the table. If in doubt, a primary key is not a bad first choice.

• Although technically it was only necessary to refer to a unique key in relation to a foreign key, it adopted standard practice to largely support the primary key. In fact, I would not be surprised if some RDBMSs only allow primary key references.

• Edit: It was pointed out that the Oracle term “clustered table” and “clustered index” are different from Sql Server. In Oracle-ese, the equivalent of what I am saying is Index Ordered Table , and this is recommended for OLTP tables - which, I think, will be the main focus of SO questions. I assume that if you are responsible for the large OLAP data warehouse, you should already have your own opinion on the design and optimization of the database.

Answer 3

The primary key is only a candidate key (unique restriction) allocated for special treatment (automatic creation of indexes, etc.).

I expect that people who argue with them see no reason to consider one key differently than another. This is where I stand.

[Edit] Apparently, I cannot even comment on my own answer without 50 points.

@chris: I don’t think there is any harm. The primary key is really just syntactic sugar. I use them all the time, but I certainly don’t think they are required. A unique key is required, yes, but not necessarily a primary key.

Answer 4

This will be a very rare denormalization, which will make you want to have a table without a primary key. Primary keys have unique limitations automatically by nature as a PC.

The only restriction will be used if you want to guarantee uniqueness in the ADDITION column for the primary key.

The rule of always having PK is good.

http://msdn.microsoft.com/en-us/library/ms191166.aspx

Answer 5

You must always have a primary key.

However, I suspect that your question is simply worded as a misleading bit, and you really want to ask if the primary key should always be an automatically generated number (also called a surrogate key) or some unique field that is relevant relevant data ( also known as natural key), like SSN for people, ISBN for books, etc.

This question is a centuries-old religious war in the field of DB.

I believe that natural keys are preferable if they are truly unique and never change. However, you have to be careful, even something that would seem to be stable, like people whose SSN may change under certain circumstances.

Answer 6

Primary keys should be used in situations when you will establish relations from this table to other tables that will refer to this value. However, depending on the nature of the table and the data that you are used to applying a unique constraint to, you can use this particular field as a natural primary key, rather than setting a surrogate key. Of course, a surrogate against natural keys is another discussion. :)

Unique keys can be used if no relationship is established between this table and other tables. For example, a table containing a list of valid email addresses that will be mapped before entering a new user record or some of them. Or unique keys can be used when you have values ​​in a table with a primary key, but must also be absolutely unique. For example, if you have a user table with a username. You would not want to use the username as the primary key, but it must also be unique so that it can be used to log in.

Answer 7

If the table is not a temporary table for processing data while working on it, you always want to put the primary key in the table, and here's why:

1 - the only restriction can be null, but the primary key never allows null. If you run a query with a join in columns with null values, you remove these rows from the resulting dataset, because null is not zero. Thus, even large companies can make accounting errors and must recount their profits. Their queries did not display the specific rows that should have been included in the final value, because in some columns of their unique index there were zero values. Shoulda used the primary key.

2 - a unique index will be automatically placed in the primary key, so you do not need to create it.

3 - most database engines automatically place the clustered index in the primary key, making queries faster because the rows are stored adjacent to each other in data blocks. (This can be changed to put the clustered index in a different index if it will speed up the queries.) If the table does not have a clustered index, the rows will not be stored adjacent to the data blocks, making the queries slower because the read / write head should move across the entire disk to collect data.

4 - for many front-end development environments, a primary key is required to update the table or delete it.

Answer 8

We need to distinguish between logical constructions and physical constructions and similarly between theory and practice.

To start: from a theoretical point of view, if you do not have a primary key, you do not have a table. It is just that simple. So, your question is not whether your table should have a primary key (of course, it should be), but how you put it in your RDBMS.

At the physical level, most RDBMS implement primary key constraint as a unique index. If your chosen DBMS is one of them, then there is probably not much practical difference between designating a column as a primary key and simply placing a unique constraint on the column. However: one of these options captures your intentions, and the other does not. Thus, the solution is no problem.

In addition, some RDBMSs provide additional functions if Primary keys are properly labeled, such as diagrams and semi-automatic support for foreign key constraints.

Anyone who advises you to use unique constraints instead of primary keys as a general rule should provide a fairly simple reason.

Answer 9

the fact is that the primary key can be one or more columns that uniquely identify one table entry, where Unique Constraint is simply a restriction on a field that allows only one instance of any given data item in the Table.

PERSONALLY. I use either the GUID or auto-incrementing BIGINTS (Identity Insert for SQL SERVER) for the unique keys used for cross-referencing among my tables. Then I will use other data so that the user can select specific records.

For example, I will have a list of employees and there is a GUID attached to each record that I use behind the scenes, but when a user selects an employee, they select them based on the following fields: LastName + FirstName + EmployeeNumber.

My primary key in this scenario is LastName + FirstName + EmployeeNumber, and the unique key is the associated GUID.

Answer 10

messages say it's better to use a unique constraint (also a unique index for some db) instead of PK

I think the only point here is the same old discussion of "natural versus surrogate keys", because unique indexes and pk's are one and the same.

transfer:

messages say it's better to use a natural key instead of a surrogate key

Answer 11

I usually use both PK and UNIQUE KEY. Because, even if you do not specify PK in your scheme, you always create for yourself internally. This is true for both SQL Server 2005 and MySQL 5.

But I do not use the PK column in my SQL. This is for management purposes, such as DELETING some erroneous lines, asking for spaces between PK values, if set to AUTO INCREMENT. And it makes sense to have PK as numbers, not a collection of columns or char arrays.

Answer 12

I wrote a lot on this topic: if you read something, I will be clear that I probably mean, in particular, access to Jet aka MS Access.

In Jet, tables are physically ordered in PRIMARY KEY using an unordered clustered index (cluster on a compact). If the table does not have PK, but has candidate keys defined using UNIQUE constraints on NOT NULL columns, then the engine will select one for the clustered index (if your table does not have a clustered index, then it is called a heap, it may not be a table at all! ) How does the engine select the candidate key? Can he choose one that includes columns with a null value? I really do not know. The fact is that in Jet, the only explicit way to specify a cluster index for the engine is to use PRIMARY KEY. Of course, for a PC in Jet, for example, there are other ways to use it. it will be used as a key if it is excluded from the FOREIGN KEY declaration in SQL DDL, but again, why not be explicit.

The problem with Jet is that most people creating tables do not know or care about clustered indexes. In fact, most users (I set) put the Autonumber autoincrement column in each table and determine the PRIMARY KEY exclusively in this column, without creating any unique restrictions on the natural key and candidate keys (can I actually read the autoincrement column key without exposing it to end users, - this is another discussion in itself). I will not go into details about clustered indexes here, but suffice it to say that IMO is the only auto-increment column, rarely suitable for the perfect choice.

No matter what kind of SQL engine you are, the choice of PRIMARY KEY is arbitrary and engine dependent. Typically, the engine will attach particular importance to the PC, so you should find out what it is and use it to your advantage. I urge people to use NOT NULL UNIQUE constraints in the hope that they will pay more attention to all candidate keys, especially when they decide to use "autonumber" columns, which (should) make no sense in the data model. But I would prefer that people choose one well-considered key and use PRIMARY KEY instead of putting it in the auto-increment column out of habit.

Should all tables have a PC? I say yes, because otherwise, at least you lose the small advantage that the PC gives, and in the worst case, you do not have data integrity.

BTW Chris OC makes a good point here about temporary tables that require primary keys with primary keys (lowercase letters) that cannot be implemented using simple PRIMARY KEY constraints (SQL keywords in upper case).

Answer 13

PRIMARY KEY

1. Null It does not allow null values. Because of this, we turn to PRIMARY KEY = UNIQUE KEY + NOT INCORRECT DESIGN. 2. INDEX By default, it adds a clustered index. 3. LIMIT A table can have only one column PRIMARY KEY [s].

UNIQUE KEY

1. Null Allows you to specify the value Null. But only one value is Null. 2. INDEX By default, it adds a UNIQUE nonclustered index. 3. LIMIT A table may have several UNIQUE key columns [s].

Answer 14

If you plan to use LINQ-to-SQL, your tables will require Primary keys if you plan to perform updates, and they will require a timestamp column if you plan to work in a disconnected environment (for example, an object through a WCF service application).

If you like .NET, PK and FK are your friends.

Answer 15

I affirm that you may need both. Primary keys must be unique in nature and cannot be reset. They are often surrogate keys, because integers create faster joins than characters, and especially than multiple joins of fields. However, since they are often generated automatically, they do not guarantee the uniqueness of a data record, excluding the identifier itself. If your table has a natural key that must be unique, you must have a unique index to prevent duplicate entries. This is a basic data integrity requirement.

Edited to add: It is also a real problem that real-world data often does not have a natural key, which really guarantees uniqueness in the normalized table structure, especially if the database is focused on people. The names, even the name, address and phone number together (I think father and son in the same medical practice) are not necessarily unique.

Answer 16

I was thinking about this problem. If you use unique, you will damage 2. NF. Accordingly, each non-pk attribute must be PC-specific. A pair of attributes in this unique constraint should be considered part of the PC.

It is a pity that she answered this 7 years later, but did not want to start a new discussion.