Database Reference
In-Depth Information
e.
Uniqueidentiier
: his constraint can be used to create a surrogate key, which is a column
with automatically generated unique integers. Suppose you want to let the machine gener-
ate a unique number for the column ProductID every time a new product is added to the
table PRODUCT. You can use the following SQL statement to accomplish this task:
ProductID uniqueidentiier NOT NULL
Now that you have learned about data types and constraints, you can create tables. You will
create the database and tables corresponding to the entities displayed in Figure 4.1.
4.3.3 Creating Database
he irst task is to create database objects such as tables. In Windows Azure SQL Database, the
database can be created in the Windows Azure Management Portal. Later in this chapter, we will
demonstrate how to create databases Computer_Store with the Windows Azure Management
Portal.
4.3.4 Creating Tables
For each entity in Figure 4.1, we will irst deine a corresponding table.
hen, we will create the table with SQL statements based on the table deinition. Let us start
with the tables that do not have foreign key columns. he tables CUSTOMER, EMPLOYEE,
PAYMENT, and PRODUCT belong to this type. he column deinitions for the table
CUSTOMER are given in Table 4.1.
he SQL statement to create the table is
CREATE TABLE CUSTOMER
(
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50) NOT NULL,
Phone CHAR(12),
Street VARCHAR(50),
City VARCHAR(50),
State VARCHAR(50),
Zip CHAR(5)
)
he keyword phrase PRIMARY KEY indicates that a column is deined as a primary key. In
the above SQL statement, the keywords are capitalized only for clarity. In fact, keywords in an
SQL statement are not case sensitive.
For the table EMPLOYEE, the deinitions of the columns are listed in Table 4.2.
he SQL statement for creating the table EMPLOYEE is given below:
CREATE TABLE EMPLOYEE
(
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50) NOT NULL
)
