Lecture 14 Monday, October 27, 2014

Constraints •  We talked earlier this semester about foreign keys,

and we skipped over some things (like removing them) despite covering it with primary keys

•  We also may have encountered a “foreign key constraint” error while performing an UPDATE or DELETE statement in the Sakila database we’ve used for most of our labs

1

Example Tables •  Suppose we have the following tables, as we would have created

at the beginning of the semester, specifically noting the foreign key reference from registrations.win to students.win: !CREATE TABLE students ( !

win INT NOT NULL, !firstname VARCHAR(40) NOT NULL, !lastname VARCHAR(40) NOT NULL, !PRIMARY KEY(win)); !

CREATE TABLE registrations ( !

win INT NOT NULL, !crn INT NOT NULL, !grade DECIMAL(2,1) DEFAULT NULL, !FOREIGN KEY (win) REFERENCES students(win), !FOREIGN KEY (crn) REFERENCES courses (crn)); !

2

Example Tables students registrations

3

win firstname lastname

900010001 John Smith

900020002 Mike Johnson

win crn grade

900010001 30001 NULL

900020002 30002 NULL

Foreign Key Definition •  Specifically, let’s look at the foreign key definition

highlighted: FOREIGN KEY (win) REFERENCES students(win)

•  Right now, other than for informational purposes, this definition really isn’t doing anything other than telling us that the field registrations.win matches up to students.win

•  You can still do joins with fields that are not defined as foreign keys

4

Foreign Key Definition •  We can add constraints which perform automated actions

when the referenced field is updated or deleted

•  Our syntax for the definition that goes in CREATE TABLE is (with the “new syntax” highlighted): CONSTRAINT [constraintname] FOREIGN KEY (column) REFERENCES reftable(refcolumn) [ON DELETE action] [ON UPDATE action] !

•  ON DELETE and ON UPDATE perform the action when a DELETE or UPDATE command is run on the referenced table on an affected record

•  The three different possible actions are…

5

RESTRICT/NO ACTION •  Putting RESTRICT is the same as NO ACTION !

•  Means restrict the query or perform no action

•  Specifying RESTRICT/NO ACTION is the same as omitting ON DELETE or ON UPDATE,

•  If you attempt to change the referenced table by deleting the record with that key or updating that key, nothing will happen – you will get the following error: ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails !

6

RESTRICT/NO ACTION •  Suppose we set the constraint on the foreign key

registrations.win to students.win to ON DELETE RESTRICT ON UPDATE RESTRICT !

•  Both of these queries would produce that given error: o  DELETE FROM students WHERE win = 900010001; !o  UPDATE students SET win = 900050005 WHERE win =

900010001; !

7

CASCADE •  Cascade (in general) means “a process whereby something,

typically information or knowledge, is successively passed on”

•  When CASCADE is used with ON DELETE, If you delete a record from the referenced table with the key, it will also delete the records in that table linked to that foreign key

•  When CASCADE is used with ON UPDATE, If you change a key in the referenced table, it will also change the key in the table that is using that foreign key

8

CASCADE •  Suppose we set the constraint on the foreign key

registrations.win to students.win to ON DELETE CASCADE ON UPDATE CASCADE !

•  Then you ran the following queries: o  DELETE FROM students WHERE win = 900010001; !o  UPDATE students SET win = 900222222 WHERE win =

900020002; !

•  The result would be…

9

CASCADE students registrations

10

win firstname lastname

900222222 Mike Johnson

win crn grade

900222222 30002 NULL

SET NULL •  If you attempt to change the referenced table by deleting

the record with that key or updating that key in the referenced table, it will set the key in the referencing table to NULL

•  Suppose we set the constraint on the foreign key registrations.win to students.win to ON DELETE SET NULL ON UPDATE SET NULL !

•  Then you ran the following queries: o  DELETE FROM students WHERE win = 900010001; !o  UPDATE students SET win = 900222222 WHERE win =

900020002; !

•  The result would be…

11

SET NULL students registrations

12

win firstname lastname

900222222 Mike Johnson

win crn grade

NULL 30001 NULL

NULL 30002 NULL

Inserting or Updating the Referencing Table

•  The previous slides outline how the constraints affect updating/deleting records in the referenced table

•  In the referencing table, you will get the following error if you attempt to add a record with a value not in the referenced table, or update a field in an existing record to a value not in the referenced table: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails !

13

ALTER TABLE •  When we talked about the ALTER TABLE command,

we skipped over removing foreign keys because we had not covered constraints

•  To remove a foreign key: ALTER TABLE tablename DROP FOREIGN KEY constraintname; !

14

With MySQL Dumps •  When I provided a dump file for the Sakila database,

you received the error shown on a previous slide for inserting records in a referencing table

•  Why? o  mysqldump puts the table creation and insertion statements in

alphabetical order by table o  If a referencing table falls alphabetically before the referenced table,

it will give an error

15

SET FOREIGN_KEY_CHECKS

•  To ignore foreign key constraints, run the following command: SET FOREIGN_KEY_CHECKS = 0; !

•  When they are turned off, it is as if that constraint was never even listed in the CREATE TABLE syntax

•  To turn them back run, run the following command: SET FOREIGN_KEY_CHECKS = 1; !

16

VIEWS •  The only object we’ve covered so far are tables, so now we will

move on to views

•  Think of views as taking a SELECT statement and aliasing it to a single table

•  To define a view, we use: CREATE VIEW viewname AS select_statement; !

•  Why? o  It makes it much easier when using you are frequently joining the same set of

tables together o  Suppose you have the following situation:

•  You have a query that needs to be run on applications built in to several different platforms

•  You need to update the query to change something in the query •  You can just update the view rather than having to update every application

17

Example Tables •  Suppose we have the students and registrations

tables from earlier in the lecture, as well as a courses table: !CREATE TABLE courses ( !

crn INT NOT NULL, !sub VARCHAR(4) NOT NULL, !num VARCHAR(5) NOT NULL, !sec VARCHAR(3) NOT NULL, !PRIMARY KEY(crn));

18

VIEWS •  The query to see which courses each student is registered

for would be: SELECT firstname, lastname, sub, num, sec FROM students JOIN registrations USING (win) JOIN courses USING (crn); !

•  To make this a view, we would use: CREATE VIEW stunamesandregs AS SELECT firstname, lastname, sub, num, sec FROM students JOIN registrations USING (win) JOIN courses USING (crn); !

•  We can now run the following query to get the list of courses each student: SELECT * FROM stunamesandregs; !

19

VIEWS •  We can use anything that we have used so far in the

SELECT statement that is defining the view, including JOIN, WHERE, IN, GROUP BY/aggregating functions, ORDER BY, LIMIT, and so on

•  You can then use views as if it were a regular table, including joining views with other tables, search for specific records using WHERE, sort them with ORDER BY, and so on

20

VIEWS •  Updating data in the tables referenced in the view

definition automatically updates the view

•  Some views are updatable – that is, you can run SQL statements using the commands that change data, and they will link back to the source tables and update the data in them

•  But not all views are updatable – we will go over which are later in the semester if we have time

21