Update database and Deal with Constraint Violations
Tables can be updated by the following operations :
• INSERT
• UPDATE
• DELETE
• Integrity constraints should not be violated by the update operations.
• Several update operations may have to be grouped together.
Deal with Constraint Violations
• Updates may propagate to cause other updates automatically. This
may be necessary to maintain integrity constraints.
In case of integrity violation, several actions can be taken:
• Cancel (the operation that causes the violation )
[RESTRICT or REJECT ]
• Perform the operation but inform the user of the violation
• Trigger additional updates so the violation is corrected
[CASCADE OR SET NULL ]
Possible Violations for Each Operation
▸INSERT may violate any of the constraints:
▹Domain constraint:
if one of the attribute values provided for the new table is not of the
specified attribute domain
▹Key constraint:
if the value of a key attribute in the new table already exists in another table
in the relation
▹Referential integrity:
if a foreign key value in the new table references a primary key value that
does not exist in the referenced relation
▹Entity integrity:
if the primary key value is null in the new table
Possible Violations for Each Operation
▸DELETE may violate only referential integrity:
If the primary key value of the table being deleted is referenced from other tables in
the database
RESTRICT, CASCADE, SET NULL
■ RESTRICT option: reject the deletion
■ CASCADE option: replace the new primary key value into the foreign
keys of the referencing tables
■ SET NULL option: set the foreign keys of the referencing tables to NULL
▹One of the above options must be specified during database design for each
foreign key constraint
Possible Violations for Each Operation
UPDATE may violate domain constraint and NOT NULL constraint on an attribute being
modified:
Any of the other constraints may also be violated, depending on the attribute being updated:
▹Updating the primary key (PK):
Like a DELETE followed by an INSERT
Need to specify similar options to DELETE
▹Updating a foreign key (FK):
May violate referential integrity
▹Updating an ordinary attribute (neither PK nor FK):
Can only violate domain constraints
ON DELETE Action
•ON DELETE RESTRICT: we can not delete the information of a referenced table
if there is a reference to this information from the referencing table
•ON DELETE CASCADE: this action deletes the rows in the referencing table that is
corresponding to the row deleted from the referenced table.
•ON DELETE SET NULL: This action sets the rows in the referencing table
to NULL when the corresponding row in the referenced table is deleted
To execute this action, the foreign key columns must be nullable.
•ON DELETE SET DEFAULT: This action sets the default values for the rows in the
Referencing table that have the corresponding rows in the Referenced table deleted.
To execute this action, the foreign key columns must have default definitions.
Note that a nullable column has a default value of NULL if no default value is specified
ON UPDATE Action
• ON UPDATE RESTRICT :
• ON UPDATE CASCADE: This action updates the corresponding rows in the
referencing table when the rows in the referenced table are updated.
• ON UPDATE SET NULL: This action sets the rows in the referencing table
to NULL when the corresponding row in the referenced table is updated. Note that the
foreign key columns must be nullable for this action to execute.
• ON UPDATE SET DEFAULT: This action sets the default values for the rows in the
Referencing table that have the corresponding rows in the Referenced table updated
Identification FOREIGN KEY
CREATE TABLE table name
(
Field1 name Data Type Constraint(NULL/NOT NULL),
Field2 name Data Type Constraint ,
Field3 name Data Type Constraint ,
Field4 name Data Type Constraint ,
CONSTRAINT fk_GHJH FOREIGN KEY (Field name)
REFERENCES table name (Field name)
ONDELETE SET DEFAULT
ONUPDATE CASCADE
);
Columns
(ALTER) Statement
1- ADD
ALTER TABLE Table name
ADD field name Data TYPE column_constraint,
field name Data TYPE column_constraint;
Columns
(ALTER) Statement
2- Drop
ALTER TABLE Table name
DROP COLUMN field name ;
ALTER TABLE Table name
DROP COLUMN field name , field name;
Columns
(ALTER) Statement
3- Modify
ALTER TABLE Table name
ALTER COLUMN field name Data TYPE
Constraints
(ALTER) Statement
1- ADD
ALTER TABLE Table name
ADD CONSTRAINT constraint name primary key (Field name)
CONSTRAINTs
(ALTER) Statement
2- Drop
ALTER TABLE Table name
DROP CONSTRAINT constraint name ;
SQL (Structured Query Language)
(DML)
SQL (Structured Query Language)
(SELECT) Statement
The purpose of the SELECT statement is to retrieve and
display data from one or more database tables. It’s an
extremely powerful command and it’s also the most
frequently used SQL command.
SQL (Structured Query Language)
(SELECT) Statement
The sequence of processing in a SELECT statement is:
SELECT : specifies which columns are to appear in the output;
FROM : specifies the table or tables to be used;
WHERE : filters the rows subject to some condition;
GROUP BY : forms groups of rows with the same column value;
HAVING : filters the groups subject to some condition;
ORDER BY : specifies the order of the output.
Queries in SQL
▸Structure Of ( SQL SELECT statement )
SELECT >attribute list<
FROM >table list<
WHERE >condition<
• Attribute list: list of attribute names whose values are to be retrieved by the query
• Table list is a list of the tables names required to process the query
• Condition is a conditional (Boolean) expression that identifies the tables to be
retrieved by the query
SQL (Structured Query Language)
(SELECT) Statement
Staff (staffNo, name, position, salary, branchNo)
Video (catalogNo, title, category, dailyRental, price, directorNo)
Director (directorNo, directorName)
Actor (actorNo, actorName)
Role (actorNo, catalogNo, character)
RentalAgreement (rentalNo, dateOut, dateReturn, memberNo, videoNo)
VideoForRent (videoNo, available, catalogNo, branchNo)
SQL (Structured Query Language)
(SELECT) Statement
Selecting All Columns or Specific Columns:
SELECT column, column, ……
FROM Table;
SQL (Structured Query Language)
(SELECT) Statement
Selecting All Columns or Specific Columns:
List the full details of all videos :
SELECT catalog_No, title, category, Dailyrential, price, directorNo
FROM video;
When you want to list all columns of a table, you can use an asterisk (*) in place of the
column names
Select *
FROM video;
SQL (Structured Query Language)
(SELECT) Statement
Results
SQL (Structured Query Language)
(SELECT) Statement
Selecting Specific, all rows:
SELECT catalog_No, title, Dailyrential
FROM test_1;
SQL (Structured Query Language)
(SELECT) Statement
List all video categories.
SELECT category
FROM Test_1;
The result table is shown .Note that there are
several duplicate values (by default, SELECT does
not eliminate duplicate values). To eliminate
duplicates, we use the DISTINCT keyword and by
rewriting the above query as:
SQL (Structured Query Language)
(SELECT) Statement
Select Distinct Columns
List all video categories.
SELECT DISTINCT category
FROM Test_1;
SQL (Structured Query Language)
(SELECT) Statement
Select Distinct Columns
List all video categories.
SELECT DISTINCT category , price
FROM Test_1;
SQL (Structured Query Language)
(SELECT) Statement
Where (Limiting Rows Using a Selection)
Select Column , Column
From Table
Where (one Condition or more condition)
SQL (Structured Query Language)
(SELECT) Statement
Where (Limiting Rows Using a Selection)
1- In SQL, the following simple comparison operators are available:
• = equals < > is not equal to
• < is less than <= is less than or equal to
• > is greater than > = is greater than or equal to
• != is not equal
2- More complex predicates can be generated using the logical operators AND,
OR, and NOT,
SQL (Structured Query Language)
(SELECT) Statement
Where (Limiting Rows Using a Selection)
SELECT staffNo, name, position, salary
FROM Staff
WHERE salary > 40000;
SQL (Structured Query Language)
(SELECT) Statement
Where Limiting Rows Using a Selection
SELECT staffNo, name, position, salary
FROM Staff
WHERE salary >= 45000 AND salary <= 50000;
SQL (Structured Query Language)
(SELECT) Statement
Where Limiting Rows Using a Selection
Using the BETWEEN Operator.
SELECT staffNo, name, position, salary
FROM Staff
WHERE salary BETWEEN 45000 AND 50000;
SQL (Structured Query Language)
(SELECT) Statement
Where Limiting Rows Using a Selection
Using the NOT BETWEEN Operator.
SELECT staffNo, name, position, salary
FROM Staff
WHERE salary NOT BETWEEN 45000 AND 50000;
SQL (Structured Query Language)
(SELECT) Statement
Where Limiting Rows Using a Selection
SELECT catalogNo, title, category
FROM Video
WHERE category = ‘Action’ OR category = ‘Children’;
SQL (Structured Query Language)
(SELECT) Statement
Where Limiting Rows Using a Selection
Using the IN Operator.
SELECT catalogNo, title, category
FROM Video
WHERE category IN (‘Action’, ‘Children’);
SQL (Structured Query Language)
(SELECT) Statement
Where Limiting Rows Using a Selection
Using the NOT IN Operator.
SELECT catalogNo, title, category
FROM Video
WHERE category NOT IN (‘Action’, ‘Children’);
