differences between primary key vs unique key
The primary key does not store null values, whereas the unique key does. A table
can only have one primary key, whereas it can have multiple unique keys.
The primary key does not allow you to delete or modify the data. On the other hand,
a unique key does.
SQL Constraints
SQL constraints are used to specify rules for the data in a table. Constraints are
used to limit the type of data that can go into a table.
The following constraints are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE
The UNIQUE constraint ensures that all values in a column are different.
Unique key can have null values.A table can have more than one unique key.
PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key.the table with the primary key is called as
parent table.
FOREIGN KEY
Prevents actions that would destroy links between tables.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the
PRIMARY KEY in another table.
It is possible to have more than one foreign key in a table, and they can accept a
null value. Foreign key values do not need to be unique; duplicate values can be
stored in foreign key columns. The table with the foreign key is called the child
table
CHECK
The CHECK constraint is used to limit the value range that can be placed in a
column.
If you define a CHECK constraint on a column it will allow only certain values for
this column.
DEFAULT - Sets a default value for a column if no value is specified
CREATE INDEX -
The CREATE INDEX statement is used to create indexes in tables.
Indexes are used to retrieve data from the database more quickly. The users cannot
see the indexes, they are just used to speed up searches/queries.
SQL JOINS
A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables.
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the
matching records from the right table (table2). The result is 0 records from the
right side, if there is no match.
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the
matching records from the left table (table1). The result is 0 records from the
left side, if there is no match.
SQL FULL OUTER JOIN Keyword
The FULL OUTER JOIN keyword returns all records when there is a match in left
(table1) or right (table2) table records.
FULL OUTER JOIN and FULL JOIN are the same.
SQL SELF JOIN
A self join is a regular join, but the table is joined with itself.
Triggers
A trigger is a special type of stored procedure that automatically runs when an
event occurs in the database server. DML triggers run when a user tries to modify
data through a data manipulation language (DML) event. DML events are INSERT,
UPDATE, or DELETE statements on a table or view.
DDL (data definition language) triggers – As expected, triggers of this type shall
react to DDL commands like – CREATE, ALTER, and DROP.
