Key & CONSTRAINTS

CONSTRAINTS

SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint. Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement).

SQL CREATE TABLE + CONSTRAINT Syntax

The basic syntax is as follows:

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

we have the following constraints:

NOT NULLL
UNIQUE
PRIMARY KEY
FOREIGN KEY
SUPER KEY
CANDIDATE KEY 
CHECK 
DEFAULT

Primary key:

Primary Key. A primary key is a field in a table which uniquely identifies each row/record in a database table.
1- Primary key uniquely identify a record in the table.
2- Primary Key can't accept null values.
3- We can have only one Primary key in a table.
4-It created a clusted index by default.

Unique Key:

It's a column or group of columns that can identify uniqueness in a row. Can more than one unique key in one table. The unique key can have null values. It can be a candidate key. The unique key can be null and may not be unique. 

Foreign Key:

 In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as a Foreign key.

Super key:

It’s any combination of column(s) for which that combination of values will be unique across
all rows in a table. So, all of those combinations of columns in the Manager table that we 
gave earlier would be considered to be superkeys. 

Minimal super key:

its like a candidate key. super keys can be more than one. candidate keys can be more than one.
primary key is picked from the the bundle of candidate keys.

Secondary keys:

Consider a table called Managers that stores all of the managers in a company. Each manager has a unique Manager ID Number, a physical address, and an email address. Let’s say that the Manager ID is chosen to be the primary key of the Managers table. Both the physical address and email address could have been selected as the primary key, because they are both unique fields for every manager row in the Managers table. But, because the email address and physical address were not selected as the primary key, they are considered to be secondary keys.

Candidate Key:

A candidate key is the most minimal subset of fields that uniquely identifies a tuple. For exampl,e if you have a candidate key on the column "user_id" and "pet_id" you'll never have more than 1 tuple with the same user_id and pet_id and neither user_id nor pet_id individually will work as a unique identifier for the tuple. 

A super key is a set of fields that contains a key. Using the above example where the combination of "user_id" and "pet_id" uniquely identifies a tuple if we added, "pet_name" (which is not key because we can have multiple pets named "fluffy") it would be a super key. Basically,y it's like a candidate key without the "minimal subset of fields" constraint.