Database Reference
In-Depth Information
[TitleKey] [int] NOT NULL,
[StoreKey] [int] NOT NULL,
[SalesQuantity] [int] NOT NULL,
CONSTRAINT [PK_FactSales] PRIMARY KEY CLUSTERED
( [OrderNumber] ASC,[OrderDateKey] ASC, [TitleKey] ASC, [StoreKey] ASC )
)
GO
By typing this code into a query window and clicking the “! Execute” button, the table is created with the
composite primary key on the first four columns. After you create all the tables in a database, you can create
foreign key constraints between the tables using code similar to that shown in Listing 5-7.
Listing 5-7.
Adding Foreign Key Constraints
ALTER TABLE [dbo].[FactSales] WITH CHECK ADD CONSTRAINT [FK_FactSales_DimStores]
FOREIGN KEY([StoreKey]) REFERENCES [dbo].[DimStores] ([Storekey])
GO
ALTER TABLE [dbo].[FactSales] WITH CHECK ADD CONSTRAINT [FK_FactSales_DimTitles]
FOREIGN KEY([TitleKey]) REFERENCES [dbo].[DimTitles] ([TitleKey])
GO
Note that in Listing 5-7 there are two foreign key constraint commands. If a table has multiple foreign key
relationships, as most fact tables do, you need to execute a separate command for each of them.
At this point, you have seen three ways to create tables: using SQL code, using the table designer and using
the diagramming tool. All three ways accomplish the same thing.
Now, after so many pages of theory, it is time to do another exercise!
eXercISe 5-2. creatING taBLeS aND FOreIGN KeY cONStraINtS
In this exercise, you create the tables and foreign key constraints in the Publication Industries data warehouse.
You can choose to use the SQL code presented here, the table designer or the diagramming tools to
accomplish your goal.
The files for this exercise, as well as all the exercises throughout this topic, are available in the downloadable
book content.
creating the tables
The first thing we need to do is create the tables. Let's do that now.
1.
If it is not open already, open SQL Server Management Studio (see Exercise 5-1 for
more details).
2.
Decide on a method for creating the tables and begin creating them utilizing the
table names, column names, data types, nullablity, and primary keys represented in
Listing 5-8. You can find a copy of this SQL script in the downloadable book files.
Listing 5-8.
Creating the DWPubsSales Tables
USE [DWPubsSales]
GO
/****** Create the Dimension Tables ******/
