Transcript Introduction to Databases - University of Saskatchewan

Data Types & Constraints
•Week 3, Day 2
•(based on Ch 6 of Connolly and Begg)
CMPT 355 Sept-Dec 2010 - w3d2
1
Data Types & Constraints - Overview
Today sets the stage for creating SQL data definitions.
• It deals with:
– SQL Identifiers
– SQL Data Types
– SQL Integrity Constraints
CMPT 355 Sept-Dec 2010 - w3d2
2
SQL Identifiers
• From the text (p158)
– The ISO standard provides a default character set, which consists of
•
•
•
•
the upper-case letters A … Z,
the lower-case letters a … z,
the digits 0 …9, and the underscore character.
It is also possible to specify an alternative character set.
– The following restrictions are imposed on identifiers:
• An identifier can be no longer than 128 characters
– (most dialects have a much lower limit than this);
• An identifier must start with a letter;
• An identifier cannot contain spaces.
• From ISO/IEC 9075-2 it appears that identifiers are case insensitive.
CMPT 355 Sept-Dec 2010 - w3d2
3
SQL Data Types
“4.4.1 General Data Type Information
• “Every data value belongs to some data type.
• “Every data type is either predefined or constructed or
user-defined.
– “The name of a predefined or constructed data type is a reserved
word specified by that part of ISO/IEC 9075 that specifies the
data type.
– “The name of a user-defined data type is provided in its
definition.”1
1 ISO/IEC 9075-2 Database Languages – SQL - Foundation
CMPT 355 Sept-Dec 2010 - w3d2
4
Predefined Data Types
“4.4.1 General Data Type Information
• “A predefined data type is a data type specified by ISO/IEC 9075
and is therefore provided by the SQL-implementation.
• “A data type is predefined even though the user is required (or
allowed) to provide certain parameters when specifying it (for
example the precision of the number.
• “A predefined data type is atomic.
– “An atomic type is a data type whose values are not composed of
values of other data types.
– “The existence of an operation (SUBSTRING, EXTRACT) that is
capable of selecting part of a string of datetime value does not imply
that a string or datetime is not atomic.”1
•
The text (p.159-163) identifies major SQL predefined data types.
1 ISO/IEC 9075-2 Database Languages – SQL - Foundation
CMPT 355 Sept-Dec 2010 - w3d2
5
Other SQL Data Types
• Various types of constructed and user-defined data types
are dealt with by the text in section 28.4 SQL3
CMPT 355 Sept-Dec 2010 - w3d2
6
PostgreSQL Data Types
• PostgreSQL has a slightly different set of data types
– from those identified in the text
– (which are those from the official SQL specification)
– that better serve the philosophy of PostgreSQL
– information on them can be found in the recommended online
documentation on PostgreSQL
• http://www.postgresql.org/docs/7.4/interactive/datatype.html
CMPT 355 Sept-Dec 2010 - w3d2
7
SQL Integrity Constraints
•
“Integrity constraints, generally referred to simply as constraints, define valid
states of SQL-data by constraining the values in base tables.”1
•
Constraints are defined in the CREATE and ALTER TABLE commands we
will see later this week.
•
When specified, a constraint causes the DBMS to reject any attempt to
• INSERT a record that violates the constraint
• UPDATE a record that violates the constraint
• DELETE a record that violates the constraint
•
Chapter 6.2 of the text introduces the following types of constraints:
–
–
–
–
–
Required data
Domain constraints
Entity integrity
Referential integrity
Enterprise constraints
1 ISO/IEC 9075-2 Database Languages – SQL - Foundation
CMPT 355 Sept-Dec 2010 - w3d2
8
SQL Integrity Constraints
Required data – NOT NULL (p.164)
– Can be applied to some or all attributes in a table
– If not specified for an attribute, the DBMS will accept records
having nulls in that attribute.
– When specified, NOT NULL causes the DBMS to reject any
attempt to
• INSERT a record with a null in that attribute
• UPDATE a record to have a null in that attribute
CMPT 355 Sept-Dec 2010 - w3d2
9
SQL Integrity Constraints
Domain constraints (p.163-164)
– A domain is a set of legal values.
• Each attribute has a domain.
• Where not otherwise specified,
– the domain of an attribute is the set of all legal values of the attribute
– based on the data type of the attribute
– Can be done directly for a single attribute using CHECK
(condition)
– Can be named and created to be used repeatedly
• “A domain constraint is a constraint that is specified for a domain. It
is applied to all columns that are based on that domain, and to all
values that are cast to that domain.”1
1 ISO/IEC 9075-2 Database Languages – SQL - Foundation
CMPT 355 Sept-Dec 2010 - w3d2
10
SQL Integrity Constraints
Domain constraints (cont.)
– General format
for creating a named domain constraint:
• CREATE DOMAIN domainName [AS] dataType
• [DEFAULT default value]
• [CHECK condition]
– The condition can be any valid condition.
• NOTE:
– The condition is only checked upon an INSERT or an UPDATE of the record with
the constraint. It is not checked when records referred to in the CHECK of the
domain are updated or deleted.
– The domainName can (& should) be used
• in place of a dataType by one or more data definitions.
CMPT 355 Sept-Dec 2010 - w3d2
11
A NOTE about style
• For purposes of this class
– ALL attributes must be based on constrained
data types as defined by a DOMAIN
• We will use the defined DOMAIN as the data type
of ATTRIBUTES when we define TABLES
– This is important, since
• This helps us to recognize whether we are dealing
with the same type of ATTRIBUTE or not
• A list of DOMAINS provides us with an idea about
what types of data we have in out database
CMPT 355 Sept-Dec 2010 - w3d2
12
SQL Integrity Constraints
Entity integrity (p.166)
– If you don’t demand it with entity integrity constraints
• then SQL does not ensure uniqueness.
– Records will be unique
if they have a primary key which can be defined as:
• PRIMARY KEY (attribute1, attribute2, …)
– This should be done once for each table.
– Records can also insist that other values (e.g alternate keys) be unique:
• UNIQUE (attribute1, attribute2, …), (attribute 3)
– Zero, one, or multiple alternate keys can be defined for a table.
– NOTE:
all attributes that are used in a PRIMARY KEY or in a UNIQUE
must also be defined as NOT NULL
• PRIMARY KEY and UNIQUE apply to TABLES
– NOT NULL applies to individual ATTRIBUTES
CMPT 355 Sept-Dec 2010 - w3d2
13
SQL Integrity Constraints
Referential integrity (p. 166-167)
– Links a set of attributes of this record to a record key in another table.
– If a foreign key contains a value
• which must currently exist
– as the key to a valid record in the foreign table
– The general format for foreign keys is:
•
•
•
•
•
FOREIGN KEY (attribute1, attribute2, …)
REFERENCES tableName
[MATCH {PARTIAL/FULL}]
[ON DELETE action]
[ON UPDATE action]
• Where
– MATCH (see p. 170 for details)
– ON DELETE and ON UPDATE are optional
– action is one of: CASCADE, SET NULL,SET DEFAULT, NO ACTION
CMPT 355 Sept-Dec 2010 - w3d2
14
SQL Integrity Constraints
Referential integrity (cont.)
– CASCADE
• Will delete the constrained row if the referenced row in the foreign table is
deleted.
• Will update the foreign key value if the referenced value is updated.
– SET NULL
• Will set each of the attributes in the foreign key to null if the referenced value
is changed or its record is deleted, provided that the foreign key attributes
were not defined as NOT NULL.
– SET DEFAULT
• Will set each of the attributes in the foreign key to their defaults if the
referenced value is changed or its record is deleted, provided that the
foreign key attributes were defined with defaults.
– NO ACTION (the default if other not specified)
• Will not allow the updating or deleting of the referenced value.
CMPT 355 Sept-Dec 2010 - w3d2
15
SQL Integrity Constraints
Enterprise constraints - ASSERTIONS (p. 167-168)
– “An assertion is a named constraint that may relate to the content of
individual rows of a table, to the entire contents of a table, or to a state
required to exist among a number of tables.
– “An assertion is satisfied if and only if the specified <search condition>
is not false.”1
– The general format for assertions is:
• CREATE ASSERTION assertionName
• CHECK condition
– An assertion is an integrity constraint that is not directly linked to a
table.
– An assertion is similar to a CHECK, but it involves multiple tables.
– An assertion differs from a DOMAIN in that it is not a data type.
– An assertion can be automatically checked every time that an INPUT,
UPDATE, or DELETE might violate it.
1 ISO/IEC 9075-2 Database Languages – SQL - Foundation
CMPT 355 Sept-Dec 2010 - w3d2
16
SQL Integrity Constraints
Enterprise constraints - ASSERTIONS
• NOTE: ASSERTIONS (as described in the text)
– are not supported by PostgreSQL
– you will be shown an alternate way to achieve the effects of an
ASSERTION in the tutorials when it is needed
CMPT 355 Sept-Dec 2010 - w3d2
17
SQL Integrity Constraints
Effects of Different Integrity Constraints
Constraint
Effects
NOT NULL
any attribute
DOMAIN
any attribute
PRIMARY KEY
one set of attributes per table
UNIQUE
multiple sets of attributes per table
FOREIGN KEY
multiples of: one set of attributes and another table
ASSERTION
across tables
CMPT 355 Sept-Dec 2010 - w3d2
18
SQL Integrity Constraints
Application
– What are some circumstances where you would need/use:
• Required data (NOT NULL)
• Domain constraints (DOMAIN)
• Entity integrity (PRIMARY KEY, UNIQUE)
• Referential integrity (FOREIGN KEY)
• Enterprise constraints (ASSERTION)
CMPT 355 Sept-Dec 2010 - w3d2
19