PRIMARY KEY vs FOREIGN KEY vs CHECK Constraint vs UNIQUE Constraint vs NOT NULL Constraint

 PRIMARY KEY vs FOREIGN KEY vs CHECK Constraint vs UNIQUE Constraint vs NOT NULL Constraint

Primary keys, foreign keys, check constraints, unique constraints, and not null constraints are all used to enforce data integrity in a relational database. However, they each serve different purposes.

Primary keys are unique identifiers for rows in a table. They cannot be null and must be unique within the table. A primary key constraint ensures that each row in a table has a unique value in the primary key column. This helps to prevent duplicate data and makes it easier to identify individual rows in the table.

Foreign keys are used to relate two tables together. They ensure that the values in a foreign key column in one table match the values in the primary key column of another table. This helps to maintain referential integrity between the two tables. For example, if you have a table of customers and a table of orders, you could use a foreign key constraint to ensure that the customer ID in the orders table matches the customer ID in the customers table. This would prevent you from creating an order for a customer that does not exist.

Check constraints are used to restrict the values that can be stored in a column. For example, you could use a check constraint to ensure that the values in a column are always positive integers. Check constraints can be used to enforce business rules and to prevent invalid data from being entered into the database.

Unique constraints are similar to primary keys, but they do not have to be unique across the entire table. They only have to be unique within a group of rows. For example, you could use a unique constraint to ensure that each customer ID in a table is unique within a specific state. Unique constraints can be used to prevent duplicate data and to make it easier to identify individual rows in the table.

Not null constraints are used to ensure that a column cannot have a null value. This can be useful for columns that are essential for identifying rows in the table, such as the primary key column. Not null constraints can also be used to enforce business rules, such as ensuring that a customer must have a name.

Constraint	Description
Primary key		A unique identifier for rows in a table. It cannot be null and must be unique within the table. A primary key constraint ensures that each row in a table has a unique value in the primary key column. This helps to prevent duplicate data and makes it easier to identify individual rows in the table.
Foreign key		Used to relate two tables together. It ensures that the values in a foreign key column in one table match the values in the primary key column of another table. This helps to maintain referential integrity between the two tables. For example, if you have a table of customers and a table of orders, you could use a foreign key constraint to ensure that the customer ID in the orders table matches the customer ID in the customers table. This would prevent you from creating an order for a customer that does not exist.
Check constraint		Used to restrict the values that can be stored in a column. For example, you could use a check constraint to ensure that the values in a column are always positive integers. Check constraints can be used to enforce business rules and to prevent invalid data from being entered into the database.
Unique constraint		Similar to primary keys, but they do not have to be unique across the entire table. They only have to be unique within a group of rows. For example, you could use a unique constraint to ensure that each customer ID in a table is unique within a specific state. Unique constraints can be used to prevent duplicate data and to make it easier to identify individual rows in the table.
Not null constraint		Used to ensure that a column cannot have a null value. This can be useful for columns that are essential for identifying rows in the table, such as the primary key column. Not null constraints can also be used to enforce business rules, such as ensuring that a customer must have a name.

Statement	Description
DROP TABLE	Completely removes the table from the database, including the table structure and all data.
TRUNCATE TABLE	Removes all data from the table, but does not remove the table structure.
DELETE	Removes specific rows from a table.