Transcript Data Definition Language

Creating data tables and Referential Integrity
• Objective
– To learn about the data constraints supported by SQL2
– To be able to relate tables together in such a fashion that the meaning
of the information is maintained despite attempts to change a subset of
the data
• Contents
–
–
–
–
–
–
–
–
–
Supported Data Types
Column Attributes
Domains
Table definition
– Practical 6-1
Referential Integrity further clarified
Further ANSI Column Attributes
Referential Integrity Rules
CREATE TABLE using DRI
Other methods to safeguard data
– Practical 6-2
CDT/1
SQL92 Standard Data Types
DATE ‘1995-10-23’
TIME ‘17:45:45.75’
DATE
TIME
TIMESTAMP
1234
-98321
INTEGER
SMALLINT
123.4567
‘Fred Smith’
‘Blue Widgets’
CHARACTER (Char)
CHARACTER VARYING
(VarChar)
2.4319E38
0.234E-35
110101
NUMERIC (precision, scale)
DECIMAL (precision, scale)
FLOAT (precision)
BIT
BIT VARYING
CDT/2
ANSI Column Attributes - 1
• NOT NULL (Mandatory column)
• NULL (Nullable/Optional column)
• DEFAULT { Literal | Scalar System function }
– DEFAULT ‘Sales’
– DEFAULT CURRENT_DATE
CDT/3
ANSI Column Attributes - 2
• UNIQUE
– used for non-key column values
– e.g. dept_name, driver_licence_no
– NOT used for Primary Key column
• CHECK
– specifies a range check for this column (similar to a WHERE
clause)
– must be resolvable by examining just the row being modified
CDT/4
CREATE TABLE
CREATE TABLE contact
(
company_no
contact_code
name
job_title
tel
notes
)
Integer
Char(3)
VarChar(20)
NOT NULL,
NOT NULL,
NULL,
VarChar(25)
VarChar(20)
VarChar(60)
NULL,
NULL,
NULL
• Note, 6 columns, but 5 commas! – it’s a comma separated list in
parentheses, often seen in SQL
• To remove the table, and all its columns & constraints:
DROP TABLE contact
CDT/5
ALTER TABLE
• Adding, modifying and deleting columns
ALTER TABLE dept
ADD location char(30)
NULL
ALTER TABLE sale
ALTER order_value
DROP DEFAULT
• Features rarely supported
– Deleting columns from existing tables
ALTER TABLE
DROP
salesperson
sales_target
– Adding NOT NULL columns to existing table
– Changing a column’s length or nullability
CDT/6
Ch8Practical1 - Basic DDL
• Create and Alter a Table corresponding to the description
supplied in the practical file
CDT/7
What is Referential Integrity?
•The Referential Integrity question
“What should happen if sales exist for company 2 and someone tries to
delete company 2 from the company table?”
– Should it be disallowed?
– Should the system delete the sales as well?
– Should it keep the sales but change the company_no to NULL?
company
Name
Advanced System Ltd
Corporate Trading Ltd
Draxstone Financing Ltd
Lloyds Bank Plc
National car components
Charles Systems Ltd
Ossis Water Supplies
Saturn satellite stations
Wilkinson Blades Ltd
Telephones Unlimited
Address
27 Heath Drive
12 The Walk
22 Bower Court
21-25 Lombard Street
National House
223 High Street
Waterfall House
Celestial House
23 Warrick Road
Communication House
sale
No
1
2
3
4
5
6
7
8
9
10
?
Sale
1
2
3
4
5
6
7
8
9
10
11
Company No
7
2?
2?
4
9
6
10
2?
8
9
6
Product
IBM Thinkpad 755CE
MS Office Professional
ScanPRO 4800 Scanner
Modems and Cables
Laser printer
Complete Desktop Publishing System
Lotus 1-2-3 spreadsheet package
Colour Injet Printer
Psion Pocket Computer
Acer Aspire + laser printer
Virtual Reality Helmet
CDT/8
Key definition
• PRIMARY KEY (CREATE or ALTER TABLE)
[CONSTRAINT constraintname] PRIMARY KEY (column1,column2,..)
• FOREIGN KEY (CREATE or ALTER TABLE)
[CONSTRAINT constraintname] FOREIGN KEY (column1,column2,..)
REFERENCES tablename[(column1,column2,..)
[ON DELETE|UPDATE CASCADE|RESTRICT|SET NULL|SET DEFAULT]
CDT/9
Referential Integrity Rules
• Foreign key column values added to or updated in a dependent table
are checked against the parent table’s Primary Key
• Rows to be deleted from the parent table are checked for usage in the
dependent table. SQL2 supports 4 behaviour options:
RESTRICT, CASCADE, SET DEFAULT and SET NULL
• Many DBMS’s do not support all options e.g. MS SQL Server only
offers RESTRICT
CDT/10
CREATE TABLE using DRI
CREATE TABLE contact
(
company_no
contact_code
name
job_title
INTEGER
CHAR(3)
VARCHAR(20)
VARCHAR(25)
NOT NULL,
NOT NULL,
NULL,
NULL,
tel
notes
VARCHAR(20)
VARCHAR(60)
NULL,
NULL,
PRIMARY KEY (company_no, contact_code),
FOREIGN KEY (company_no)
REFERENCES company
ON DELETE CASCADE
ON UPDATE RESTRICT
-- not in SQL Server
-- not in SQL Server
)
CDT/11
Other methods to safeguard data
ID Dept Salary
100 234 20000
101 124 30000
102 234 20000
212 100 5000
213 124 25000
214 124 21000
282 100 8000
Changes
If changes occur,
execute this SQL
CDT/12
Ch8Practical2 - Referential Integrity
• Follow the instructions in this tutorial to define primary keys,
foreign keys and to observe resultant behaviour.
CDT/13
SUMMARY
• Tables
– Used to store data one row per entity occurrence
– Each row is divided into column definitions which together describe the
entity occurrence
– Column definitions
– Control the type of data that is inserted into a given column/ row
intersection
• Column attributes
– Enable us to define valid entries into a column
– Nulls, Defaults, Checks, Uniqueness etc
• Tables can be Altered, after populating them with data
• Referential Integrity is important
– It ensures data is updated in multiple locations when necessary or that
updates are prevented, depending on business requirements
– SQL92 syntax allows for the definition of DRI via Primary and Foreign
Key constraints, but DRI is not fully supported
CDT/14