Alex Goft Alex Goft - 7 days ago 6
SQL Question

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
Offer
s that consist of
tnum
and
cid
that don't even exist.

Answer

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.

Comments