Points to Consider While Writing Queries - Microsoft SQL Server 2012 Performance Tuning - page 272

GO

select * into ProductModelDemo from Production.ProductModel
select * into ProductDemo from Production.Product WHERE
ProductModelID is not null
GO

2. After creating the child table, ProductDemo , and parent table, ProductModelDemo ,

let us make sure that there is no NULL value in the foreign key ProductModelID of
the ProductDemo table:
ALTER TABLE ProductDemo
ALTER COLUMN ProductModelID INT NOT NULL
GO

3. Now is the time to create a primary key constraint for the child table, ProductDemo :

ALTER TABLE ProductDemo ADD CONSTRAINT [PK_ProductDemo_ProductID]
PRIMARY KEY CLUSTERED
(
[ProductID] ASC
)
GO

4. Now, create a primary key constraint in the parent table, ProductModelDemo :

ALTER TABLE ProductModelDemo ADD CONSTRAINT [PK_ProductModelDemo_
ProductModelID] PRIMARY KEY CLUSTERED
(
ProductModelID ASC
)
GO

5. Add a foreign key to the child table with the WITH NOCHECK option. The WITH

NOCHECK option prevents the parser from checking the existence of existing data of
child field with parent field data. By default, the ALTER TABLE command uses the
WITH CHECK option if nothing is specified:
ALTER TABLE ProductDemo
WITH NOCHECK
ADD CONSTRAINT
FK_ProductDemo_ProductModelDemo_ProductModelID
FOREIGN KEY
(ProductModelID)
REFERENCES
ProductModelDemo(ProductModelID)
GO