ALTER TABLE tablename DROP PRIMARY KEY;

Remember that a table can only have one primary key. If you decide that a different column is better

suited as a primary key, you must drop the original one first.

Summary

You covered a lot of material today. You learned about the various types of tables that MySQL has to offer,
and you learned how to CREATE, ALTER, and DROP these tables. You also learned about the importance of
indexes and how they can speed up your database access. You read about the importance of naming
conventions and how they can assist you and your colleagues in maintaining and using your database. Most
importantly, you learned how to implement the blueprint from the design phase.

Q&A

Q:

I like the idea of temporary tables. What I don't like is that they are

invisible to everyone else. Is there a way to make a temporary table

viewable by everyone?

A:

There definitely is a way. MySQL provides a temporary table, called a HEAP

table, that is available to everyone who makes a connection to the server.

This table disappears when the server shuts down. This table exists solely

in memory and is very fast. However, it has some limitations. Text and blobs

cannot be used. They would take up too many resources and would slow

things down. All the data types must be of a fixed length. This, too, is to help

conserve resources and speed things up. Also, indexes cannot have NULL

values and can only be used in equal-to and not-equal-to comparisons. To

create a table like this, you would use the following syntax:

CREATE TABLE tablename (columenames columntypes)

TYPE=HEAP;

You can also convert an existing table to a HEAP table, but remember

that the table will be dropped automatically when the server shuts

down. The syntax to do this looks like the following:

ALTER TABLE tablename TYPE=HEAP;

Q:

I have used other RDBMS like Sybase and SQL Server 7. They have

table constraints and checks. Does MySQL have these as well?

A:

The quick answer is no. However, MySQL can parse this syntax, so you can

easily import existing schemas into MySQL without a lot of hassle.

Q: How much data can a MySQL table hold??

A:

This question can be a little tricky to answer. The maximum size of a

MySQL table is based on the file system of the OS (operating system).

MySQL stores its data in files whose size depends entirely on the operating

system. On 64-bit systems, they are so large that there really is no

limitation. On a Windows NT platform, you can expect around a 2TB limit.

Exercises

1. Create the schema for the Meet_A_Geek project. Base the schema on the blueprint
   that was developed on Day 3.


2. Create indexes for the Meet_A_Geek database. Use the lessons you learned here to
   determine which columns should be indexed.

Day 7: MySQL Data Types

Overview

As with most Relational Database Management Systems (RDBMS), MySQL has specific column data types.
Today you will learn the following:
ƒ MySQL string and character types
ƒ MySQL numeric types
ƒ Enumerations and sets
ƒ Column modifiers