SQL Constraints and Triggers, Lecture notes of Business

Download SQL Constraints and Triggers and more Lecture notes Business in PDF only on Docsity!

1

SQL Constraints and Triggers

SQL Constraints

• Constraints
  • Primary Key (covered)
  • Foreign Key (covered)
  • General table constraints
  • Domain constraints
  • Assertions
• Triggers

Foreign Key Constraints

• Represents a relationship between two tables
• If a table R contains a foreign key on attributes

{a} that references table S :

• {a} generally correspond to the primary key of S
  • Must have the same number of attributes, and
  • The same domains
• Any value for {a} in R must also exist in S except that - If { a } is not part of the primary key of R it may be null
• There may be values for {a} in S that are not in R

Foreign Key Specification

• Foreign keys specify the actions to be taken if

referenced records are updated or deleted

• For example, create a foreign key in Account that references Branch - Assign accounts of a deleted branch to the Fairfax branch - Cascade any change in branch names

Referencing non-Primary Keys

• By default SQL foreign keys reference the

primary key (of the referenced table)

• It is possible to reference a list of (non-

primary-key) attributes

• The list must be specified after the name of the referenced table
• The specified list of attributes must be declared as a candidate key of the referenced table

General Constraints

• A general or table constraint is a constraint

over a single table

• Included in a table's CREATE TABLE statement
• Table constraints may refer to other tables
• Defined with the CHECK keyword followed

by a description of the constraint

• The constraint description is a Boolean expression, evaluating to true or false
• If the condition evaluates to false the update is rejected

Creating Constraints

• Approach 2: After CREATING TABLE, use ALTER TABLE 10 CREATE TABLE Students (sid CHAR(20), name CHAR(20), login CHAR(10), age INTEGER, gpa REAL); ALTER TABLE Students ADD CONSTRAINT check_gpa CHECK(gpa > 0 AND gpa <= 4.0); To specify a set of allowed values, do something like this (using either approach): … CHECK(gender=‘M’ OR gender=‘F’)

Complex Constraint Example

• Check that a customer's age is greater than 18,

and that a customer is not an employee

CREATE TABLE Customer (SSN CHAR(11), age INTEGER, income REAL, PRIMARY KEY (SSN), CONSTRAINT CustAge CHECK (age > 18), CONSTRAINT notEmp CHECK (SSN NOT IN (SELECT empSSN FROM Employee))); However, nested subquery not allowed for check constraint in Oracle (or most DBMSs). Alternative: Trigger (later) You can give the constraint a name

Domain Constraint Example

• Create a domain for minors, who have ages

between 0 and 18

• Make the default age 10 CREATE DOMAIN minorAge INTEGER DEFAULT 10 CHECK (VALUE > 0 AND VALUE <= 18)

Using Domain Constraints

• A domain can be used instead of one of the base types in a CREATE TABLE statement - Comparisons between two domains are made in terms of the underlying base types - e.g. comparing an age with an account number domain simply compares two integers
• The SQL:1999 standard introduced syntax for distinct types - Types are distinct so that values of different types cannot be compared
• Not supported by Oracle
  • Create a table that holds the domain values instead, and reference this table

Create Type Example

• Suppose you have address attributes (street,

city, state, zip) for customers. You can

create a type for Address:

• Once defined, you can use the new type to

create tables

16 CREATE OR REPLACE TYPE full_address_type AS OBJECT ( Street VARCHAR2(80), City VARCHAR2(80), State CHAR(2), Zip VARCHAR2(10) ); )

Create Type Example

17 CREATE OR REPLACE TYPE full_address_type AS OBJECT ( Street VARCHAR2(80), City VARCHAR2(80), State CHAR(2), Zip VARCHAR2(10) ); ); CREATE TABLE Customer ( full_name full_name_type, full_address full_address_type ); INSERT INTO Customer VALUES ( full_name(‘John’, ‘Smith’), full_address(‘4400 University Dr’, ‘Fairfax’, ‘VA’, ‘22030’) );

Deferring Constraint Checking

• To get around this, create tables without

foreign key constraints, then alter table:

19 CREATE TABLE chicken(cID INT PRIMARY KEY," eID INT);" CREATE TABLE egg(eID INT PRIMARY KEY," cID INT);" " ALTER TABLE chicken ADD CONSTRAINT chickenREFegg" FOREIGN KEY (eID) REFERENCES egg(eID)" INITIALLY DEFERRED DEFERRABLE;" " ALTER TABLE egg ADD CONSTRAINT eggREFchicken" FOREIGN KEY (cID) REFERENCES chicken(cID)" INITIALLY DEFERRED DEFERRABLE;"

Deferring Constraint Checking, Cont’d 20 CREATE TABLE chicken(cID INT PRIMARY KEY," eID INT);" CREATE TABLE egg(eID INT PRIMARY KEY," cID INT);" " ALTER TABLE chicken ADD CONSTRAINT chickenREFegg" FOREIGN KEY (eID) REFERENCES egg(eID)" INITIALLY DEFERRED DEFERRABLE;" " ALTER TABLE egg ADD CONSTRAINT eggREFchicken" FOREIGN KEY (cID) REFERENCES chicken(cID)" INITIALLY DEFERRED DEFERRABLE;" Tells Oracle to do deferred constraint checking until the COMMIT point To insert: INSERT INTO chicken VALUES(1, 2);" INSERT INTO egg VALUES(2, 1);" COMMIT;"