A FOREIGN KEY in SQL is a constraint that creates a relationship between two tables. It links a column or group of columns in one table (child table) to a primary key or unique key in another table (parent table). This ensures referential integrity, meaning that any value in the foreign key column must exist in the primary key column of the parent table.
2. Creating Foreign Keys
Foreign keys can be defined:
During table creation with the CREATE TABLE statement.
After table creation with the ALTER TABLE statement.
2.1. Defining a Foreign Key in CREATE TABLE
Here’s the syntax for adding a foreign key while creating a table:
compras AS co ON c.cliente_id = co.compra_cliente_id
INNER JOIN
ivr_calls AS ivr ON ivr.ivr_num_src = c.cliente_telefono AND ivr.ivr_call_status = 1
LEFT JOIN
balance AS b ON b.balance_cliente = c.cliente_id
GROUP BY
c.cliente_id,
c.cliente_nombre,
c.cliente_telefono,
c.cliente_blacklist_status,
ivr.ivr_num_src,
ivr.ivr_num_dst,
ivr.ivr_caller_channel,
ivr.ivr_call_status,
ivr.ivr_calldate,
b.balance_creditos;";
Added a LEFT JOIN:
The balance table is joined with LEFT JOIN to ensure that even if there is no corresponding entry in the balance table, you still get results from the other tables.
Selected b.balance_creditos:
This column is included in the SELECT statement to retrieve the credit balance for each client.
Explanation:
Using a LEFT JOIN allows you to fetch client information even if they do not have any associated balance records.
RETURNS INT specifies that the function will return an integer value.
DETERMINISTIC indicates that the function always returns the same result for the same input parameters.
How to Call the Function:
SELECT add_two_numbers(10, 5) ASresult; -- This will return 15
Key Differences:
Stored Procedure: More versatile, can handle multiple result sets and output parameters.
Function: Returns a single value and can be used directly in SQL queries (like SELECT, WHERE, etc.).
Both the stored procedure and function will remain available on the MySQL server even after it restarts.
Explanation of the Code:
CREATEPROCEDURE GetLowestCost (OUT LowestCost DECIMAL(6,2))
SELECTMIN(Cost) INTO LowestCost FROM Orders;
CREATE PROCEDURE: This statement is used to define a new stored procedure in MySQL.
GetLowestCost: The name of the stored procedure being created.
(OUT LowestCost DECIMAL(6,2)):
This specifies that LowestCost is an OUT parameter.
The OUT keyword means that this parameter is used to return a value from the procedure.
DECIMAL(6,2) defines the data type of LowestCost as a decimal number with up to 6 digits, 2 of which are after the decimal point.
SELECT MIN(Cost): This part of the procedure selects the minimum value of the Cost column from the Orders table. The MIN() function returns the smallest value in a column.
INTO LowestCost: This stores the result of MIN(Cost) into the LowestCost variable, which is the OUT parameter.
FROM Orders: This indicates that the data is being selected from the Orders table.
What This Stored Procedure Does:
When you call this stored procedure, it will find the lowest cost from the Orders table and return that value in the LowestCost variable.
Example Usage:
To call the stored procedure and capture the output:
CALL GetLowestCost(@lowest);
SELECT@lowest; -- This will display the lowest cost from the Orders table.
In this example, @lowest will hold the value returned by the LowestCost OUT parameter.
Stored Procedure vs. Function:
Stored Procedures:
Can perform actions like INSERT, UPDATE, DELETE, and SELECT.
Can return multiple result sets.
Can have IN, OUT, and INOUT parameters.
Cannot be used directly in SQL statements like a SELECT.
Functions:
Primarily used to compute and return a value.
Can only return a single value.
Can be used in SQL statements (like SELECT, WHERE, etc.).
Cannot perform actions like INSERT, UPDATE, or DELETE (only SELECT).
1. Using Stored Procedures
a) Creating a Stored Procedure
Here’s an example of a stored procedure that takes two numbers as input, adds them, and returns the result.
DETERMINISTIC means the function will always return the same result for the same input.
The function returns an integer value.
b) Using the Function
You can use functions directly in SQL queries, unlike stored procedures.
sql
SELECT square_number(4); -- This will return 16
You can also use the function in other SQL statements, like WHERE or GROUP BY clauses:
sql
SELECT*FROM my_table WHERE square_number(my_column) >100;
Differences in Calling Stored Procedures and Functions
Stored Procedure:
Called using the CALL statement.
It can accept multiple input (IN) and output (OUT) parameters.
It cannot be used directly in a SELECT statement.
Example:
sql
CALL add_numbers(10, 20, @output);
Function:
Called like a built-in MySQL function (e.g., SUM, AVG).
Can return only one value.
Can be used in SELECT, WHERE, and ORDER BY statements.
Example:
sql
SELECT square_number(5);
Real-Life Example
a) Stored Procedure Example:
Let’s say you want to create a stored procedure that fetches the details of a customer by their ID.
DELIMITER //CREATEPROCEDURE get_customer_details(IN customer_id INT)
BEGINSELECT*FROM customers WHERE id = customer_id;
END//
DELIMITER ;
You can call this procedure like this:
CALL get_customer_details(123);
This would return all the details of the customer whose ID is 123.
b) Function Example:
Let’s create a function that returns the total amount a customer has spent based on their orders.
DELIMITER //CREATEFUNCTION total_spent(customer_id INT)
RETURNSDECIMAL(10,2)
DETERMINISTICBEGINDECLARE total DECIMAL(10,2);
SELECTSUM(order_total) INTO total FROM orders WHERE customer_id = customer_id;
RETURN total;
END//
DELIMITER ;
Now you can use this function in a query:
SELECT total_spent(123) AS total FROM dual;
This will return the total amount spent by the customer with ID 123.
---------------
Creating a stored procedure in a database that uses only input variables is quite straightforward. Below, I'll guide you through the process using MySQL as an example. The steps are similar in other relational databases, but the syntax might differ slightly.
MySQL allows you to define stored procedures on your databases that can execute arbitrary SQL statements whenever you invoke them, including updating data in one or more tables. This tutorial will teach you how to use stored procedures and give you some ideas about what they're good for.
What's a stored procedure?
A stored procedure is a set of SQL statements that you invoke with the CALL keyword. They can accept parameters that change how the procedure operates. Here's a simple classic example using MySQL stored procedure syntax: defining a stored procedure that inserts values into multiple tables.
This procedure inserts a row into both the employees and birthdays tables, using the generated employee ID from the first INSERT to do the second.
Let's break down the definition of this stored procedure and see what each part does.
Defining a stored procedure
The procedure we defined above looks like this. I'm commenting every element in the definition to make it clear what it does.
delimiter// -- since our stored procedure contains multiple statement separated with ";",-- we need to tell the MySQL shell not to try to execute the statement when-- it encounters a ";" like it normally would. Instead, it should wait for "//" CREATEprocedure new_employee(-- the name of our procedure is new_employeefirstchar(100),-- the first param is called "first" and is a character stringlastchar(100),-- the second param is called "last" and is a character string
birthday date)-- the third param is called "birthday" and is a dateBEGIN-- since our procedure body has multiple statements, we wrap them in a BEGIN .. END blockINSERTINTO employees (first_name, last_name)VALUES(first,last);-- insert into employees tableSET@id=(SELECT last_insert_id());-- assign the auto-generated ID from the INSERT to a variableINSERTINTO birthdays (emp_id, birthday)VALUES(@id, birthday);-- INSERT into the second tableEND;-- end of the BEGIN .. END block//delimiter;-- now that we're done defining our procedure, change the delimiter back to ";"
So a procedure has a number of parts that must be declared in the order above. They are:
The name of the procedure. Procedure names must be unique in a schema.
A list of parameters (could be none). Each parameter must have:
IN or OUT. Out-params are how MySQL returns values from stored procedures (see next section). Params are IN by default.
A name, which must be unique in the parameter list
A type, which MySQL will convert automatically if possible (like how the string "1980-02-03" got converted to a date)
BEGIN starts the body of the procedure, like a curly brace in most programming languages. This is optional if your procedure has only a single statement
One or more statements for the body of the procedure, each ending in ;
END ends the body of the procedure
Also note the use of DELIMITER. This isn't a SQL statement, it's a command to the MySQL shell. These are only necessary if you're using the MySQL shell to define your procedure. If you're using another tool, like a GUI workbench, it will already correctly interpret the ; characters in the body of the procedure.
OUT parameters
MySQL stored procedures don't use the return keyword to return a value to the caller like other programming languages. Instead, you declare parameters as OUT rather than IN, and then they're set in the procedure body. Here's a simple example.
Inside the stored procedure, it's used as a parameter that gets a value assigned to it with a SET statement.. You could also use the SELECT .. INTO syntax for this purpose.
Outside the stored procedure, the variable @count is initialized before the call, passed into the procedure, and then selected to get the result.
Generally speaking, if your procedure needs to return a value, it probably makes more sense to write it as a function instead, but there are situations where OUT parameters are important.
Variables
Stored procedures can set session variables (@var), or global variables (@@var), or local variables. The latter are defined only in the scope of an execution of a call of the procedure. Which one you use depends on what you're trying to accomplish. It's considered best practice to use the DECLARE syntax to declare local variables for the procedure, and then to return any values necessary back to the calling scope with OUT params.
Here we are declaring two local variables count1 and count2, both of the int type. The syntax is very similar to parameters, with the added wrinkle that you can define more than one variable with the same type in the same statement.
Just like any other programming language, MySQL stored procedures support conditional logic by means of a set of control flow statements like IF, LOOP, etc. Using these statements makes it possible to implement any logic you can imagine in your stored procedures.
IF
IF statements execute one of N statements depending on a condition. Multiple ELSEIF cases can follow the IF, and an optional ELSE can end the block. The THEN keyword that follows IF and ELSEIF begins a block of statements similar to BEGIN. Finally, the entire IF statement must be terminated by END IF. Let's see an example.
CREATEPROCEDURE birthday_message(
bday date,OUT message varchar(100))BEGINDECLARE count int;DECLARE name varchar(100);SET count =(SELECTcount(*)FROM birthdays WHERE birthday = bday);IF count =0THENSET message ="Nobody has this birthday";ELSEIF count =1THENSET name =(SELECT concat(first_name," ", last_name)FROM employees join birthdays
on emp_id = id
WHERE birthday = bday);SET message =(SELECT concat("It's ", name,"'s birthday"));ELSESET message ="More than one employee has this birthday";ENDIF;ENDcall birthday_message (now(),@message);
Query OK,0rows affected,1 warning (0.00 sec)SELECT@message;+--------------------------+|@message|+--------------------------+| Nobody has this birthday |+--------------------------+1rowinset(0.00 sec)call birthday_message ('1980-02-03',@message);
Query OK,0rows affected (0.01 sec)SELECT@message;+--------------------------+|@message|+--------------------------+| It's tim sehn's birthday |+--------------------------+1rowinset(0.00 sec)call birthday_message ('1985-01-10',@message);
Query OK,0rows affected (0.00 sec)
mysql>SELECT@message;+------------------------------------------+|@message|+------------------------------------------+| More than one employee has this birthday |+------------------------------------------+1rowinset(0.00 sec)
CASE
CASE statements are another way of expressing conditional logic, when the same expression is evaluated for every logical branch, similar to the switch statement found in many programming languages. We can implement the same procedure above using case statements instead. Note that you end a CASE block with a END CASE statement.
CREATEPROCEDURE birthday_message(
bday date,OUT message varchar(100))BEGINDECLARE count int;DECLARE name varchar(100);SET count =(SELECTcount(*)FROM birthdays WHERE birthday = bday);CASE count
WHEN0THENSET message ="Nobody has this birthday";WHEN1THENSET name =(SELECT concat(first_name," ", last_name)FROM employees join birthdays
on emp_id = id
WHERE birthday = bday);SET message =(SELECT concat("It's ", name,"'s birthday"));ELSESET message ="More than one employee has this birthday";ENDCASE;END
Compared to using IF and ELSEIF, the CASE version makes it clearer that the choice of execution path depends on the value of the count variable.
Loop constructs
To repeat the same set of statements more than once, use a loop construct. MySQL provides three different loop constructs to choose from: WHILE, REPEAT, and LOOP. Which one you use is mostly a matter of personal preference and how easy you find them to read for a particular situation.
For this example, we'll write a procedure that computes the Nth fibonnaci number and assigns it to an out parameter.
WHILE
First let's look at the WHILE keyword:
CREATEPROCEDURE fib(n int,out answer int)BEGINDECLARE i intdefault2;DECLARE p, q intdefault1;SET answer =1;WHILE i < n DOSET answer = p + q;SET p = q;SET q = answer;SET i = i +1;ENDWHILE;END;
Note the use of the DEFAULT keyword on the DECLARE statements, which we hadn't used before. This assigns an initial value to the variable. Unlike other languages, MySQL integer variables do not default to 0 or any other value, but instead are initialized to NULL by default (which you don't want for calculation).
REPEAT
Now that our procedure works, let's write a version using REPEAT:
CREATEPROCEDURE fib(n int,out answer int)BEGINDECLARE i intdefault1;DECLARE p intdefault0;DECLARE q intdefault1;SET answer =1;REPEATSET answer = p + q;SET p = q;SET q = answer;SET i = i +1;
UNTIL i >= n ENDREPEAT;END;
Unlike WHILE, REPEAT loops check the loop condition at the end of the loop, not the beginning. So they always execute the body of the loop at least once. Because of this, we needed to adjust our initial variable values for the n=1 and n=2 cases.
LOOP, ITERATE and LEAVE
Finally let's look at LOOP. Unlike REPEAT and WHILE, LOOP has no built-in exit condition, making it very easy to write an infinite loop. You have to use a label and code an explicit LEAVE statement to exit the loop. Here's the same procedure again, with the loop1 label applied:
CREATEPROCEDURE fib(n int,out answer int)BEGINDECLARE i intdefault2;DECLARE p, q intdefault1;SET answer =1;
loop1: LOOPIF i >= n THENLEAVE loop1;ENDIF;SET answer = p + q;SET p = q;SET q = answer;SET i = i +1;ENDLOOP loop1;END;
Note that the loop1 label occurs both before the LOOP as well as at the end of it. The LEAVE statement terminates the loop.
In addition to the LEAVE keyword, it can sometimes be useful or clearer to use the ITERATE keyword. Here's a version of the loop that uses ITERATE:
CREATEPROCEDURE fib(n int,out answer int)BEGINDECLARE i intdefault2;DECLARE p, q intdefault1;SET answer =1;
loop1: LOOPIF i < n THENSET answer = p + q;SET p = q;SET q = answer;SET i = i +1;ITERATE loop1;ENDIF;LEAVE loop1;ENDLOOP loop1;END;
Which way you choose to write a LOOP depends on what you're trying to do and the cleanest way to express it. You can also use ITERATE to conditionally skip parts of a loop under certain circumstances.
Exception handling
MySQL stored procedures support terminating execution with an error with a special SIGNAL keyword. You can also define your own named error conditions to use in stored procedures you write for readability. This can be used to halt the operation of a transaction and cause it to be rolled back, just like any native MySQL error. This can be useful when implementing complex data integrity checks.
The syntax to signal an error is unfamiliar but straightforward. This example comes straight out of the MySQL docs:
CREATEPROCEDURE p (pval INT)BEGINDECLARE specialty CONDITION FOR SQLSTATE '45000';IF pval =0THEN
SIGNAL SQLSTATE '01000';ELSEIF pval =1THEN
SIGNAL SQLSTATE '45000'SET MESSAGE_TEXT ='An error occurred';ELSEIF pval =2THEN
SIGNAL specialty
SET MESSAGE_TEXT ='An error occurred';ELSE
SIGNAL SQLSTATE '01000'SET MESSAGE_TEXT ='A warning occurred', MYSQL_ERRNO =1000;
SIGNAL SQLSTATE '45000'SET MESSAGE_TEXT ='An error occurred', MYSQL_ERRNO =1001;ENDIF;END;
What are stored procedures used for?
Now that you understand how to define a stored procedures in MySQL, you might be asking yourself why you would want to. What are they good for? What do people use these things to do? Stored procedures are a general tool you can use to do all sorts of things, but here are some of the more common ones in our experience.
System maintenance
Sometimes it's convenient or desirable to encode business logic directly in the database, in the same place where the data is defined and stored. This approach means that the logic to maintain key database properties isn't spread out somewhere else in a distributed system (like a batch job), but directly in the database itself.
For example, consider an online storefront, where you store an order history for every customer. It might make sense to archive details about these orders, like tracking information and return requests, once they get old enough that it's unlikely they'll be useful any longer. Archiving old data means a smaller database, which means lower storage bills for your backups and possibly faster execution time on queries.
Here's a procedure that deletes order information older than a cutoff provided:
CREATEPROCEDURE archive_order_history(cutoff date)BEGIN-- sanity check: don't proceed if we are given a date in the last yearIF datediff(now(),date)<365THEN
SIGNAL SQLSTATE '45000'SET MESSAGE_TEXT ='attempted to archive recent order history';ENDIF;-- clear out any orders older than the cutoff, lots of tables to delete from hereDELETEFROM order_updates WHERE update_date < cutoff;DELETEFROM order_history WHERE order_date < cutoff;DELETEFROM order_feedback WHERE feedack_date < cutoff;DELETEFROM support_requests WHERE order_date < cutoff;END
Note that this procedure encapsulates the logic of deleting old data from many tables into a single procedure. As the database schema continues to evolve, new logic can be added here as necessary.
CREATE EVENT archive_order_history_event
ON SCHEDULE
EVERY 1DAY
STARTS TIMESTAMP(CURDATE(),'02:00:00')DOBEGINCALL archive_order_history(DATE_SUB(CURDATE(),INTERVAL1YEAR));END;
You can also alter this procedure to perform work in small chunks, if the data is truly massive and there's any concern about interfering with other write throughput. Just run the DELETE with a LIMIT clause in a loop:
CREATEPROCEDURE archive_order_history(cutoff date)BEGINDECLARE count intdefault1;WHILE COUNT >0DODELETEFROM order_history WHERE order_date < cutoff LIMIT100;-- See how many are left to delete-- Make sure to stop our table scan once we find a matching rowSET count =(SELECTcount(*)FROM(SELECT order_id FROM order_history WHERE order_date < cutoff LIMIT1));COMMIT;-- commit our batch of 100 deletesENDWHILE;END
Time-based compliance
Some regulations, such as GDPR, require online businesses to remove customer data upon request or face heavy fines. Usually there is a grace period of some number of days for the customer to change their mind, during which time you don't want to delete their data if possible. Here again it may make sense to implement a stored procedure to encapsulate the logic of what a deletion request does, then run it on a timer.
First, create a table that logs when a customer requests data removal:
Now define a procedure to delete all personally identifiable data associated with a customer.
CREATE PROCEDURE delete_customer(id_to_remove int)
BEGIN
-- clear out any customer information
UPDATE cust_info SET first_name = "REDACTED", last_name = "REDACTED"
WHERE cust_id = id_to_remove;
DELETE FROM customer_addresses WHERE cust_id = id_to_remove;
DELETE FROM reviews WHERE cust_id = id_to_remove;
UPDATE order_history SET delivery_instructions = "REDACTED"
WHERE cust_id = id_to_remove;
END
Notice that this procedure completely encapsulates the logic required to comply with the regulation. We can't just delete the customer record because that would cause referential constraint violations, or cascading deletion, in other tables storing information we're required to keep for financial reasons. Instead, we perform a mix of deletions where possible and updates to redact personally identifiable information where it's not.
Finally we schedule an event to delete customers whose time is up:
CREATE EVENT customer_removal_event
ON SCHEDULE
EVERY 1DAY
STARTS TIMESTAMP(CURDATE(),'03:00:00')DOBEGINDECLARE cust_id int;
cust_loop: REPEATSET cust_id =(SELECT customer_id FROM removal_requests
WHERE removal_date >=now()limit1);IF cust_id ISNULLLEAVE cust_loop;CALL delete_customer(cust_id);
UNTIL cust_id ISNULLENDREPEAT cust_loop;-- redundant end condition on this loopEND;
Data integrity checks and complex updates
For some complex table relationships, it might be desirable to define the logic of inserting or updating the data in the tables in a procedure. This has the advantage of allowing you to execute arbitrarily complex logic for validation, things that can't be expressed with simple FOREIGN KEY or CHECK constraints.
Consider a library system with tables for books, holds, and checkouts.
CREATETABLE books(
isbn varchar(100)primarykey,
title varchar(255),...);CREATETABLE holds(
isbn varchar(100),
patron_id bigint,uniquekey(isbn, patron_id));CREATETABLE checkouts(
isbn varchar(100),
patron_id bigintdatedate,KEY(isbn, patron_id));
We can define a procedure to handle all the business logic we want to consider when checking out a book:
CREATEPROCEDURE checkout(
checkout_isbn varchar(100),
borrower_patron_id bigint)BEGINDECLARE current_checkouts int;SET current_checkouts =(SELECTCOUNT(*)FROM checkouts WHERE patron_id = borrower_patron_id);IF current_checkouts >20THEN
SIGNAL SQLSTATE '45000'SET MESSAGE_TEXT ='Too many books checked out';END;DELETEFROM holds WHERE isbn = checkout_isbn and patron_id = borrower_patron_id;INSERTINTO checkouts VALUES(checkout_isbn, borrower_patron_id,now());END;
It's also possible (and more common) to define this sort of logic in application code. But the advantage of defining logic at the database level is that multiple applications can use this stored procedure without needing to each understand the business logic around checking out a book. The logic can be updated in one place when policy like the max number of books per patron changes.
In modern architectures, this role is more typically filled by a web service than a database, but there are still a surprising number of older systems out there where this isn't an option, where multiple legacy applications still connect directly to and update a shared database. For these systems, it's useful to have this kind of trick up our sleeves.
Advanced topic: cursors
The examples above all limit results from SQL queries in procedures to a single row, and usually a single value. There's a lot you can do with this pattern, but for some situations you really need to examine multiple rows in a loop to answer some question. For those situations, MySQL provides cursors. For obvious reasons, it's a lot more difficult to write correct stored procedures that require cursors than ones that don't.
This simple example is straight out of the MySQL docs.
CREATEPROCEDURE curdemo()BEGINDECLARE done INTDEFAULTFALSE;DECLARE a CHAR(16);DECLARE b, c INT;DECLARE cur1 CURSORFORSELECT id,dataFROM test.t1;DECLARE cur2 CURSORFORSELECT i FROM test.t2;DECLARECONTINUEHANDLERFORNOT FOUND SET done =TRUE;OPEN cur1;OPEN cur2;
read_loop: LOOPFETCH cur1 INTO a, b;FETCH cur2 INTO c;IF done THENLEAVE read_loop;ENDIF;IF b < c THENINSERTINTO test.t3 VALUES(a,b);ELSEINSERTINTO test.t3 VALUES(a,c);ENDIF;ENDLOOP;CLOSE cur1;CLOSE cur2;END;
