Relational Databases for Archaeology Main Menu

Constraints

4
Data types are one way to keep your data clean by limiting what users can enter into each field, for example by preventing people from accidentally entering text into a numeric field.  Another way to restrict the data that can be entered into a field is by using constraints, which help protect data by constraining the range of values a user can enter.  In other words, constraints allow the user to explicitly control what is entered into a particular field.  For example, you might set a constraint to limit entry in a date field to a day within only the current year. 

We can constrain the type, values, and other forms of the data entered into a field.  A special type of constraint, called a key, which was discussed in the previous section.  PostgreSQL provides three other types of constrains: 'unique', 'check', and 'exclude'.  A unique constraint allows you to specify a set of fields that should contain data where every row has different values and is therefore unique from those same fields in all other rows.  This functionality is covered by keys and there are not so many other uses for the unique constraint on an archaeological project.  The exclude constraint is more complex and will not often be used by archaeologists.  The check constraint is very useful, on the other hand.  You can use operators such as < to check the data entered against some predetermined condition.

Here are some common operators:In order to compare your field's contents with a text string or date, you need to use special formatting, by surrounding either with single quotes.  For example, you might check to make sure that a particular context type is not entered by setting your check constraint on that field to <> 'large pit', as we do in the following screencast (note that the check is case sensitive).  Dates should be formatted as year-month-day, such as if your date_opened field is checked to be > '2018-01-31', ie anytime after January 31st 2018.  Note that in order to include January 31st itself, you would need to use the >= operator.  You can use multiple operators together with the 'and' or 'or' keywords to setup logical combinations.  With 'and', both checks must pass, with 'or', only one check needs to pass.  You can also compare the values in one field to another, as we do by making sure the date_closed is later than the date_opened.  In the following screencast, we also set a check constraint to limit the range of possible values of the top elevation, since the topmost point on our entire site is at 155m above sea level, and the top must obviously be above the bottom.  

Finally, we go to Libreoffice Base in order to test all of these constraints by entering data into the contexts table.  You need to save the row in order to test the constraint, you can do this by leaving the row.  If you get completely stuck, you can use the undo button to get yourself out of the situation.

Note that you can add constraints to a table after you have already entered data into that table.  However, if any of the existing data violates the constraint, you will not be able to save or apply that constraint.  You would first need to change the data in the table and then save the constraint.

Exercise: Add Constraints to your Tables

Add constraints to all three tables.  Add the constraints shown above to the contexts table, but also develop them further.  We should probably prevent people from entering dates too far in the future.  We should also check to make sure that an open date has been entered before a close date is entered (hint: use the 'is not null' operator).  We might want to constrain the bottom elevation, and remember that a top elevation should be entered before a bottom elevation since we dig down. Add any additional constraints you can think of to the areas and contexts table.  In the samples table, we should probably prevent negative counts and weights.  What other constraints can we add?

Test all of your constraints by adding at least two rows of data to each table, using the Libreoffice Base interface.

This page has paths:

  1. Relational Databases for Archaeology Peter Cobb

This page references: