Datasheet

For example, the following command, CREATE DATABASE, would create a new database within the DBMS:
CREATE DATABASE MyDatabaseOfWonders
Once created, you can add tables using additional SQL statements, although first you need to specify the
name of the database where the statements will execute. To do so, you use the
USE command:
USE MyDatabaseOfWonders
Then you can use a CREATE TABLE statement to add a table to your database:
CREATE TABLE [dbo].[Product]
(
[ProductId] [uniqueidentifier] NOT NULL,
[ProductName] [varchar](200) COLLATE Latin1_General_CI_AI NOT NULL,
[ProductCost] [money] NOT NULL,
[ProductCategoryId] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ProductId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
This command creates the Product table you’ve been looking at throughout this chapter. Some of the
syntax used here is a little strange at first glance, but it’s all easy enough to understand.
First, the table name is specified as
[dbo].[Product]. This says that the Product table should belong
to the
dbo schema, where dbo is an abbreviation of database owner, and is a schema that exists in SQL
Server 2005 databases by default. This additional specification is optional, and typically the
dbo schema
will be the default schema used unless additional configuration has taken place.
The next few lines specify the columns that will be contained by the table, by way of column names,
data types, and whether they allow null values (the qualifier
NOT NULL is used for rows that don’t). Also,
in the case of text fields, the collation is specified via the
COLLATE keyword. The collation defines the
character set to use, and therefore how the data will be stored in the database. (Different character sets
require different amounts of storage for each character.)
After the column specifications, a constraint is added. Basically, constraints are additional properties that
are applied to columns and define what values are allowed in columns, how the column data should be
used (including key specifications), and indexing information. In this example, the
ProductId column is
made the primary key of the table with an ascending index and the key name
PK_Product.
The final lines of code determine the partition that the table should exist in — in this case
PRIMARY,
which is the default installation of SQL Server.
One thing is missing here — there is no foreign key specification. Assuming that you had added the
ProductCategory table, this specification would require a second command. However, before that sec-
ond command runs, you need to make sure that the
CREATE TABLE statement executes. To pause until
previous statements have completed, use the simple SQL keyword
GO:
GO
29
Database Fundamentals
44063c01.qxd:WroxBeg 9/12/06 10:31 PM Page 29