Creating Tables

A database must be created before a table is added. The database administrator usually creates the database through the database-administration tools or GUI. If you are also the person administering the database, create a database so that we can work with tables inside of it.

In MySQL, creating a database is easy to do from the command line. To create a database named widgets, this is all you need to do:

mysqladmin create widgets -uusername -ppassword 

You won't get any fancy messages back if the database creation is successful; you will just return to the command-line.

Now we are ready to work with tables, so let's take a look at the SQL needed to create a table called products.

The SQL commands to create the products table are listed below. You can recreate the products table from the database interface or can write a short Perl script to execute the SQL for you. The SQL keywords are in all capital letters to make the SQL statement easier to read.

01: CREATE TABLE products( 02: Pid   INT NOT NULL, 03: Item  VARCHAR(50), 04: Descr VARCHAR(255), 05: Price REAL, 06: Vid   INT NOT NULL);

This SQL statement creates a table named products with five items in it.

Line 1 creates the table named products and opens the parenthesis for the data types.

Line 2 creates the product id field and names it Pid. It sets the data type to INT (integer) and adds a NOT NULL clause, which causes the database server to generate an error if someone tries to add a record but leaves this field blank.

Tip 

It is always a good idea to make your data types as specific as possible because the database can use that specific information to make storage and access more efficient.

Line 3 creates the item-name field and names it Item. This line sets the data type to VARCHAR with a size of 50. The VARCHAR data type allows up to x number of records.

Line 4 creates a field for the item description and names it Descr. This field is also set to the data type VARCHAR but is 255 characters long instead of 50 like the preceding Item field.

Note 

I do not abbreviate the description field Desc because DESC is a reserved word in SQL and is used for sorting items in a descending order.

Line 5 creates the item-price field and names it Price. This field is set to a REAL data type because we know that prices typically have a decimal point in them, which no integer can have. Line 6 is the last line of this SQL statement. It creates a field named Vid, which stands for Vendor ID. This field is also an INT data type and is set so that it cannot be NULL.

Next, we need a vendor table so that we can link a product to a vendor.

01: CREATE TABLE vendors( 02: Vid     INT NOT NULL, 03: Name    VARCHAR(100), 04: Address VARCHAR(100), 05: City    VARCHAR(50), 06: State   CHAR(2), 07: Zip     VARCHAR(10), 08: Phone   VARCHAR(25));

Line 1 creates the table named vendors and opens the parenthesis for the data types.

Line 2 creates the vendor id field and names it Vid. It sets the data type to INT and adds a NOT NULL clause, which causes the database server to generate an error if someone tries to add a record but leaves this field blank. This is the field that we'll use to keep the relation between the product and the vendors.

Line 3 creates the name field and names it Name. This line sets the data type to VARCHAR with a size of 100.

Line 4 creates a field for the address and names it Address. This field is also set to the data type VARCHAR and is also100 characters long.

Line 5 creates a field for the city and names it City. This field is also set to the data type VARCHAR and is 50 characters long.

Line 6 creates a field for the state and names it State. This field is set to the data type CHAR and is 2 characters long. It is kept this short to allow only each state's two-letter abbreviation.

Line 7 creates a field for the ZIP code and names it Zip. This field is also set to the data type VARCHAR and is 10 characters long.

Line 8 creates a field for the phone number and names it Phone. This field is also set to the data type VARCHAR and is 25 characters long. You might think we could use a number data-type for this, but ‘234-1234' isn't a number, because it has a ‘-' in the middle, to say nothing of the parens and the space in ‘[phone]'.

That is all the SQL that is needed to create two tables that can be used for an item database with relations between each item and its vendor. Please note that the field sizes and names I used are very U.S.-centric. If you live somewhere that needs different field types or sizes, please adjust as necessary to accommodate for changes in address format.