Learning SQL - Constraints

These tips are mostly applicable to all forms of SQL, some are specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.

Introduction

We saw in our earlier lesson- SQLIte Datatypes, that SQLite doesn't really care what data you enter into your carefully crafted database. You can enter text into a number field, numbers into a text field and whatever you like into field type that you have made up. So how do we ensure that only the type of data we want is inserted into our database? Constraints.

Constraints

Constraints 'do what it says on the tin' - they constrain your data. You can tell the database what types of data you want, if you only want unique data, foreign key links - great!

The first type of constraint we will talk about is the Primary Key Constraint.

Primary Key Constraint

Yes a Primary Key is a constraint. A unique value must be entered (or automatically generated by the database). Primary Keys were covered in detail in our Primary Keys lesson.

Unique Constraints

Unique Constraints (or Unique Indexes) enforce - yes you geussed it, Uniqueness, on column values!

Let's create a table in our test database to hold the various Sports teams our kiddies support.

create table QiSQL_Teams (
id integer primary key,
sport text ,
team_name text,
nickname text,
founded_year integer,
ground_name text,
unique ( sport , team_name ) ) ;

See how we have cleverly applied two constraints on our new table, the id column has an Integer Primary Key constraint and we have added a Unqiue constraint on the Sport and Team columns (we couldn't just use team name as there may be duplicate team names across the sports!)

So, let's test our constraints by inserting some data:

insert into QiSQL_Teams ( sport , team_name, nickname , founded_year, ground_name ) values (
'Football' , 'Tottenham Hotspur', 'Mighty Spurs!' , 1882 , 'White Hart Lane' );

insert into QiSQL_Teams ( sport , team_name, nickname , founded_year, ground_name ) values (
'Football' , 'Arsenal', 'Woo Cares?' , 1999 , 'Emirates Stadium' );

insert into QiSQL_Teams ( sport , team_name, nickname , founded_year, ground_name ) values (
'Football' , 'Tottenham Hotspur', 'The Spurs' , 1000 , 'White Hart Lane, London' );

The first two insert work perfectly, the third one gives us an error - columns sport, team are not unique.

So Unique constraints work nicely.

Check Constraints

You can get very creative (and restrictive) using these beauties!

We want to stop folks entering silly Founding Years, and have decide that the oldestfounding year is going to be 1800 and the highest will be 2009. How can we do this? Lets try and add a new Constraint using the Alter Table command.

alter table QiSQL_Teams add check ( founding year between 1800 and 2009);

Flip! It doesn't work. SQLIte currently only allows Check Constraints in Table Creation commands! Flip again!

OK, Let's trash our table and create it again, adding another check constraint that forces us to enter a team name of more than 4 characters, we'll also name the team_name length constraint to see if that has any effect:

drop table QiSQL_Teams;

create table QiSQL_Teams (
id integer primary key,
sport text ,
team_name text constraint Team_Length_Check check ( length ( team_name) > 4 ),
nickname text,
founded_year integer check ( founded_year between 1800 and 2009 ),
ground_name text,
unique ( sport , team_name ) ) ;

Now we'll try and enter some data to test the Check constraint:

insert into QiSQL_Teams ( sport , team_name, nickname , founded_year, ground_name ) values (
'Football' , 'Tott', 'Mighty Spurs!' , 1700 , 'White Hart Lane' );

OK, the Check Constraints did their job , but, we get a most unhelpful error message - 'constraint failed'. We don't know which Constraint failed - just that at least one did!

When we are building a database application, we wnat to give our users as much feedback as possible. If they enter invalid data we need to tell them, so the next section will cover Using Triggers as Constraints - exciting!