SQL - Keys And Constraints

Keys And Constraints

Keys and constraints define the structure and integrity rules of a relational database.

Primary Key

A primary key uniquely identifies each record in a table.

Properties:

• cannot contain NULL
• should be unique
• each table should have one primary key

Foreign Key

A foreign key references the primary key of another table. It creates a relationship between tables.

Foreign keys can contain NULL when the relationship is optional.

Stored Procedures And Triggers

Stored procedures and triggers move logic into the database. They can be useful, but they also make behavior less visible from application code.

Trigger Example

DELIMITER $$
CREATE TRIGGER trigger_update_chicago_school_teachers_score_only
BEFORE UPDATE ON chicago_school
FOR EACH ROW
BEGIN
    DECLARE new_icon VARCHAR(11);
 
    IF NEW.teachers_score != OLD.teachers_score
       AND NEW.teachers_icon = OLD.teachers_icon THEN
 
        IF NEW.teachers_score < 20 THEN
            SET new_icon = 'Very Weak';
        ELSEIF NEW.teachers_score < 40 THEN
            SET new_icon = 'Weak';
        ELSEIF NEW.teachers_score < 60 THEN
            SET new_icon = 'Average';
        ELSEIF NEW.teachers_score < 80 THEN
            SET new_icon = 'Strong';
        ELSE
            SET new_icon = 'Very Strong';
        END IF;
 
        SET NEW.teachers_icon = new_icon;
    END IF;
END$$
DELIMITER ;

Relation To Joins

SQL Joins often use primary-key and foreign-key relationships as join conditions.