Transcript Lecture 3b

DBS201: More on SQL
Lecture 3
1
Agenda

Review
 Constraints
Primary Key
 Not Null
 Unique
 Check
 Foreign Key


Changing a Table Definition
 Changing and deleting Table Rows
2
Review

What information is stored in a database
structure chart?
 Data Dictionary shows
Column Name
 Column Type
 Column Length
 PK?
 FK Reference?
 Req’d?
 Unique?
 Validation?

3
Review

Terms starting with the largest
 SQL Terms
Native Equivalents






Schema/Collection
Table
Row
Column
View
Index
Library
Physical File
Record
Field
Logical File
Logical File
4
Review
CREATE TABLE painter (
 p_num
char (4) not null with default,
 p_lname
char (15) not null with default,
 p_fname
char (15) not null with default,
 p_city
char (20),
 p_phone
dec (10),
 Constraint
Painter_P_num_PK
 primary key (p_num) )
 Table PAINTER in DR201C40 created but was not
journaled.
5
Review

Is different than
6
Review
CREATE TABLE painter (
 p_num
char (4) not null with default,
 p_lname
char (15) not null with default,
 p_fname
char (15) not null with default,
 p_city
char (20),
 p_phone
dec (10),
 Constraint
Painter_P_num_PK
 primary key (p_num) )
 Table PAINTER created in PAINTRUS
7
Constraints






Constraints are used to ensure that the
database receives “good” data
PRIMARY KEY  unique and not null.
NOT NULL  a value must exist.
UNIQUE  value exists only once in a column.
CHECK  value must pass validation criteria
FOREIGN KEY  used to enforce Relational
Integrity between two tables
8
Constraints – Primary Key

PRIMARY KEY

Provides rapid access to a row on a
table

Guarantees uniqueness of the PK field
(there can be only one Doctor # D5524 in
Ontario)

Insists that a primary key value be
specified by the user for every row on
the table.
9
Constraints – Not Null

NOT NULL

The user must specify a value.

NOT NULL WITH DEFAULT

The user must specify a value or the system will
specify a value.(spaces for char, 0 for numeric
and “sysdate” for date fields)

NOT NULL WITH DEFAULT “value”

The user must specify a value or the system will
insert “value” into the field.
10
Constraints – Unique

UNIQUE

The database will not allow rows to be added or
modified if it results in the unique field occurring
more than once in the column in a table.

This is useful for fields like:

Social Insurance Number
Driver’s License Number
Ontario Health Card Number
Why not make these a primary key to uniquely locate a
table row?
Do you want someone in your company having any of these
unique numbers when they need to update your address?




11
Constraints – Check
CHECK
 The database will examine the value of the
field. There is a condition to be satisfied.
The database will not allow rows to be added or
modified if it results in the field failing the
validation.

Here are some common validations:
 (Age >= 18 and Age <= 49)
 (Salary BETWEEN 20000 and 40000)
 (Grade IN(‘A’,’B’,’C’,’D’,’F’,’I’))

12
Constraints – Check










CREATE TABLE TEST (
AGE
DECIMAL (3,0) NOT NULL,
SALARY DECIMAL (7,2) NOT NULL WITH DEFAULT,
GRADE CHARACTER (1),
CONSTRAINT TEST_AGE_CK
CHECK (AGE >= 18 AND AGE <= 49),
CONSTRAINT TEST_SALARY_CK
CHECK (SALARY BETWEEN 20000 AND 4000),
CONSTRAINT TEST_GRADE_CK
CHECK (GRADE IN('A', 'B', 'C', 'D', 'F', 'I') ) )
13
Constraints – Foreign Key
FOREIGN KEY
The Foreign Key is used to get information from
another table using the Primary Key of the other
table.
A PATIENT record has the DoctorNum as a Foreign Key
field. DoctorNum is the Primary Key of the DOCTOR
table. The DoctorNum is used to get information
about the Doctor from the DOCTOR table.
The Foreign Key is used to enforce relational
integrity between the two tables. We will have more
to say about Relational Integrity later in this
course.
14
Constraints - Examples












CREATE TABLE customer(
c_num
char (5) not null with default,
c_lname
char (15) not null with default,
c_fname
char (15) not null with default,
c_city
char (20),
c_phone
dec (10),
cp_num
char(4),
Constraint Customer_c_num_PK
primary key (c_num),
Constraint Customer_Customer_cp_num_FK
Foreign Key (cp_num)
References paintrus.Painter (p_num ) )
15
Foreign Key Constraint



INSERT INTO PAINTRUS/CUSTOMER
VALUES('99999', 'Smith', 'Ronald',
'Mississauga', 9059998888, '111')
Operation not allowed by referential constraint
CUSTOMER_CUSTOMER_CP




INSERT INTO PAINTRUS/painter VALUES('111',
'Wong', 'Mary','Mississauga', 9054443234)
1 rows inserted in PAINTER in PAINTRUS.
INSERT INTO PAINTRUS/CUSTOMER
VALUES('99999', 'Smith', 'Ronald', 'Mississauga',
9059998888, '111')
1 rows inserted in CUSTOMER in PAINTRUS.
16
Foreign Key Constraint

delete from painter


Delete prevented by referential constraint
CUSTOMER_CUSTOMER_CP_NUM
delete from customer

1 rows deleted from CUSTOMER in PAINTRUS.


delete from painter
1 rows deleted from PAINTER in PAINTRUS
17
Constraints - Examples
LAB
3 has examples
of these 5 types of
constraints.
18
Agenda

Data Definition Language
 How to use SQL to update table
definitions

Data Manipulation Language
 How to update data in a table
 How to delete rows of data in a table
19
Changing a Table Definition

ALTER TABLE


Syntax


Used to update a database definition
ALTER TABLE tablename
Can do any of the following





Add a field
Alter a field
Drop a field
Add a constraint
Drop a constraint
20
Changing a Table Definition

To add a field:
 ALTER TABLE tablename

ADD COLUMN field-name datatype
 Example:


ALTER TABLE MARINA
ADD COLUMN Boat_Description CHAR (20)
21
Changing a Table Definition

To change a field definition:

ALTER TABLE tablename

ALTER COLUMN fieldname

SET DATA TYPE data type

Example:

ALTER TABLE MARINA
ALTER COLUMN Boat_Description
SET DATA TYPE CHAR(12)
NOT NULL WITH DEFAULT 'abc'
22
Changing a Table Definition

To remove a field:

ALTER TABLE tablename


DROP COLUMN column_name
Example:

ALTER TABLE MARINA

DROP COLUMN Boat_Description


*Note – be careful not to drop a column that you may in fact need as
the data will be lost.

Rule of thumb: do not alter a table after it contains data
23
Changing a Table Definition

To add a constraint:

Example defining the PRIMARY KEY:

ALTER TABLE MARINA

ADD CONSTRAINT Marina_Boat_No_PK

PRIMARY KEY (Boat_No)

Example of adding a check constraint:

ALTER TABLE MARINA

ADD CONSTRAINT Marina_Boat_Code_CK

CHECK (Boat_Code BETWEEN 'AAA' AND 'DDD‘)
24
Updating a Table Definition - DDL






To drop a constraint
ALTER TABLE tablename
DROP CONSTRAINT constraint_name
ALTER TABLE MARINA
DROP CONSTRAINT Boat_Name_UN
Name must be the name assigned to the constraint (may have been assigned by
user or by system)
25
Updating Data in a Table

To update data in a table, use UPDATE.

UPDATE tablename
SET column name = new value
WHERE condition
examples
UPDATE MARINA
SET Dock_Number = ‘AAA'
UPDATE MARINA
SET Dock_Number = ‘AAB‘
WHERE Marina_Num = ‘M-2407’








26
How to Delete Rows in Tables-DML

To delete a row in a table, Use the Delete
statement

DELETE FROM tablename
WHERE condition
example
DELETE FROM MARINA
WHERE Dock_Num = ‘CCC’
27
Updating a Table Definition - DDL

Adding a Primayr Key and Foreign Key Constraint



ALTER TABLE CUSTOMER

ADD CONSTRAINT CUSTOMER_CUSTOMER_NUMBER_PK

PRIMARY KEY (CUSTOMER_NUMBER)
ALTER TABLE CUSTOMER

ADD CONSTRAINT CUSTOMER_SALES_REP_NUMBER_FK

FOREIGN KEY (SALES_REP_NUMBER)

REFERENCES SALESREP(SALES_REP_NUMBER)
Table SALESREP in PRMIERC40 does not have a matching
parent key
28