SQL Unit 10 Creating Tables, Adding Constraints, and Defining Indexes

1. SQL Unit 10Creating Tables, Adding Constraints, and Defining Indexes Kirk Scott

2. 10.1 The Keyword CREATE • 10.2 Background on General Constraints • 10.3 The Primary Key Constraint • 10.4 The Uniqueness Constraint • 10.5 The Not Null Constraint • 10.6 The Foreign Key Constraint • 10.7 What are Indexes? • 10.8 Syntax for Indexes

3. 10.1 The Keyword CREATE

4. 10.1 Creating Tables • Microsoft Access has a graphical user interface for defining database tables. • This interface allows you to name the fields in a table, specify their types and sizes, designate a key, and also add various kinds of formatting, data integrity, and referential integrity constraints.

5. Assuming you already have an understanding of relational databases, the graphical user interface is probably the preferred way of creating a table definition. • It will be covered in Unit 12, when explaining the final project assignment. • In the meantime, it is still useful to see how these things are done in SQL.

6. Dealing with SQL syntax is not as convenient as dealing with a graphical user interface, but using SQL has the advantage that everything is made explicit. • If you have mastered the SQL, then it's safe to say that you will be able to manage the kinds of things you'll find in the graphical user interface.

7. The converse is less likely to be the case. • Working with the interface may not be the easiest way to get a clear, organized picture of what table creation is all about. • Certain critical points may be hidden in the interface and easily overlooked.

8. On the overhead following the next one, an example is given of using the CREATE TABLE command in SQL, where one of the tables of the sample database is created. • Everything that is shown is correct, and it is sufficient to create the table. • In this command the primary key is not designated.

9. In the long run, this is not ideal, but it is acceptable. • Under the covers, the system will in effect supply a hidden primary key which it will use to prevent duplicate records, and so on. • The syntax for specifying primary and foreign key fields will be given starting with the next section. • Notice that the SQL syntax parallels the schema notation for a table.

10. CREATE TABLE Car • (vin TEXT(5), • make TEXT(18), • model TEXT(18), • year TEXT(4), • stickerprice CURRENCY, • dealercost CURRENCY)

11. Although strictly speaking the command above is not a query, it can be entered as a query in Microsoft Access using the SQL editor. • Clicking the execute button will cause the table to come into existence.

12. 10.2 Background for General Constraints • A simple example of creating a table using SQL is given on the next overhead. • The fields and their types and sizes are defined. • The primary key field is not defined.

13. CREATE TABLE Person • (SSN TEXT(9), • lastname TEXT(12), • dob DATE)

14. The Person table and other tables will be used to illustrate these four types of constraints, which can be added to the table definition: • Primary key • Uniqueness • Not null • Foreign key

15. When including additional specifications or conditions in a table definition, these are known generally as constraints. • In general, it is also possible to add constraints to table definitions after the tables have been created. • This unit will cover including constraints in the original definition. • The next unit will cover adding or dropping constraints after a table has been created.

16. If constraints are named, this makes it possible to refer to them later on, in particular, so that they can be removed from the table. • There are various forms of the syntax for constraints. • These various forms are essentially redundant. • Not all of the forms will be shown in the following sections, just a consistent set of forms that should be relatively easy to remember.

17. 10.3 The Primary Key Constraint • In a complete table definition it would be desirable to specify the primary key. • Remember that the primary key value has to be a unique identifier for each record in the table and no part of the primary key can be null. • These requirements together are formally known as entity integrity. • Specifying a primary key field in a table definition is a kind of constraint.

18. This example shows the syntax for specifying a primary key in a table definition: • CREATE TABLE Person • (SSN TEXT(9), • lastname TEXT(12), • dob DATE, • CONSTRAINT personpkSSN PRIMARY KEY(SSN))

19. As usual, the keywords are capitalized. • The field name SSN happens to be capitalized too in this example, but that is a coincidence. • It is a good idea to give the constraint a descriptive name. • The name can't have spaces in it.

20. Recall that it is possible to have a table with a concatenated key field. • This means that the unique identifier for a record in the table is the combination of the values of two different fields in the table.

21. This can happen when there is a many-to-many relationship, and the primary keys of both of the tables in the many-to-many relationship are embedded as foreign keys in a table in the middle. • Assuming that there was a Chimpanzee table with chimpid as its primary key, the relationships between chimps could be captured by the table design given in the next example.

22. The table's primary key would be the concatenation of chimpid1 and chimpid2. • You could specify the primary key by including the line shown at the end of the table definition. • All you have to do is list the concatenated key fields inside the parentheses, separated by commas.

23. CREATE TABLE Chimprelationships • (chimpid1 TEXT(6), • chimpid2 TEXT(6), • beginningdateDATE, • enddateDATE, • CONSTRAINT chimppk PRIMARY KEY(chimpid1, chimpid2))

24. 10.4 The Uniqueness Constraint • It may be desirable to require that other fields in a table besides the primary key be unique. • It is possible to have a situation like this: • The Person table is redefined so that it has a personid field which is the primary key. • The SSN is still included, but it’s not the primary key field anymore. • This is shown on the next overhead.

25. CREATE TABLE Person • (personid TEXT(12), • lastname TEXT(12), • dob DATE, • SSN TEXT(9) • CONSTRAINT personpkpersonid PRIMARY KEY(personid))

26. In a situation like this, the personid should be unique and not null, because it's the key. • It would also be desirable for the SSN to be unique and probably not null. • Enforcing uniqueness would be another kind of constraint that could be added to the table definition. • Adding a not null constraint will be shown in the next section.

27. The next example shows the syntax for specifying the primary key and also for specifying that another field in the table be unique.

28. CREATE TABLE Person • (personid TEXT(12), • lastname TEXT(12), • dob DATE, • SSN TEXT(9), • CONSTRAINT personpkpersonid PRIMARY KEY(personid), • CONSTRAINT SSNunique UNIQUE(SSN))

29. The personid field will be constrained to be unique because it's the primary key. • The SSN field will be constrained to be unique by the separate uniqueness constraint on it. • As before, the key words are shown capitalized. • It's a coincidence that the field SSN is also capitalized.

30. 10.5 The Not Null Constraint • It is also possible to have a situation where it is possible for a field to have duplicate values in different records, but you don't want to allow null values. • For example, in the Person table, you may not wish to allow entries for people who do not have names. • This is yet another example where a constraint would be used.

31. Specifying NOT NULL as a constraint on a table is slightly different from the other constraints, because it is not named. • All you have to do is put the constraint after the relevant field in the table definition: •  This is shown on the next overhead.

32. CREATE TABLE Person • (personid TEXT(12), • lastname TEXT(12) NOT NULL, • dob DATE, • SSN TEXT(9), • CONSTRAINT personpkpersonid PRIMARY KEY(personid), • CONSTRAINT SSNunique UNIQUE(SSN))

33. 10.6 The Foreign Key Constraint • Referential integrity defines the requirements for a primary key to foreign key relationship between two tables or between a table and itself. • Consider this alternative definition of the Person table: • CREATE TABLE Person • (SSN TEXT(9), • lastname TEXT(12), • motherSSN TEXT(9), • dob DATE, • CONSTRAINT personpkSSN PRIMARY KEY(SSN))

34. Let there be a Mother table which also has a primary key named SSN. • The motherSSN field in the Person table is known as a foreign key and it refers to the SSN field in the Mother table. • Referential integrity states that the motherSSN field in the Person table cannot contain values which do not exist in the SSN field in the Mother table. • Enforcing referential integrity is another kind of constraint.

35. When putting a referential integrity constraint into a database design, the constraint goes into the foreign key table, not the primary key table. • Let there be a table named Mother with a primary key field defined as shown on the next overhead.

36. CREATE TABLE Mother • (SSN TEXT(9), • …, • CONSTRAINT motherpkSSN PRIMARY KEY(SSN))

37. Then a foreign key constraint in the Person table would be as shown on the next overhead.

38. CREATE TABLE Person • (SSN TEXT(9), • lastname TEXT(12), • motherSSN TEXT(9), • dob DATE, • CONSTRAINT personpkSSN PRIMARY KEY(SSN), • CONSTRAINT personfkmother FOREIGN KEY(motherSSN) REFERENCES Mother(SSN))

39. Notice that there are two sides to the foreign key constraint. • It is not possible to enter new values into the foreign key table, or update values in the foreign key table to ones that don't exist in the primary key table. • It would also violate referential integrity if there were changes in the primary key table that left values in the foreign key table without matches in the primary key table.

40. Referential integrity is so important that the system also protects the database contents from changes in the primary key table. • There are two possibilities: • 1. If a primary key record is deleted, if it had corresponding foreign key records, they would be orphaned. • It is most common in this case to disallow such deletions. • This is known as "ON DELETE RESTRICT".

41. 2. If the primary key value is updated, if that value had matches in the foreign key table, they would be orphaned. • It is most common in this case to specify that the corresponding foreign key records be updated to match. • This is known as "ON UPDATE CASCADE".

42. The next overhead shows how the foreign key constraint example would look with DELETE and UPDATE restrictions/cascades explicitly specified:

43. CREATE TABLE Person • (SSN TEXT(9), • lastname TEXT(12), • motherSSN TEXT(9), • dob DATE, • CONSTRAINT personpkSSN PRIMARY KEY(SSN), • CONSTRAINT personfkmother FOREIGN KEY(motherSSN) REFERENCES Mother(SSN) • ON DELETE RESTRICT • ON UPDATE CASCADE)

44. Notice that with these options set, the system is doing a lot of work on behalf of the user, protecting the integrity of the data in the related tables.

45. 10.7 What are Indexes? • 1. An index can be described as a construct that supports two-column lookup. • Suppose you're interested in words and their locations in a book. • You look up the word in the index, and what you find is its page number.

46. This is a somewhat more detailed description of the situation: • A) The words in a book don't occur in sorted order. • They appear in sentences and paragraphs in an order that is determined by the topic under discussion and the rules of grammar.

47. B) The index of a book consists of the important words in the book sorted in alphabetical order, followed by the page numbers where those words appear. • This is your two column lookup. • You look up the word, and what you find is the page where it occurs.

48. 2. Being able to look things up is critical to the internal operation of a database management system and the execution of queries. • Remember that technically tables are like sets: • Their contents do not have to be kept in any particular order.

49. If you want to see the contents of tables in sorted order, you know that you can put the key words ORDER BY in a query, but this doesn't change the order in which the records are stored. • You may have noticed that if you don't specify ORDER BY in a query, the results tend to come out sorted in primary key order.

50. This still doesn't signify that the contents of the table are maintained in that order. • It just means that that order may be the default order for results in some cases. • It is generally the case that the records in a table are simply stored in the same order that they were entered into the table.