3. Constraints

A constraint is a rule that you apply or abide by while doing SQL operations. They are useful in cases where you wish to make the data inside your database more meaningful and/or structured. Consider the example of the programming languages table - every programming language that has been created, must have an author (whether a single person, or a couple or a committee). Similarly it should have a year when it was introduced, be it the year it first appeared as a research paper or the year a working compiler for it was written. In such cases, it makes sense to create your table in such a way that certain fields do not accept a NULL (empty) value.

We now modify our previous CREATE TABLE statement so that we can apply the NULL constraint to some fields.

1 CREATE TABLE proglang_tblcopy (
2 id        INTEGER     NOT NULL,
3 language  VARCHAR(20) NOT NULL,
4 author    VARCHAR(25) NOT NULL,
5 year      INTEGER     NOT NULL,
6 standard  VARCHAR(10) NULL);

We see in this case that we have achieved our objective of creating a table in which the field’s id, language, author and year cannot be empty for any row, but the new field standard can take empty values. We now go about trying to insert new rows into this table using an alternative INSERT syntax.

3.1 Selective fields INSERT

From our last encounter with the INSERT statement, we saw that we had to specify the data to be inserted in the same order as specified during the creation of the table in question. We now look at another variation which will allow us to overcome this limitation and handle inserting rows with embedded NULL values in their fields.

 1 INSERT INTO <Table_Name>
 2 
 3 (<Field Name 1>,
 4  <Field Name 2>,
 5  . . .
 6  <Field Name N>)
 7 
 8 VALUES
 9 
10 (<Value For Field 1>,
11  <Value For Field 2>,
12  . . .
13  <Value For Field N>);

Since we specify the field order in the statement itself, we are free to reorder the values sequence in the same statement thus removing the first limitation. Also, if we wish to enter a empty (NULL) value in any of the fields for a record, it is easy to do so by simply not including the field’s name in the first part of the statement. The statement would run fine without specifying any fields you wish to omit provided they do not have a NOT NULL constraint attached to them. We now write some INSERT statements for the proglang_tblcopy table, in which we try to insert some languages which have not been standardized by any organizations and some which have been.

1 INSERT INTO proglang_tblcopy (id, language, author, year, standard)
2 VALUES (1, 'Prolog', 'Colmerauer', '1972', 'ISO');
3 
4 INSERT INTO proglang_tblcopy (id, language, author, year)
5 VALUES (2, 'Perl', 'Wall', '1987');
6 
7 INSERT INTO proglang_tblcopy (id, year, standard, language, author)
8 VALUES (3, '1964', 'ANSI', 'APL', 'Iverson');

When you run this through your SQL interface, 3 new rows would be inserted into the table. Notice the ordering of the third row; it is not the same sequence we used to create the table. Also Perl has not been standardized by an international body, so we do not specify the field name itself while doing the INSERT operation.

To verify the results of these statements and to make sure that the correct data went into the correct fields, we run a simple query as before.

1 SELECT * FROM proglang_tblcopy;

3.2 Primary Key Constraint

A primary key is used to make each record unique in atleast one way by forcing a field to have unique values. They do not have to be restricted to only one field, a combination of them can also be defined as a primary key for a table. In our programming languages table, the id field is a good choice for applying the primary key constraint. We will now modify our CREATE TABLE statement to incorporate this.

1 CREATE TABLE proglang_tbltmp (
2 id        INTEGER     NOT NULL  PRIMARY KEY,
3 language  VARCHAR(20) NOT NULL,
4 author    VARCHAR(25) NOT NULL,
5 year      INTEGER     NOT NULL,
6 standard  VARCHAR(10) NULL);

ID fields are usually chosen as primary fields. Note that in this particular table, the language field would have also worked, since a language name is unique. However, if we have a table which describes say people - since two people can have the same name, we usually try to find a unique field like their SSN number or employee ID number.

3.3 Unique Key Constraint

A unique key like a primary key is also used to make each record inside a table unique. Once you have defined the primary key of a table, any other fields you wish to make unique is done through this constraint. For example, in our database it now makes sense to have a unique key constraint on the language field. This would ensure none of the records would duplicate information about the same programming language.

1 CREATE TABLE proglang_tbluk (
2 id        INTEGER     NOT NULL  PRIMARY KEY,
3 language  VARCHAR(20) NOT NULL  UNIQUE,
4 author    VARCHAR(25) NOT NULL,
5 year      INTEGER     NOT NULL,
6 standard  VARCHAR(10) NULL);

Note that we write the word UNIQUE in front of the field and omit the KEY in this case. You can have as many fields with unique constraints as you wish.

3.4 Differences between a Primary Key and a Unique Key

You might have noticed that the two constraints discussed above are similar in their purpose. However, there are a couple of differences between them.

1. A primary key field cannot take on a NULL value, whereas a field with a unique constraint can. However, there can be only one such record since each value must be unique due to the very definition of the constraint.
2. You are allowed to define only one primary key constraint but you can apply the unique constraint to as many fields as you like.