When to use a unique key in a table, than a primary key?

Question

Since the primary key and unique similar. I find it difficult to understand the concept of these two. I know that the primary key does not accept null, and the unique key accepts null once. Since a null value is a unique value, therefore it can only be accepted once. But the idea of ​​a primary key has uniqueness in every row. which also makes a unique key. that’s why I ask when it is right to use a primary key with a unique key and vice versa.

Answer 1

The UNIQUE constraint is similar to the PRIMARY key, but you can have more than one UNIQUE constraint for each table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the duplicate lookup process. In this case, the index defaults to NONCLUSTERED, because you can only have one CLUSTERED index for each table.

• The number of UNIQUE constraints for each table is limited by the number of indexes in the ie 249 NONCLUSTERED index table and one possible CLUSTERED index.

Unlike the PRIMARY UNIQUE key, constraints can be NULL, but only once. If the restriction is defined in a combination of fields, then each field may be NULL and may have some values ​​on them if the combination values ​​are unique.

Also see another link ( MSDN )

Answer 2

Summary: for each base table, it is important to have a key using either PRIMARY KEY or NOT NULL UNIQUE . The difference between them is not a relational consideration and does not matter from a logical point of view; rather, this is just a psychological consideration.

---

relvar may have several keys, but we select only one for underlining and call it the primary key. The choice is arbitrary, so the concept of the primary is not very important from a logical point of view. However, the general concept of the key is very important! the term candidate key means exactly the same as the key (that is, adding a candidate does not have real meaning - it was proposed by Ted Codd because he considered each key as a candidate for appointment as a primary key) ... SQL allows a subset of the table columns declared as the key for this table. It also allows one of them to be assigned as a primary key. Indication of a key that will be for a certain convenience due to other restrictions that may be required

What is a key? Hugh darwen

---

usually ... allocate one key as the primary key (and any other keys for the relvar in question are said to be alternative keys). But whether one should choose a key as primary, and if so, which one, are essentially psychological problems that go beyond the framework of the relational model as such. As for good practice, most of the basic ones. Relvars should probably have a primary key, but repeating this rule, if this rule is not really a relational problem as such ... Strong recommendation [for SQL users]: for basic tables, in any case, use PRIMARY KEY and / or UNIQUE to ensure that each such table has at least one key.

SQL and relational theory: how to write accurate SQL code By C. J. Date

---

In standard SQL PRIMARY KEY

• implies uniqueness, but you can specify this explicitly (using UNIQUE ).
• implies NOT NULL , but you can specify this explicitly when creating columns (but you should avoid zeros anyway!)
• allows you to omit columns in FOREIGN KEY , but you can explicitly specify them.
• it can be declared for only one key in the table, but it is not clear why (Codd, which originally proposed the concept, did not impose such a restriction).

Some PRIMARY KEY products mean a table-based clustered index, but you can specify this explicitly (you may not want the primary key to be a clustered index!)

For some people, PRIMARY KEY has a purely psychological meaning:

• they think that this means that the key will refer to the foreign key (this was suggested by Codd, but not actually accepted by standard SQL and SQL providers).
• they think that this means a single table key (but refusing to enforce other candidate keys leads to a loss of data integrity).
• they think that this implies a "surrogate" or "artificial" key that has no meaning for the business (but actually imposes an undesirable value on the enterprise, being susceptible to users).

Answer 3

A table can have several UNIQUE keys, but only one PRIMARY key is allowed for a table. IF your unique key is NOT A UNIQUE KEY, then it is always recommended to promote it in the MAIN KEY. If your storage engine is INNODB, and if you do not have a PRIMARY key, then innodb will automatically create the HEXDECIMAL PRIMARY internal key, which will have some performance impact, so it is best to create a primary key always with the INNODB storage engine.

Answer 4

A PK is considered a unique identifier for a string. It should never be changed. For example, user id.

The UK is considered unique throughout the column. It is not necessarily a string identifier, as it can be changed. For example, the username or email address of the user.