Clever Geek Handbook

SQL - unique key, primary key and foreign key

What are the differences between Unique Key, Primary Key and Foreign Key in terms of concept SQL

?

How do they differ from each other?

+3


source to share


3 answers


Precedence Keys and UNIQUE Key constraints are the same and provide unique uniqueness for the column on which they are defined.

Primary key

  • The primary key cannot be NULL.
  • Each table can only have one primary key.
  • By default, the primary key is a clustered index, and the data in a database table is physically organized in a clustered index sequence.
  • A primary key can be linked to another table as a foreign key.
  • We can generate the ID automatically using the Auto Increment field. The primary key supports the Auto Increment value.

Unique key



  • The unique constraint can be NULL.
  • Each table can have more than one unique constraint.
  • By default, a unique key is a unique nonclustered index.
  • A unique constant cannot be associated with another table as a foreign key.
  • Unique Constraint does not support Auto Increment value.

External key

  • A foreign key is a field in a table that is the primary key in another table.
  • A foreign key can take multiple null values.
  • The foreign key does not automatically create an index, clustered or non-clustered. You can manually create an index on a foreign key.
  • We can have more than one foreign key in a table.
  • There are actual advantages to having a clustered index foreign key, but you only get one per table. What is the advantage? If you select parent plus all child entries, you want the child to write next to each other. This is easy to accomplish with a clustered index.
  • Having a null foreign key is usually a bad idea. In the example below, the entry is in [dbo]. [Child] is what will be called an "orphan". Think long and hard before doing this.
+16


source


Note: we use constraint to ensure data integrity

Primary key
1) cannot insert null
2) one table has one primary key



Unique key 1) insert null value one by one 2) one table has multiple unique keys 3) you can also reference candidate key

foreign key 1) maintain a relationship between two tables as well as multiple Note: without any restriction, you get data in multiple tables, but you cannot get data peoperly

+1


source


Note on Unique Key

The parent table in relation to the foreign key primary key is usually called the primary key table, but PK is not required in the parent table. A unique key / constraint in the parent table is sufficient. Since PK is always unique, it is often used as a foreign key in another table. see this SO post

-1


source






More articles:


All Articles