Add a tuple-based check to this relation

Question

In relation Person (SSN, State, Name), add a tuple-based check: if a person’s State is NY, then the first three digits of his SSN has to be between ‘050’ and ‘134’. (Hint: use LEFT function in SQL).

CREATE TABLE Person (
    SSN   INT PRIMARY KEY,
    State   CHAR(50),
    Name  CHAR(100),
    CHECK ( 
        SELECT LEFT (SSN, 3) AS FirstThreeDigits, SSN FROM Person,
        WHERE Person.State == ‘NY’) between '050' and between '134'
);

I am not comfortable with the CHECK condition here. I am not sure if this is the right way doing conditional check. Can someone please verify this? If not, how do we do a conditional check?

We need this:

if state == 'NY', perform CHECK

Do we need a trigger? I was thinking that if a new insert / update occurs, check the value. But the question doesn't ask that.

@Conrad Frix It's just SQL statement. Non-platform specific. It's an introductory course. Thanks. So I think any general SQL statements will be valid. — CppLearner, May 05 '12 at 03:39

score 1 · Accepted Answer · edited Feb 28 '16 at 10:37

1

Ok so its generic but DDL typically isn't but lets use the doc on SQL Server CHECK Constraints as a guide anyway

You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators. For the previous example, the logical expression is:
salary >= 15000 AND salary <= 100000.

Note that the above example from the docs is not a SELECT statement but and expression. This means all you need to do is come up with a expression that returns TRUE

when all of the following are true

State is NY
The Left three digits of the SSN is => ‘050’
The Left three digits of the SSN is <= 134.

Or

Sate is not NY

Remembering that you can Group a set of logical expressions in a Parens

e.g.

(Group of logical expressions) OR (Group of logical expressions)

It shouldn't be too hard

edited Feb 28 '16 at 10:37

Sevle

3,109
2
19
31

answered May 05 '12 at 03:45

Conrad Frix

51,984
12
96
155

Thanks for the hint. I am struggle with the fact that the check is performed ONLY when state is == NY, then we do the check on the digits. How do I do that? If all three are fasle, the check will be false, and reject the modification. That's not what the problem want, I think. I think I can use `between` to check the digits? – CppLearner May 05 '12 at 03:49
Ahhh. I tend to complicated things. Thanks Conrad! You are right. :( Thanks. You've given a big hint before. – CppLearner May 05 '12 at 03:56

Add a tuple-based check to this relation

1 Answers1