10SQLDML

SQL - Data Manipulation Language (DML)

Outline

DELETE statement
INSERT statement
UPDATE statement

DELETE statement belong to a group of Data Manipulation Language (DML) statements. DML statements can be used to modify the contents of a database. DML statements do not change the structures of a database. Modifications performed with DML statements can be reversed with ROLLBACK statement or it be permanently saved with COMMIT statement.

DELETE statement, deletes from a given relational table, all rows that satisfy a given condition. It is syntactically correct to say: DELETE table-name or DELETE FROM table-name. WHERE clause provides a condition that must be satisfied fro a row to be deleted. Missing WHERE clause means that we delete all rows from a relational table.

A computational model of the DELETE statement, is an iteration over all rows, in a relational table determined by: the DELETE FROM, clause.

A Boolean expression in WHERE condition, consists of the terms being the simple comparisons of an attribute and a constant, or the comparisons of two attributes. Such terms are joined with: and, or, not Boolean operations. The parenthesis can be used to precisely determine an order of evaluation of a Boolean expression.

The rows deleted by the DELETE statement, CAN be restored by the ROLLBACK statement, unless processing of the DELETE statement, has been committed by the COMMIT statement.

DELETE statement, does not drop a relational table.

DELETE statement, does not release persistent storage occupied by the deleted rows.

DELETE statement

Functionality:
- DELETE statement deletes all rows that satisfy a given condition
- The rows deleted by DELETE statement CAN be restored by ROLLBACK statement unless DELETE has been committed by COMMIT statement
- DELETE statement DOES NOT delete a table
- DELETE statement DOES NOT release disk storage occupied by the deleted rows

In this presentation, we use the same sample database, that contains information about the departments and about the courses offered by the departments.

Information about the departments is kept in a relational table: DEPARTMENT.

The relational table: DEPARTMENT, consists of six columns: department name (name), department code (code), total staff number, chairperson (chair ), and budget.

A column: name, is a primary key and the values in a column: name, are the variable size strings, no longer than 50 characters, A column: name, is a primary key and because of that all values in the column are mandatory (NOT NULL).

A column: code, is a candidate key and all it’s values are fixed size strings 5 characters long. If the codes are shorter than 5 characters, then such values are padded with a sequence of blanks, up to 5 characters long. The values in a column code are mandatory (NOT NULL).

The values in a column: total staff, are integer values, in a range from one to fifty. The values in a column: total staff, are mandatory (NOT NULL). Total staff number per department cannot be greater than 50.

A column: chair, contains the variable size strings, no more than 50 characters long, and all its values are optional (NULL)

A column: budget, contains the real numbers, no more than 9 digits long, with at most one position after a decimal dot. The values in a column budget are mandatory (NOT NULL).

A relational table COURSE contains the descriptions of course.

A description of course consists of a course number, title, credits and name of department that offers a course.

A column course number is a unique identifier of each course. A value of course number is a fixed length string of characters, 7 characters long.

A column title contains titles of the courses. A title is a variable length string of characters, maximum 200 characters long.

A column credits determine the credits granted fro passing a course.The granted credits can be equal to either 6 or 12.

A column offered_by contains the names of departments that offer courses.

A column offered_by is a foreign key referencing the names of departments in. column name in a table DEPARTMENT.

DELETE statement

Sample database:

CREATE TABLE DEPARTMENT(
 name               VARCHAR(50)        NOT NULL,
 code               CHAR(5)            NOT NULL,
 total_staff_number DECIMAL(2)         NOT NULL,
 chair              VARCHAR(50)            NULL,
 budget             DECIMAL(9,1)       NOT NULL,
  CONSTRAINT dept_pkey PRIMARY KEY(name),
  CONSTRAINT dept_ckey1 UNIQUE(code),
  CONSTRAINT dept_ckey2 UNIQUE(chair),
  CONSTRAINT dept_check1 CHECK (total_staff_number BETWEEN 1 AND 50) );

 CREATE TABLE COURSE(
 cnum               CHAR(7)           NOT NULL,
 title              VARCHAR(200)      NOT NULL,
 credits            DECIMAL(2)        NOT NULL,
 offered_by         VARCHAR(50)           NULL,
  CONSTRAINT course_pkey PRIMARY KEY(cnum),
  CONSTRAINT course_check1 CHECK (credits IN (6, 12)),
  CONSTRAINT course_fkey1 FOREIGN KEY(offered_by)
                        REFERENCES DEPARTMENT(name) ON DELETE CASCADE );

We start from few simple applications of the DELETE statement. Assume, that we would like to delete a description of course with a number (attribute cnum) equal to CSCI235.

In the first example on the present slide, all rows where a value in a column cnum is equal to csci235 are removed from a relational table COURSE.

In the second example all rows where a value in a column: credits, is equal to 12, or a value in a column: title, contains a word: database (no matter if it is typed in uppercase or lowercase), are removed from a relational table COURSE.

To avoid the large number of conditions when a word: database, is stored in an uppercase or a mixture of uppercase and lowercase letters, a value in a column title is converted to uppercase and compared with a pattern in an uppercase format. A row function: UPPER, converts a value in a column: title, into an uppercase format.

A text constant: "%DATABASE%", is a text pattern. Any text that contains a word: database, in uppercase, matches the pattern. A percentage character denotes any, possibly empty, sequence of characters.

A comparison operator: LIKE, compares a text with a pattern. If a text matches a pattern a comparison evaluates to: true.

For example, the following texts match a pattern given in DELETE statement: DATABASE SYSTEMS, I LIKE DATABASE, MY FIRST DATABASE and so on.

In the next example, a value in a column: total staff number, is compared with an integer constant. WHERE condition evaluates to true, if a value in a column: total staff number, is less than five.

Finally, delete statement without WHERE condition, unconditionally deletes all rows from a relational table.

DELETE statement

Examples:
- Delete a course with a code CSCI235
- Delete all courses with 12 credits or such that their title includes a word "database"
- Delete all departments where total number of staff members is less than 5
- Delete all departments

Assume, that we would like to delete a department of Physics from a relational table: DEPARTMENT. CREATE TABLE statement that was used to create a relational table: COURSE, is listed at the top of the present slide. Note a foreign key: COURSE_FKEY1 in a relational table: COURSE. A foreign key: COURSE_FKEY1, references a primary key in a relational table: DEPARTMENT. It means, that all values in a column: OFFERED_BY, must be earlier entered into a column: NAME, in a relational table: DEPARTMENT.

A simple DELETE statement is given below a statement that creates a relational table: COURSE.

If a relational table: COURSE, contains at least one row that describes a course offered by a department of Physics, then deletion of the department would create an orphan row, that references nothing in a relational table: DEPARTMENT. Accordingly to the principles of the relational data model such situation must not happen ! This is why, an attempt to delete information about a department of physics in a situation when there is at least one course offered by physics, is rejected by a database system.

SQL processor displays an error message: Cannot delete or update a parent row: a foreign key constraint fail.

There are three ways how this problem can be solved.

First, we can delete all courses offered by a department of Physics before deletion of the department.

Second, assuming, that there will be some other departments that would like teach courses offered by Physics, we may not wish to delete the courses. To preserve the courses we can replace in a relational table: COURSE, a name of department: Physics in a column: offered_by, with NULL. Note, that it can be done because a foreign key can be null !

The third solution has the same effects as the first one and it is explained on the next slide.

DELETE statement

BEWARE !!!

CREATE TABLE COURSE(
 cnum               CHAR(7)           NOT NULL,
 title              VARCHAR(200)      NOT NULL,
 credits            DECIMAL(2)        NOT NULL,
 offered_by         VARCHAR(50)           NULL,
  CONSTRAINT course_pkey PRIMARY KEY(cnum),
  CONSTRAINT course_check1 CHECK (credits IN (6, 12)),
  CONSTRAINT course_fkey1 FOREIGN KEY(offered_by)
                        REFERENCES DEPARTMENT(name) );


DELETE FROM DEPARTMENT WHERE name='Physics';


--------------
DELETE FROM DEPARTMENT WHERE name='Physics'
--------------

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key 
constraint fails (`csit115`.`COURSE`, CONSTRAINT `course_fkey1` 
FOREIGN KEY (`offered_by`) REFERENCES `DEPARTMENT` (`name`))

In the third solution, we can automatically delete the courses offered by a department of Physics, when the department is deleted from a relational table: DEPARTMENT.

To achieve that, we must add: ON DELETE CASCADE clause, to a referential integrity constraint: COURSE_FKEY1. If ON DELETE CASCADE clause is appended to a specification of a foreign key, then deletion of a row with the respective value of the primary key, triggers automatic deletion of the rows with the same values of a foreign key.

If ON DELETE CASCADE clause is not used in a specification of a foreign key, then an order in which the rows are deleted is important and the rows with the same values of a foreign key must be deleted first. It means, that first we have to process the DELETE statement that removes from a relational table: COURSE, all courses offered by a department of Physics.

And then, we can remove a department of Physics from a relational table: DEPARTMENT.

In the future, we shall present the more advanced versions of the DELETE statement, that use the complex queries within WHERE condition.

DELETE statement

BEWARE !!!
- If ON DELETE CASCADE clause is not used in a specification of a foreign key then an order in which the rows are deleted is important !!!
- If ON DELETE CASCADE clause is used in a specification of a foreign key then deletion of a row with a respective value of primary key triggers automatic deletion of the rows with the same value of a foreign key
- Otherwise, the rows with the same value of a foreign key must be deleted first

SQL - Data Manipulation Language (DML)

INSERT statement, inserts a new row into a relational table and automatically verifies the consistency constraints related to the new row.

A new row is inserted into a random location into a relational table. However, a behaviour of a typical relational database systems is such that that a new row is inserted at the bottom of a relational table. It is because after a relational table is created few empty data blocks are automatically allocated. The new rows are initially inserted into empty data block. However, after some rows are deleted an empty space is available in already allocated and used data blocks. Such empty space can be later on used for insertions into the random locations in a relational table.

Of course, a relational table is still a set of unordered rows and to bet on a row being inserted at the bottom of a table is very risky.

Database system verifies all consistency constraints before insertion of a row. If evaluation of at least one of consistency constraints fails a row is not inserted into a relational table.

INSERT statement

Functionality:
- INSERT statement inserts a new row into a relational table and automatically verifies the consistency constraints

INSERTE

We again use the same sample database, that contains information about the departments and about the courses offered by the departments.