CODEWITHSUNDEEP
sqldatabasecheck-constraints
CppLearner
CppLearner

Reputation: 17040

Add a tuple-based check to this relation

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.

Upvotes: 0

Views: 2024

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52655

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

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

Or

  1. 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

Upvotes: 1

Related Questions