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 Reled 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 Column CAN Contain Null
3. UNIQUE CONSTRAIN COLUMN CAN Be Modified or Updated
4. Unique constrain column value can be reused
5. Unike Primary Keys, UNIQUE CONSTRAINTS CANNOT BE Used to Define Foreign Keys
Check constraints:
Check Constaints Are Used to Ensure That Data I Column Meets A Set of criteria That You Specify.
1. Checking minimun or maximum value - E.G. Preventing An ORDER OF ZERO ITEMS
2. Specifying Ranges - E.G. Making Sure That A Ship Date Is Greater Thanles Date 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 I 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;
