SQL constraints Check Answers

From Juneday education
Jump to: navigation, search

Check 1

Create a table user with the following columns:

  • user_id (INTEGER and PRIMARY KEY)
  • email (TEXT and a GLOB constraint for a plausible email address, and also only unique values)
  • user_name (should be unique and not null)

The email GLOB constraint should check that the emails inserted

  • starts with a lower case letter or a number before the @
  • has at least one @ (if we could check that it only had exactly one @ that'd be great but we can't using globbing with one single expression)
  • starts with a lower case letter or a number after the @
  • has at least one . after the @
  • doesn't end with a .

The following inserts should fail:

sqlite> INSERT INTO users(email, user_name) VALUES('rikard@gu.se.', 'xrazor'); -- ends with a dot
Error: CHECK constraint failed: valid_email
sqlite> INSERT INTO users(email, user_name) VALUES('rikardgu.se.', 'xrazor');  -- ends with a dot and no @
Error: CHECK constraint failed: valid_email
sqlite> INSERT INTO users(email, user_name) VALUES('rikardgu.se', 'xrazor');   -- no @
Error: CHECK constraint failed: valid_email
sqlite> INSERT INTO users(email, user_name) VALUES('-rikard@gu.se', 'xrazor'); -- doesn't start with a number or letter
Error: CHECK constraint failed: valid_email
sqlite> INSERT INTO users(email, user_name) VALUES('rikard@guse', 'xrazor');   -- no dot after the @
Error: CHECK constraint failed: valid_email

But the following should be accepted:

sqlite> INSERT INTO users(email, user_name) VALUES('rikard@gu.se', 'xrazor');
sqlite> INSERT INTO users(email, user_name) VALUES('1rikard@gu.se', '1razor');
sqlite> INSERT INTO users(email, user_name) VALUES('1rikard@127.0.0.1', '2razor');
sqlite> INSERT INTO users(email, user_name) VALUES('rikard1@gu.se', 'xrazor1');

Suggested solution to Check 1

CREATE TABLE users(user_id INTEGER PRIMARY KEY,
                   email TEXT UNIQUE CONSTRAINT valid_email
                     CHECK(GLOB('[a-z0-9]*@[0-9a-z]*.[a-z0-9]*[a-z0-9]', email)),
                   user_name TEXT UNIQUE NOT NULL);
  • First part, [a-z0-9]* means "has to start with number or letter"
  • then the @
  • followed by [0-9a-z]*. which means "number or letter followed by anything followed by a dot"
  • followed by [a-z0-9] which concludes the GLOB expression meaning "has to end with a number or letter"

Check 2 challenge

Drop the table from Check 1 and create a new table which uses two GLOB expressions in the constraint (joined with e.g. AND) in order to prevent emails with more than one @. You can use the expression from the previous question and add an add an additional check (using AND between the two) for the rule of no more than one @.

Hint: How would you check that there are indeed at least two @? Use that and twist it around.

Suggested solution to the Check 2 challenge

CREATE TABLE users(user_id INTEGER PRIMARY KEY,
                   email TEXT UNIQUE CONSTRAINT valid_email
                    CHECK(GLOB('[a-z0-9]*@[0-9a-z]*.[a-z0-9]*[a-z0-9]', email)
                           AND NOT
                          GLOB('*@*@*', email)),
                   user_name TEXT UNIQUE NOT NULL
);

sqlite> INSERT INTO users(email, user_name) VALUES('@gu.se', 'xrazor10');       --Starts with @
Error: CHECK constraint failed: valid_email

sqlite> INSERT INTO users(email, user_name) VALUES('rikard@gu.se', 'xrazor10'); --OK

sqlite> INSERT INTO users(email, user_name) VALUES('rik@rd@gu.se', 'xrazor11'); --Two @s
Error: CHECK constraint failed: valid_email
sqlite> INSERT INTO users(email, user_name) VALUES('@rikard@gu.se', 'xrazor11');--Two @s 
Error: CHECK constraint failed: valid_email
sqlite> INSERT INTO users(email, user_name) VALUES('rikard@gu.se@', 'xrazor11');--Two @s, doesn't end with num or letter
Error: CHECK constraint failed: valid_email

Other - Bonus!

Here's a really weird constraint for a valid Swedish personal identification number ("personnummer"):

CREATE TABLE person(
 pnr TEXT NOT NULL PRIMARY KEY
  CONSTRAINT valid_pnr 
  CHECK(STRFTIME(DATETIME('19'||SUBSTR(pnr,1,2)||'-'||SUBSTR(pnr,3,2)||'-'||SUBSTR(PNR,5,2))) IS NOT NULL)
  CHECK(GLOB('*-[0-9][0-9][0-9][0-9]', pnr))
  CHECK (DATE('19'||SUBSTR(pnr,1,2)||'-'||SUBSTR(pnr,3,2)||'-'||SUBSTR(PNR,5,2), '+0 days')
   IS DATE('19'||SUBSTR(pnr,1,2)||'-'||SUBSTR(pnr,3,2)||'-'||SUBSTR(PNR,5,2))),
 name TEXT NOT NULL);

The first check, CHECK(STRFTIME(DATETIME('19'||SUBSTR(pnr,1,2)||'-'||SUBSTR(pnr,3,2)||'-'||SUBSTR(PNR,5,2))) IS NOT NULL), prepends the string with 19 and the the letters 1 and 2, appends a -, appends letters 3 and 4, appends a - and appends letters 5 and 6. This is then converted to a date, and converted to a string. If this works (result is not null), then it is a valid date string (almost). Example: 711124 is converted to 1971-11-24, translated to a date, and then back to a string. This works for the example.

The next check, CHECK(GLOB('*-[0-9][0-9][0-9][0-9]', pnr)), checks that the string ends with a dash followed by four digits (the number part of a Swedish personal identification number).

The last check is more complicated: CHECK (DATE('19'||SUBSTR(pnr,1,2)||'-'||SUBSTR(pnr,3,2)||'-'||SUBSTR(PNR,5,2), '+0 days') IS DATE('19'||SUBSTR(pnr,1,2)||'-'||SUBSTR(pnr,3,2)||'-'||SUBSTR(PNR,5,2))). It creates a date from the birth date part of the string, and adds 0 days to the resulting date. This is needed, because the data function sadly accepts dates like "1971-02-31" which is an invalid date. But adding 0 days to it, actually creates a valid date: "1971-03-03". This date is checked against the DATE we got from the string. Since "1971-02-31" is not the same as "1971-03-03", this constraint detects invalid dates like 31st of February.

Here are some explanatory examples:

-- Check #1
sqlite> SELECT STRFTIME(DATETIME("1971-00-00"));

sqlite> SELECT STRFTIME(DATETIME("1971-00-00")) IS NOT NULL;
0
sqlite> SELECT STRFTIME(DATETIME("1971-02-31"));
1971-02-31 00:00:00
sqlite> SELECT STRFTIME(DATETIME("1971-02-31")) IS NOT NULL;
1

-- Check #2
sqlite> SELECT GLOB('*-[0-9][0-9][0-9][0-9]', '710231-1234');
1
sqlite> SELECT GLOB('*-[0-9][0-9][0-9][0-9]', '710231-12340');
0
sqlite> SELECT GLOB('*-[0-9][0-9][0-9][0-9]', '7102311234');
0
sqlite> SELECT GLOB('*-[0-9][0-9][0-9][0-9]', '710231-12AB');
0

-- Check #3
sqlite> SELECT DATE('1971-02-31');
1971-02-31
sqlite> SELECT DATE('1971-02-31', '+0 days');
1971-03-03
sqlite> SELECT DATE('1971-02-31', '+0 days') IS DATE('1971-02-31');
0
sqlite> SELECT DATE('1971-11-24', '+0 days');
1971-11-24
sqlite> SELECT DATE('1971-11-24', '+0 days') IS DATE('1971-11-24');;
1

The constraint thus checks that the first six numbers are indeed a valid date and that they are followed by -NNNN (a dash and four numbers. It doesn't check that the very last number, the checksum, is valid. In order to do that, we'd need a function (or stored procedure). If you are interested in how to verify the last number of a "personnummer", you can read about it on Swedish Wikipedia.