×
Clear all filters including search bar
Valeri Tandilashvili's Personal Professional Blog
Results: 1022
Create INDEX with several fields
Creates one 
INDEX
with several fields for table 
notes
. Combines the two fields 
title, description
to create one index
CREATE TABLE note (
    id int AUTO_INCREMENT,
    note_id int,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(id, note_id),
    INDEX ind_title_description (title, description)
);
If we need to filter the result with both fields 
(title, description)
at the same time, then 
ind_title_description
is a great index
Create procedure that checks and prints income level 
DROP PROCEDURE IF EXISTS printIncomeLevel;

DELIMITER //

CREATE PROCEDURE printIncomeLevel (IN monthly_value INT)

BEGIN

   DECLARE income_level varchar(20);

   IF monthly_value <= 4000 THEN
        SET income_level = 'Low Income';
   ELSEIF monthly_value > 4000 AND monthly_value <= 7000 THEN
        SET income_level = 'Avg Income';
   ELSE
        SET income_level = 'High Income';
   END IF;

   INSERT INTO log (description)
   VALUES ('getIncomeLevel procedure was called');

   SELECT income_level;

END;
After creating the procedure, we can call it using 
CALL
keyword:
CALL printIncomeLevel(450)
Create function salary level 
DROP FUNCTION IF EXISTS getIncomeLevel;

DELIMITER //

CREATE FUNCTION getIncomeLevel ( monthly_value INT )
RETURNS varchar(20)

BEGIN

   DECLARE income_level varchar(20);

   IF monthly_value <= 4000 THEN
        SET income_level = 'Low Income';
   ELSEIF monthly_value > 4000 AND monthly_value <= 7000 THEN
        SET income_level = 'Avg Income';
   ELSE
        SET income_level = 'High Income';
   END IF;

   INSERT INTO log (description)
   VALUES ('getIncomeLevel function was called');

   RETURN income_level;

END; //

DELIMITER ;
After creating the function we can call it:
SELECT getIncomeLevel(450)
Foreign key constraint Referential Actions
MySQL supports several referential actions: 
CASCADE
- deletes or updates appropriate rows in the child table 
SET NULL
- sets the foreign key column of the child table to 
NULL
RESTRICT
- rejects the operation for the parent table 
NO ACTION
- equivalent to 
RESTRICT
Note: 
RESTRICT
is the same as omitting the 
ON DELETE
or 
ON UPDATE
clause, which means that it's default action
Create TRIGGER before INSERT
Creates trigger that runs 
before
insert
and checks & replaces gender values
DROP TRIGGER IF EXISTS before_student_insert;

DELIMITER $$

    CREATE TRIGGER before_student_insert BEFORE INSERT 
    ON students
    FOR EACH ROW 
    BEGIN

    INSERT INTO log (description)
    VALUES ('One student will be inserted');
    
    IF (NEW.gender = 'Female') THEN
        SET NEW.gender = 2;
    ELSE
        SET NEW.gender = 1;
    END IF;

    END$$

DELIMITER ;
Trigger types
Trigger can run 
before
or 
after
some database event. The event can be 
insert
, 
update
or 
delete
. It means there are six types of triggers: 
before / after
insert 
before / after
update 
before / after
delete
Add FOREIGN KEY constraint after creating a table
Creates 
FOREIGN KEY
constraint with 
student_id
column and references it to 
students.id
ALTER TABLE notes 
ADD CONSTRAINT fk_student_note
    FOREIGN KEY (student_id)  
    REFERENCES students(id) 
    ON UPDATE CASCADE 
    ON DELETE RESTRICT
We can omit 
CONSTRAINT fk_student_note
sub-clause. The difference is that if we omit the sub-clause, MySQL names the constraint automatically
ALTER TABLE notes 
ADD FOREIGN KEY (student_id)  
    REFERENCES students(id) 
    ON UPDATE CASCADE 
    ON DELETE RESTRICT
Delete FOREIGN KEY by its name 
ALTER TABLE notes 
DROP FOREIGN KEY fk_student_note`;
Differences between NO ACTION and RESTRICT 
NO ACTION
is equivalent to RESTRICT. The MySQL Server rejects the delete and update operations of the parent table if there is a related foreign key value in the referenced table
FOREIGN KEY 
FOREIGN KEY
constraint protects data integrity. Let's create 
students
and 
note
tables
USE university;
DROP TABLE IF EXISTS students;
CREATE TABLE students (
    id int AUTO_INCREMENT,
    first_name varchar(50),
    last_name varchar(50),
    PRIMARY KEY(id)
);
Let's add 
FOREIGN KEY
called 
fk_student_note
to the 
notes
table
DROP TABLE IF EXISTS notes;
CREATE TABLE notes (
    id int AUTO_INCREMENT,
    student_id int,
    title varchar(50),
    description varchar(50),
    PRIMARY KEY(id),
    INDEX(student_id),
    CONSTRAINT fk_student_note  
        FOREIGN KEY (student_id)  
        REFERENCES students(id) 
        ON UPDATE CASCADE 
        ON DELETE RESTRICT
);
Results: 1022