CODEWITHSUNDEEP
sqlpostgresql
Alex Goft
Alex Goft

Reputation: 1124

Understanding constraint violations in SQL

I am new to SQL and I'm a bit struggling -

Consider the next snippet of code:

CREATE TABLE Trip
(
    tnum INTEGER PRIMARY KEY,   
    location TEXT NOT NULL, 
    duration INTEGER DEFAULT(5),
    difficulty INTEGER CHECK(difficulty > 0 AND difficulty < 7),
    CHECK (difficulty <= 6 AND difficulty >= 3)
);

CREATE TABLE Company
(
    cid INTEGER PRIMARY KEY,
    diffiname TEXT NOT NULL, 
    estYear Integer NOT NULL
);

CREATE TABLE Offer
(
    cid INTEGER,
    tnum INTEGER,
    FOREIGN KEY(cid) REFERENCES Company(cid),
    FOREIGN KEY(tnum) REFERENCES Trip(tnum)
);

I'm experiencing two constrains violations, the first one is that i'm able to add Trips with difficulty 0, or trips with difficulty 7 with duration 2 (Desired constraint - Trips of duration more than 6 have difficulty of at least 3) -

enter image description here

And the second violation, is that i'm able to add Offers that consist of tnum and cid that don't even exist.

Upvotes: 0

Views: 58

Answers (2)

James K. Lowden
James K. Lowden

Reputation: 7837

If we rephrase your constraint as the logically equivalent, 

difficulty INTEGER not NULL
    CHECK(difficulty between 1 and 6), 
    CHECK(difficulty between 3 and 6)

it becomes clear that the second constraint controls, because anything between 3 & 6 is also between 1 & 6.

i'm able to add Trips with difficulty 0

That wouldn't be possible with the above. Using and and or, though, it's easy to get tangled up and describe a constraint other than what you intend.

Regarding the foreign key constraints, the SQL as it now stands looks correct, but @Gordon Linoff's answer indicates that the original version of your post included errors that would not have correctly constrained Offer.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think your foreign key reference to trip is wrong on offer:

CREATE TABLE Offer
(
    cid INTEGER,
    tnum INTEGER,
    FOREIGN KEY(cid) REFERENCES Company(cid),
    FOREIGN KEY(tnum) REFERENCES Trip(cid)
--------------------------------------^ tnum
);

As for the check constraints, I don't understand the intent of this:

CHECK (difficulty <= 6 OR duration >= 3)

Do you intend AND? Do you intend AND difficulty >= 3 instead of duration?

Upvotes: 1

Related Questions