SQL Unit 12 Creating Tables, Keys, and Constraints in Access

1. SQL Unit 12Creating Tables, Keys, and Constraints in Access Kirk Scott

2. 12.1 Creating a Blank Database in Access • 12.2 Creating a Table Using Access's Graphical User Interface • 12.3 Specifying Referential Integrity Using Access's Graphical User Interface • 12.4 Entering Data • 12.5 Assignment Description

3. 12.2 Creating a Blank Database in Access • In order to create tables, you need a blank database to put them in. • In order to do this in Access, click on the Office Icon in the upper left hand corner. • In the menu there will be a New option.

5. If you click on the New option, the screen shown on the following overhead will appear. • The option to name and create the new database is on the right hand side of the screen. • By default, the database will probably be saved to the Documents folder on the machine you're using.

7. You can type in a different database name if you want to. • You can also click on the little folder icon and browse for a different destination now • Or later on you can take the Save option in the menu and specify a location to save it to at that time

8. In any case, to finish creating a new database, you will need to click on the Create button • When you do so, the screen shown on the following overhead will appear. • This is one place/way that tables can be defined. • Access is trying to be helpful, but as you’ll see, we don’t really want to make use of this screen.

10. The system has provided a table named Table1 as part of the new database you’re creating. • Click on the X in the upper right hand corner of Table1, closing it. • This will take you to a completely blank Access screen. • In that screen, click on the Create tab. • The blank screen with the Create tab highlighted is shown on the following overhead.

12. There will be several options listed underthe Create tab. • 1. The leftmost icon will be labeled Table. • DO NOT TAKE THIS ONE. • 2. The icon fourth from the left will be labeled Table Design. • THIS IS THE ONE TO TAKE.

13. If you mistakenly took number 1 above, the system would take you to a table design screen where a key field has already been put into the table. • The system is trying to be helpful, but it isn't a help, it's a hindrance… • The system assumes that you don’t know what a key field is • We know, and we want to define our own

14. If you take the fourth icon from the left, as desired, you will end up with a completely blank table design screen • This screen is shown on the following overhead • If you reach this point, you are ready for the next section, where table definition is covered.

16. 12.2 Creating a Table Using Access's Graphical User Interface • On the following overheads, the SQL commands are given for creating two tables, Mother and Person, in a one-to-many relationship. • The primary key of the Mother table is embedded as a foreign key in the Person table.

17. Following the table definitions is a query that would create an index on the lastname field of the Mother table. • This is done to refresh your memory. • The goal of this unit is to show how to do all of this using MS Access's graphical user interface.

18. CREATE TABLE Mother • (SSN TEXT(9), • lastname TEXT(24) • CONSTRAINT motherpkSSN PRIMARY KEY(SSN))

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

20. CREATE INDEX motherSSNindexasc • ON Person(motherSSN)

21. SQL is the preferred alternative for writing queries, but for designing and creating tables, the graphical user interface is preferred. • If you have understood the discussion of table definitions, constraints, and indexes as described using SQL, then you know the general concepts.

22. These concepts appear in a different form, and in some cases, using different words, in the graphical user interface. • This interface is preferred for two reasons, one good and one bad. • The good reason is that it can be easier to use than the SQL syntax.

23. The bad reason is that in Microsoft Access table design, if you attempt to do something using SQL, there is no guarantee that in all cases it will work. • When Microsoft designed Access, the graphical user interface was the primary interface, so if you do something there, it should work.

24. Here is a logical outline of what is involved in creating tables. • Specifying referential integrity is done after the related tables are created using a different view in the graphical user interface. • That will be covered in the next section of the unit.

25. On the following overheads an outline is given of the steps you would follow when using the graphical user interface. • It is organized in an order that makes sense when using the graphical user interface • It includes options that correspond to the points listed above.

26. This outline is also a preview of everything you will find in the interface, and it lists options that can be skipped. • After everything is summarized in the outline, the things listed in the outline will be shown with screenshots.

27. Creating tables • At the top of the design view for a table: • Give each field a name. • As soon as you enter this first field, the bottom of the design view will expand to include the options listed under the following black bullet. • If the field is the primary key field, right click on it and specify that • Alternatively, with that field highlighted, click on the key symbol above • Give each field a data type

28. At the bottom of the design view there will be a long list of options: • DO: Specify a Field Size for those data types where the user can specify a size (like the width in characters of a text field) • SKIP: Specify a field Format • SKIP: Specify a field Input Mask • SKIP: Specify a field Caption • SKIP: Specify a field Default Value • SKIP: Specify a field Validation Rule • SKIP: Specify a field Validation Text

29. DO: Specify whether the field is required or not. • This is the equivalent of specifying NOT NULL. • If the field is not required, then NULL is allowed. • Notice this unpleasant fact: • Even though you indicate that a field is the primary key field, this does not automatically change the setting to "Required—Yes". • To be on the safe side, it would be wise to check "Required—Yes" on the primary key field.

30. DO: Specify whether Allow Zero Length is OK. • This is a detail that is related to the question of nulls. • A zero length data value is virtually a null. • Notice this unpleasant fact: • Even though you indicate that a field is the primary key field, this does not automatically change the setting to "Allow Zero Length—No". • To be on the safe side, it would be wise to check "Allow Zero Length—No" on the primary key field.

31. DO: Specify whether the field is Indexed. • There are two options, No Duplicates and Duplicates Allowed. • Either way, the field is indexed. • Duplicates vs. No Duplicates corresponds to specifying NOT UNIQUE or UNIQUE in SQL. • Note that when you make something the primary key, by default, this becomes indexed with no duplicates allowed. • Other fields can be indexed at will.

32. SKIP: Specify Unicode Compression • SKIP: Specify IME Mode • SKIP: Specify IME Sentence Mode • SKIP: Specify Smart Tags

33. When you finish the design, you should close the table by clicking on the X in the upper right hand corner. • At this time you can save the table in the database and give it a name. • This is sort of like closing and naming queries. • What follows are screen shots for the completed designs for the Mother and Person tables.

34. Keep in mind when looking at the screenshots that the general characteristics listed at the bottom apply to whichever field in the table design is highlighted at the top. • In the Mother table design, the primary key field, SSN, is highlighted.

36. The Person table design screenshot follows. • Notice that in this screenshot, it's the foreign key field, motherSSN, which is highlighted. • In addition to other characteristics, it is indexed, and for the foreign key field, "Duplicates OK" is the desired setting.

38. 12.3 Specifying Referential Integrity Using Access's Graphical User Interface • In order to include referential integrity in the database design, you have to go to the rightmost tab at the top of Access • This is theDatabase Tools tab • Under this tab is the Relationships icon • This is shown on the following overhead

40. To create a primary key-foreign key relationship between tables in a database, you have to click on the relationships button. • A dialog box will appear where you get to add the tables of your database to a graphical representation of the database. • The screenshot below shows the dialog box.

42. This is what the Relationships screen looks like after adding both tables.

44. To create a one-to-many relationship between Mother and Person: • Click on the primary key of the Mother table, SSN • Drag the mouse to the foreign key field of Person, motherSSN • Drop there

45. Remember that a primary to foreign key relationship is captured by embedding the primary key of the one table as a foreign key in the many table. • When using the graphical user interface you click, drag, and drop, from the one/primary key field to the many/foreign key field

46. After you have taken the first step in creating the relationship, the system will prompt you to see if you want to enforce referential integrity. • This is done by means of a dialog box, which is shown on the following overhead.

48. Then you have to check the Enforce Referential Integrity box and choose whether updates or deletes should cascade. • If you do not choose the cascade option, the default is to restrict. • A representative choice is shown.

50. After you click the create button, the relationships screen will look as shown on the following overhead.