Scribd
Upload
3 views9 pages

Course 1 Module 03 Lesson 4

This document covers the integrity constraint syntax in the relational data model, focusing on primary key (PK), foreign key (FK), unique, required (NOT NULL), and check constraints. It provides examples of CREATE TABLE statements demonstrating the placement of these constraints both inline and externally. The document emphasizes the importance of using constraint names and outlines limitations of CHECK constraints.

Uploaded by

tekulapallyyashwanth
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views9 pages

Course 1 Module 03 Lesson 4

This document covers the integrity constraint syntax in the relational data model, focusing on primary key (PK), foreign key (FK), unique, required (NOT NULL), and check constraints. It provides examples of CREATE TABLE statements demonstrating the placement of these constraints both inline and externally. The document emphasizes the importance of using constraint names and outlines limitations of CHECK constraints.

Uploaded by

tekulapallyyashwanth
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Information Systems Program

Module 3
Relational Data Model and
CREATE TABLE Statement

Lesson 4: Integrity Constraint Syntax


Lesson Objectives
• Read and write CREATE TABLE statements with
PK constraints
• Read and write CREATE TABLE statements with
FK constraints
• Read and write CREATE TABLE statements with
simple CHECK constraints

Information Systems Program


Constraint Overview

• Primary key
• Foreign key
Subject • Unique
• Required (NOT NULL)
• Check

• Inline
Placement • External

Information Systems Program


Constraint Syntax Examples

• CONSTRAINT PKCourse PRIMARY KEY(CourseNo)


• CONSTRAINT PKEnrollment PRIMARY KEY
(OfferNo, StdNo)
• CONSTRAINT UniqueCrsDesc UNIQUE (CrsDesc)
• CONSTRAINT FKOfferNo FOREIGN KEY (OfferNo)
REFERENCES Offering
• CONSTRAINT OffCourseNoReq NOT NULL

Information Systems Program


External PK Constraint Placement

CREATE TABLE Course


( CourseNo CHAR(6),
CrsDesc VARCHAR(250),
CrsUnits SMALLINT,
CONSTRAINT PKCourse PRIMARY KEY(CourseNo),

CONSTRAINT UniqueCrsDesc UNIQUE (CrsDesc) )

Information Systems Program


External FK Constraint Placement

CREATE TABLE Enrollment


( OfferNo INTEGER,
StdNo CHAR(11),
EnrGrade DECIMAL(3,2),
CONSTRAINT PKEnrollment PRIMARY KEY
(OfferNo, StdNo),
CONSTRAINT FKOfferNo FOREIGN KEY (OfferNo)
REFERENCES Offering,
CONSTRAINT FKStdNo FOREIGN KEY (StdNo)
REFERENCES Student );

Information Systems Program


Inline Constraint Placement
CREATE TABLE Offering
( OfferNo INTEGER,
CourseNo CHAR(6) CONSTRAINT OffCourseNoReq NOT NULL,
OffLocation VARCHAR(50),
OffDays CHAR(6),
OffTerm CHAR(6) CONSTRAINT OffTermReq NOT NULL,
OffYear INTEGER CONSTRAINT OffYearReq NOT NULL,
FacNo CHAR(11),
OffTime DATE,
CONSTRAINT PKOffering PRIMARY KEY (OfferNo),
CONSTRAINT FKCourseNo FOREIGN KEY (CourseNo)
REFERENCES Course,
CONSTRAINT FKFacNo FOREIGN KEY (FacNo)
REFERENCES Faculty );

Information Systems Program


Check Constraint Examples
• Syntax: CHECK ( <row-condition> )
• Row conditions with columns from the same table
CONSTRAINT ValidGPA CHECK ( StdGPA BETWEEN 0 AND 4 )

CONSTRAINT ValidStdClass
CHECK ( StdClass IN ('FR','SO', 'JR', 'SR' )

CONSTRAINT OffYearValid CHECK ( OffYear > 1970 )

CONSTRAINT EnrollDropValid
CHECK ( EnrollDate < DropDate )

Information Systems Program


Summary
• Importance of PK and FK constraints
• Use constraint names
• CHECK constraint limitations

Information Systems Program

You might also like