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

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

External PK Constraint Placement:

        CREATE TABLE Course 

        ( CourseNo CHAR(6), 

                CrsDesc VARCHAR(250), 

                CrsUnits SMALLINT, 

                CONSTRAINT PKCourse PRIMARY KEY(CourseNo), 

                CONSTRAINT UniqueCrsDesc UNIQUE (CrsDesc)          )

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     );

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 );

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 )

Our Official Website : Web Conquerors (https://www.webconquerors.com/) 

Want to get digital services? Contact US

Want to know about our services? Our Services