Question

SQL CHECK CONSTRAINT AND TEST CASE...

SQL Query

Here are the tables:

CREATE TABLE Movies(

    movieID INT,

    name VARCHAR(30) NOT NULL,

    year INT,

    rating CHAR(1),

    length INT,

    totalEarned NUMERIC(7,2),

    PRIMARY KEY(movieID),

    UNIQUE(name, year)

);

CREATE TABLE Showings(

    theaterID INT,

    showingDate DATE,

    startTime TIME,

    movieID INT,

    priceCode CHAR(1),

    PRIMARY KEY(theaterID, showingDate, startTime),

    FOREIGN KEY(theaterID) REFERENCES Theaters,

    FOREIGN KEY(movieID) REFERENCES Movies

);

CREATE TABLE Tickets(

    theaterID INT,

    seatNum INT,

    showingDate DATE,

    startTime TIME,

    customerID INT,

    ticketPrice NUMERIC(4,2),

    PRIMARY KEY(theaterID, seatNum, showingDate, startTime)

);

the tasks are to write a check constraint as such:

3. In Showings, if movieID is not NULL, then priceCode must also not be NULL.

AND then i need to write an update statement that makes sure it fails per the constraint.

i've written:

ALTER TABLE Showings ADD CHECK ((movieID <> NULL) OR (priceCode <> NULL)); for the constraint

and

UPDATE Showings

SET priceCode = 1

WHERE movieID is not NULL; for the failing test.

i believe this is right, but the update statement is not failing like it is supposed to.

can someone see if i'm doing anything wrong or please explain how to correctly write it?

Answer 1

Since, this is a column dependency constraint, it will be done by dealing cases:

This means, if movieID is not NULL, then priceCode is not NULL. But if movieID is NULL, then priceCode can either be NULL or not NULL.

So the constraint goes like this,

CONSTRAINT CHK_Showings CHECK(((movieID is NULL) and ((priceCode is not NULL) or(priceCode is NULL))) or ((movieID is not null) and (priceCode is not null)))

**either use alter table for adding this constraint, or rebuild this table using this constraint**

And also, the test case used is incorrect, as it goes in favor of what we have done. We have to check that if movieID is not NULL then price code should also be NOT NULL. The failing test case would be the following, which tries updating priceCode to NULL, in records where movieID is not NULL, and then shows error.

UPDATE Showings
SET priceCode = NULL
WHERE movieID is not NULL;

Error Message: