Title: SQL Basics - Create Table II
Issue: Spectrum Mar/Apr '98
author:Nathan Rector
company:Natec Systems
email:nater@northcoast.com
http:www.northcoast.com/~nater/
Last issue I showed basic example of how the CREATE TABLE command works, and how to create a simple customer file. I also showed an example of a basic file with only one multi-value field in it. Now I'm going to show some examples of more complex files.
Due to the ease of use a Mutli-value database provides for us, many programs do not limit a record to just one set of multi-value fields. If we look at the customer file again, you may find a multi-value field for the phone number as well as the fax number. If you are in the retail market, you may find a set of fields dedicated to people that are allowed to charge on a company account.
Here is an example of a file with the additional multi-value fields:
Mulit-value File:
001 ID
002 NAME
003 ADDRESS
004 PHONE]PHONE
005 FIRST]FIRST]FIRST
006 LAST]LAST]LAST
007 BEG_DATE]BEG_DATE]BEG_DATE
008 END_DATE]END_DATE]END_DATE
SQL/ODBC Table:
CREATE TABLE CUST (
ID INTEGER,
NAME CHARACTER(20),
ADDRESS CHARACTER(30),
PRIMARY KEY(ID)
);
CREATE TABLE CUST_PHONE (
ID INTEGER REFERENCES CUST,
PHONE INTEGER
);
CREATE TABLE CUST_SIGNER_LIST (
ID INTEGER REFERENCES CUST,
FIRST CHARACTER(10),
LAST CHARACTER(10),
BEG_DATE DATE,
END_DATE DATE,
PRIMARY KEY(ID)
),
Create Table with sub-values:
For most Multi-Value programer, using sub-values as part of your database to hold information is second nature and requires very little effort. To create sub-values in an SQL/ODBC table is much more of a challenge. The best example is an order file which holds a description of an item ordered, a completion date, and a delivery date. Both the completion date and delivery date can have more than one value.
Multi-value Order File:
Rec# Part# Comp Date Del Date
1001 1011-2 10/10/97 10/11/97
12/01/97 12/15/97
2087-1 10/10/97 10/11/97
12/31/97 01/05/98
SQL/ODBC Table:
CREATE TABLE ORDER (
ID INTEGER,
PRIMARY KEY(ID)
);
CREATE TABLE ORDER_PART_NO (
ID INTEGER REFERENCES ORDER,
PART_NO INTEGER REFERENCES INVENTORY,
PRIMARY KEY (ID,PART_NO)
);
CREATE TABLE ORDER_PART_NO_DETAIL (
ID INTEGER,
PART_NO INTEGER,
DATE_COMP DATE,
DATE_DEL DATE,
FORIEGN KEY (ID,PART_NO) REFERECES ORDER_PART_NO
);
The third table created has a 'FORIEGN KEY' statement. This statement allows you to create a temporary key to check against another file that is made up of more than one PRIMARY KEY.
There are times when you may want to validate the information being updated in your files before actually accepting it. If you allow your users to use Microsoft ACCESS or EXCEL to update or manipulate your information, you may want to force the user to fill out specific information correctly. There are a few keywords that you can add to your table definitions that will allow you to enforce some validation on your users.
Reference and Foreign Key:
In the examples I've used so far, you've probably seen the 'REFERENCES' a few times in the dependent tables. This key word is added to make sure that the item being placed into that field already exists in the table that 'REFERENCES' refers too.
When an item is added or changed in the dependent table, it will check the referencing file to make sure the value exists. If the value does not exist, then the update fails.
Check:
The 'CHECK' keyword comes in handy when you want to validate the input before allowing it to be saved. For example, you may not want a QTY that is less than 0 in an order file. You can place a 'CHECK' statement connected to that field to cause the update to fail if the value in QTY is less than 0.
CREATE TABLE ORDER_DETAIL (
ID INTEGER REFERENCES ORDER,
LINE_LET CHAR(2),
PART_NO CHAR(10) REFERENCES INVENTORY,
QTY INTEGER,
CHECK (QTY >= 0),
PRIMARY KEY (ID,LINE_LET)
);
Null and Not Null:
You can also specific whether a NULL is allowed to exist in a field. This allows you to force the user to fill out certain information when creating or updating your information. For example, you probably don't want a NULL in the Cust# field in your order file.
Using the key word of 'NOT NULL' requires this field to have a value in it.
CREATE TABLE ORDER (
ID INTEGER
CUST_NO INTEGER NOT NULL REFERENCES CUST,
PRIMARY KEY (ID)
);
Unique:
When working with fields that are used to store item IDs, you will want to keep from duplicating an entry. The 'UNIQUE' keyword is used to prevent this from happenning. If your users are creating a new record of information, and the value in any field with the keyword of 'UNIQUE' has the same value that is currently being updating, the update will fail.
The 'UNIQUE' keyword can be used on any field you wish, but keep in mind that it will check the value against the whole table to verify the information doesn't already exist.
As you can see, creating the multi-value effect in SQL/ODBC is more of a challenge than non-multi-valued files, but can still be done. Now that you know how SQL/ODBC files are created, in the next issue I'll talk about retreiving the information.