sql
  1. sql-difference
PreviousNext

SQL Keys Difference

SQL keys are used to uniquely identify a row in a table in a database. There are four types of keys in SQL: Primary Key, Foreign Key, Unique Key, and Candidate Key. In this article, we will discuss the differences between these SQL keys.

Syntax

Primary Key

CREATE TABLE table_name (
   column1 datatype PRIMARY KEY,
   column2 datatype,
   .....
);

Unique Key

CREATE TABLE table_name (
   column1 datatype UNIQUE,
   column2 datatype,
   .....
);

Foreign Key

CREATE TABLE table_name (
   column1 datatype,
   column2 datatype,
   .....
   FOREIGN KEY (column1) REFERENCES parent_table (column2)
);

Candidate Key

CREATE TABLE table_name (
   column1 datatype,
   column2 datatype,
   .....
   candidate1 KEY (column1, column2)
);

Example

Let's take an example of a "Book" table in a library database.

Primary Key

CREATE TABLE Book (
   book_id int PRIMARY KEY,
   title varchar(255),
   author varchar(255),
   .....
);

Unique Key

CREATE TABLE Book (
   isbn varchar(13) UNIQUE,
   title varchar(255),
   author varchar(255),
   .....
);

Foreign Key

CREATE TABLE BorrowedBooks (
   borrow_id int PRIMARY KEY,
   book_id int,
   borrower_id int,
   borrow_date datetime,
   .....
   FOREIGN KEY (book_id) REFERENCES Book(book_id),
   FOREIGN KEY (borrower_id) REFERENCES Borrowers(borrower_id)
);

Candidate Key

CREATE TABLE Book (
   book_id int,
   isbn varchar(13),
   .....
   candidate1 KEY (book_id, isbn)
);

Output

The output of executing the above SQL commands will be the creation of tables with the specified keys.

Explanation

Primary Key

A primary key is a column or set of columns that uniquely identifies each row in a table. It cannot have null values and must be unique. A table can have only one primary key.

Unique Key

A unique key is a column or set of columns that uniquely identify each row in a table. It can have null values and must be unique. A table can have multiple unique keys.

Foreign Key

A foreign key is a column or set of columns that refer to a primary key in another table. It enforces referential integrity between the two tables. A table can have multiple foreign keys.

Candidate Key

A candidate key is a column or set of columns that can be designated as a primary key or unique key. It can have null values and must be unique. A table can have multiple candidate keys.

Use

Primary keys are used to identify each row in a table. Foreign keys are used to establish relationships between tables. Unique keys are used to ensure data integrity by preventing duplicate values. Candidate keys are used to identify potential primary keys or unique keys.

Important Points

  • A primary key uniquely identifies each row in a table.
  • A foreign key refers to a primary key in another table and enforces referential integrity.
  • A unique key ensures data integrity by preventing duplicate values.
  • A candidate key can be designated as a primary key or unique key.

Summary

SQL keys are used to uniquely identify a row in a table. There are four types of SQL keys: Primary Key, Foreign Key, Unique Key, and Candidate Key. The main differences between them are their syntax, purpose, and implementation. Primary keys uniquely identify each row in a table, foreign keys are used to establish relationships between tables, unique keys ensure data integrity by preventing duplicate values, and candidate keys identify potential primary keys or unique keys.

Alternate KeyLTRIM Function
Published on: