Understanding advanced SQL features

Primary key:

1. No two rows may have the same primary key value;

2. Every row must have a primary key value(NULL is not allowed)

3. The column containing primary key values can never be modified or updated

4. Primary key value can never be reused. If a row is deleted from the table, its primary key must not be assigned to any new rows.

Foreign Key:

A foreign key is a column in a table whose values must be listed in a primary key in another table.

1. Foreign keys can help prevent accidental deletion.

2. After a foreign key is defined, the database will not allow the deletion of rows that have related rows in other tables.

3. Some database(ACCESS for example) support a feature called cascading delete. If enabled, this feature deletes all related data when a row is deleted from a table.

Unique Constraints:

1. A table can contain multiple unique constraints, but only one primary key is allowed is allowed per table.

2. Unique constraint columns can contain NULL

3. Unique constrain column can be modified or updated

4. Unique constrain column values can be reused

5. Unlike primary keys, unique constraints cannot be used to define foreign keys

Check Constraints:

Check constaints are used to ensure that data in a column meets a set of criteria that you specify.

1. Checking minimun or maximum values - e.g. preventing an order of zero items

2. Specifying ranges - e.g. making sure that a ship date is greater than or equal to today's date and not greater than a year from now

3. Allowing only specific values - e.g. allowing only M or F in a gender field

Index

Indexes are used to sort data logically to improve the speed of searching and sorting operations. The fact that makes index work is the data is sorted correctly.

Trigger

Triggers are special stored procedures taht are executed automatically when a specified database activity occurs. Triggers might be associated with INSERT, UPDATE, and DELETE operations(or combination thereof) on sepcific tables.

Unlike stores procedures(which are simple SQL statements), triggers are tied to individual tables. E.g.  A trigger associated with INSERT operation on Orders table will be executed only when a row is inserted into the Orders table.

Example:

CREATE TRIGGER Customer_state

ON Customers

FOR INSERT, UPDATE

AS

UPDATE Customers

SET cust_state = UPPER(cust_state)

WHERE Customers.cust_id = inserted.cust_id;