Question 205650 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL CHECK constraint confusion

Hello guys I had this question on my SQL exam and I do not know what the answer should be. Can you help me?

The table Employees, among others, contains a column called "Country" with a length of 3 characters and a column called "State" with a maximum length of 30 characters. If the value ‘USA’ is present in the "Country" column, then the "State" column should only contain two characters. For other values in the "Country" column, the "State" column can contain 0, 1, 2, or a maximum of 30 characters. Which of the following check constraints will ensure that the entered or modified data comply with the given requirements?

A)
Country = ‘USA’ AND LEN(State) =2

B)
Country = ‘USA’ OR LEN(State) =2

C)
Country <> ‘USA’ AND LEN(State) = 2

D)
Country <> ‘USA’ OR LEN(State) = 2

My teacher is saying that the right answer is D), but I just can’t wrap my head around that answer and How it would make sense. I thought that A) is the answer since both ‘USA’ and length should return true in order to be inserted. I think that D) answer would allow inserting values like ‘USA’ and ‘FILA’ since or will return true if one of ether is true.
Thanks in advance!

Tags:

2

Answers


  1. 0

    D would be the right answer, if a country is not equal to USA, the check constraint will not block, if USA is the country will not block only when the the state has only 2 letters, else it reports an error see sample

    CREATE TABLE employee (State varchar(3), Country Varchar(30)
  ,CONSTRAINT `state_county_chk` CHECK (Country <> 'USA' OR CHAR_LENGTH(State) = 2))

    INSERT into employee VALUES ('mic','USA')

    Check constraint 'state_county_chk' is violated.

    INSERT into employee VALUES ('mic','GERMANY')

    INSERT into employee VALUES ('MI','USA')

    SELECT * FROM employee
    State Country
    mic GERMANY
    MI USA

    fiddle

    Login or Signup to reply.
  2. 0

    As a preface, in SQL, and in computer-programming in general, OR is a logical disjunction – whereas in spoken English language "or" is generally used as a logical conjunction (i.e. "and" or "both").

    When looking at an expression involving AND or OR, remember that it means you need to evaluate each operand (left-side and right-side) of the OR expression independently, and the entire expression is either TRUE or FALSE (or UNKNOWN if you have SQL NULLs) according to this table (NULL/UNKNOWN omitted for brevity):

    Combinator Left-hand-side Right-hand-side Result
    OR TRUE TRUE TRUE
    OR TRUE FALSE TRUE
    OR FALSE TRUE TRUE
    OR FALSE FALSE FALSE
    AND TRUE TRUE TRUE
    AND TRUE FALSE FALSE
    AND FALSE TRUE FALSE
    AND FALSE FALSE FALSE

    To save time, I’ll just post some example data for each possible interpretation:

    A: Country = 'USA' AND LEN(State) = 2

    Country State Expected Result Actual Result Reason
    ‘USA’ ‘WA’ OK OK 'USA' = 'USA' – and also LEN('WA') = 2
    ‘USA’ ‘Foobar’ Fail Fail LEN('Foobar') = 6 not 2
    ‘Canada’ ‘NB’ OK Fail 'Canada' <> 'USA'
    ‘Canada’ ‘Foobar’ OK Fail 'Canada' <> 'USA' – and also: LEN('Foobar') = 6 not 2

    B: Country = 'USA' OR LEN(State) = 2

    Country State Expected Result Actual Result Reason
    ‘USA’ ‘WA’ OK OK 'USA' = 'USA' – and also LEN('WA') = 2
    ‘USA’ ‘Foobar’ Fail OK 'USA' = 'USA' – the LEN term is ignored
    ‘Canada’ ‘NB’ OK OK LEN('NB') = 2 – the 'Canada' <> 'USA' term is ignored)
    ‘Canada’ ‘Foobar’ OK Fail 'Canada' <> 'USA' – and also LEN('Foobar') = 6 not 2

    C: Country <> 'USA' AND LEN(State) = 2

    Country State Expected Result Actual Result Reason
    ‘USA’ ‘WA’ OK Fail 'USA' <> 'USA' is false – even though the LEN(State) part of the check passes using AND means both sides of the AND must succeed for a pass.
    ‘USA’ ‘Foobar’ Fail Fail 'USA' <> 'USA' is false – and LEN('Foobar') = 6 not 2 – so both sides of the AND fail.
    ‘Canada’ ‘NB’ OK OK 'Canada' <> 'USA' and also LEN('NB') = 2
    ‘Canada’ ‘Foobar’ OK Fail 'Canada' <> 'USA', but LEN('NB') = 6

    D: Country <> 'USA' OR LEN(State) = 2

    Country State Expected Result Actual Result Reason
    ‘USA’ ‘WA’ OK OK 'USA' <> 'USA' is false, but as this is an OR condition, the LEN('WA') = 2 part passes.
    ‘USA’ ‘Foobar’ Fail Fail 'USA' <> 'USA' is false, so this can still pass if LEN(State) = 2, but in this case it’s LEN('Foobar') = 6 so it fails
    ‘Canada’ ‘NB’ OK OK 'Canada' <> 'USA' is true, we can ignore the LEN(State) clause, but even if we did, that would also pass as LEN('NB') = 2.
    ‘Canada’ ‘Foobar’ OK OK 'Canada' <> 'USA' is true, we can ignore the LEN(State) clause
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search