[Paul Gardner]

24/09/2020 at 6:58 pm

Re Validation:
I teach the pupils how to write/edit a script that creates the tables with validation rules defined in the SQL create table commands. These can validate presence check, field length, range check, list of values and PK/FK constraints.

https://repl.it/@paulatdunedin/N5201819

shows how this can be done in SQLite, with some code to test that it works.

Paul

[Richard Scott]

24/09/2020 at 8:34 pm

Regarding Validation then that is implemented as follows when using sqlite3:

Validation
The four required types of validation are added as follows:
1. presence check
use NOT NULL in the table definition
petName TEXT(20) NOT NULL, a petName is required

2. restricted choice
add a check constraint in the table definition:
CHECK( house IN(‘Assisi’,’Galilee’,’Jericho’) ) house must be Assisi, Galilee or Jericho

3. field length
add a check constraint in the table definition:
CHECK( LENGTH(dateBorn) = 10 ) checks that a dateBorn has exactly 10 characters
CHECK( LENGTH(password) >= 8 ) checks that password is at least 8 characters

4. range check
add a check constraint in the table definition:
CHECK( weight BETWEEN 0.1 AND 200 ) min weight 0.1 and max weight 200 are allowed

[Richard Scott]

24/09/2020 at 8:59 pm

Consider a portion of the database code I posted previously.
This could implement the following validation rules:

Every record must have an artist name
genre can only be Rock or Metal
country must be 2 characters or more

CREATE TABLE artist(
    artistname     TEXT NOT NULL,
    country        TEXT,
    genre          TEXT,
    PRIMARY KEY    (artistname),
        CHECK( genre IN ('Rock','Metal') ),
        CHECK( LENGTH(country) >= 2 )
);

BTW – I’m not actually a fan of CAPITALISING SQL commands but when creating sheets to be printed in black ink and also when typed into a Python editor they are formatted as text (usually green) I think capitalisation makes the keywords stand out in a more readable way.

[Richard Scott]

24/09/2020 at 9:21 pm

This is a repeat of the previous code but hopefully formatted better. Just copy and paste this into any Python editor and run it…

import sqlite3
db = sqlite3.connect(':memory:')

db.execute( '''
    CREATE TABLE artist(
        artistname TEXT PRIMARY KEY,
        country    TEXT,
        genre    TEXT
    )
''')

db.execute( '''
    INSERT INTO artist(artistname, country, genre)
    VALUES
        ( 'ACDC' ,'Australia' ,'Rock'),
        ( 'Beatles','UK' ,'Pop'),
        ( 'Cramps' ,'USA' ,'Psychobilly'),
        ( 'Danzig' ,'USA' ,'Rock')
''')

results = db.execute('''
    SELECT artistname, country
    FROM artist
    WHERE artist.genre = 'Rock'
''')

print ('Rock bands…')
for record in results:
    print( f'The band {record[0]} is from {record[1]}')

db.close()