Transcript SQL or SEQUEL - (Structured English Query Language)

Constraints and Views
Chap. 3-5 continued
Integrity constraints in Oracle
Insert, delete or update can violate a referential integrity
constraint
• A NOT NULL constraint
– prohibits a database value from being null.
• A unique constraint –
– allows some values to be null.
• A primary key constraint
– combines a NOT NULL constraint and a unique constraint in a
single declaration.
• A foreign key constraint
– requires values in one table to match values in another table.
• A check constraint
– requires a value in the database to comply with a specified
condition.
Check Constraint
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
State char(2),
CONSTRAINT chk_Person CHECK (P_Id>0 AND State = ‘AL’)
)
Violation of Integrity Constraints
• What operations can violate integrity
constraints?
– Insert
– delete
– update
Violation of Integrity Constraints
• If any constraints are violated, the insert,
delete, update is rejected
• Unless ...
Qualified options for Violation
• SQL allows qualified options to be specified for the
foreign key - on delete or update (includes insert)
– Set null
– Cascade
– Set default
• Not all options available in Oracle
– In Oracle, Set null and Cascade on delete are
available
– SQLServer also allows on delete and on update
Oracle
• If do not include ON DELETE clause
– then Oracle does not allow you to delete referenced key values
in the parent table that have dependent rows in the child table.
– This means the delete is rejected
• If include ON DELETE
– Specify CASCADE if you want Oracle to remove all tuples with
dependent foreign key values.
– Specify SET NULL if you want Oracle to convert
dependent foreign key values to NULL.
Example of cascade
create table casc (id int unique, age int not null, dno int,
constraint fk foreign key (dno)
references department (dnumber)
on delete cascade);
Examples
Relational Views
• A view - a virtual table that is derived from other
tables (base tables or can be another view)
• A view can be used to simplify frequent queries
e.g. a join condition
• A view does not necessarily exist in physical
form
• There is no limit on querying a view
(limits on an update to a view)
• Views are useful for security and authorization
mechanisms
Create View
• View attribute names are inherited from
other tables
• If aggregate functions are the result of
arithmetic operations, they must be
renamed
• Views can be defined using other views
Create view
CREATE VIEW view_name [(col1 {, col2})]
AS SELECT col1 {, col2}
FROM (table1| view1) {, table2 | view2}
WHERE search_condition
Multiple table views
• To create a view from multiple tables
Create View Works_on1
As Select fname, lname, pname, hours
From Employee, Project, Works_on
Where ssn = essn and pno = pnumber;
Create View
Create a view to list for each department: dname,
number of employees and total salary paid out
Create view Name (cols)
As Select query
Views
• Queries on View - same as queries on base
tables
• Retrieve the last and first names of all
employees who work on ProjectX
Maintain views
• 2 strategies to maintain views:
– View is stored as a temporary table for future
queries called view materialization
• view is not realized at the time of the view
definition but when specify the query
– Another strategy is to modify the view query
into a query on the underlying base table
called query modification
• DBMS keeps views up-to-date – how?
Views
• A view is removed by using the DROP VIEW
command.
Drop View Works_on1;
Drop View Dept_info;
Updating views
• If specify an update to a view, should it update
the corresponding table?.
Create View Emp
As Select fname, lname, ssn, dno
From Employee
Update Emp
Set dno = 1
Where lname = ‘English’
Updating views
• It may not make sense to update some
views – why?
• Cannot always guarantee that a view can
be updated
Views
• When would it not make sense?
• General Rule: (true for ORACLE)
– View with aggregate functions are not
updatable
– Views with more than one table using joins is
not updatable
– View with one defining table is updatable if
the view attributes contain a primary key
Views
• How to represent derived attributes?
– Fig. 7.15
Triggers
• Suppose
– You want to make sure values you insert are
correct (e.g. hours > 0)
– You want to know when update an
employee’s salary if it is > than supervisor’s
• You can define a trigger to perform an
action when a specific event occurs
Triggers
• You can create triggers to be fired on any
of the following:
– DML statements (DELETE, INSERT,
UPDATE)
– DDL statements (CREATE, ALTER, DROP)
– Database operations (SERVERERROR,
LOGON, LOGOFF, STARTUP, SHUTDOWN)
Example
create trigger tr1 after insert on works_on
referencing new x
for each row when (x.hours < 1)
begin raise_application_error(-20003,'invalid hours on
insert'); // insert was performed but error printed
end;
Create Trigger
CREATE TRIGGER trigger_name {BEFORE | AFTER}
{INSERT | DELETE | UPDATE [OF colname {, colname...}]}
ON tablename [REFERENCING corr_name_def {,
corr_name_def...}]
[FOR EACH ROW | FOR EACH STATEMENT]
[WHEN (search_condition)]
{statement
-- action (single statement)
| BEGIN ATOMIC statement; { statement;...} END}
-- action (multiple statement.)
DROP TRIGGER trigger_name;
Example
CREATE OR REPLACE TRIGGER Log_salary_increase
AFTER UPDATE ON Employee
FOR EACH ROW
WHEN (new.Salary > 100000)
BEGIN
INSERT INTO Emp_log (Emp_id, Log_date, New_salary,
Action)
VALUES (:new.Empno, SYSDATE, :new.salary,
'NEW SAL');
END;
Using BEFORE
• BEFORE command fires trigger before
UPDATE
– Can write to :new value but not to :old value
• If use After, can write to either :old or :new
value
When to use triggers?
• Don’t define triggers if can do the same
with integrity constraints
• Limit triggers to less than 60 lines of code
• Do not create recursive triggers
• Triggers execute every time event occurs
on which trigger is created
• http://docs.oracle.com/cd/B19306_01/appdev.102/b1425
1/adfns_triggers.htm#i1006211