/*
Create sample database to test TRUNCATE TABLE Statements
on tables with vaious FOREIGN KEY Configurations

No Foreigne Keys
Self Referencing
Parent Table 
Two Child only tables

*/

USE master
GO
-------------------------------------------------------
-- Drop Create Database
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'TestSQL')
 DROP DATABASE TestSQL
GO
CREATE DATABASE TestSQL
GO


-------------------------------------------------------
--  Create Table with no Foreign Keys
USE TestSQL
GO
IF  EXISTS (SELECT * FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[Loner]') AND type in (N'U'))
    DROP TABLE [dbo].[Loner]
GO
CREATE Table [dbo].[Loner] (
	Loner_PK_id INT NOT NULL,
	Added_dt DATETIME  DEFAULT (getdate())
    CONSTRAINT PK_Loner PRIMARY KEY (Loner_PK_id) )
GO

-------------------------------------------------------
-- Populate Loner, can be repeated to create additional rows
Declare @MaxID INT; SELECT @MaxID = ISNULL(MAX(Loner_PK_id),0) FROM [dbo].[Loner];
INSERT INTO [dbo].[Loner] (Loner_PK_id)
(
SELECT (ROW_NUMBER() OVER(ORDER BY a.name) + @MaxID) AS 'Loner_PK_id'
FROM sys.objects a, sys.objects b
)
GO


-------------------------------------------------------
--  Create Self referencing table
USE TestSQL
GO
IF  EXISTS (SELECT * FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[SelfRefTable]') AND type in (N'U'))
    DROP TABLE [dbo].[SelfRefTable]
GO
CREATE Table [dbo].[SelfRefTable] (
	Parent_PK_id  INT NOT NULL,
	FK_Parent_PK_id INT NULL,
	Added_dt DATETIME  DEFAULT (getdate())
    CONSTRAINT PK_SelfRefTable PRIMARY KEY (Parent_PK_id) )
GO
ALTER TABLE [dbo].[SelfRefTable]  WITH CHECK ADD  CONSTRAINT [FK_SelfRefTable_SelfRefTable] 
FOREIGN KEY([FK_Parent_PK_id])
REFERENCES [dbo].[SelfRefTable] ([Parent_PK_id])
GO

-------------------------------------------------------
-- Populate Self Referencing Table, can be repeated for more rows
Declare @MaxID INT; SELECT @MaxID = ISNULL(MAX(Parent_PK_id),0) FROM [dbo].[SelfRefTable];
INSERT INTO [dbo].[SelfRefTable] (Parent_PK_id)
(
SELECT (ROW_NUMBER() OVER(ORDER BY a.name) + @MaxID) AS 'Parent_PK_id'
FROM sys.objects a, sys.objects b
)
-- SET Foreign Key Column
UPDATE [dbo].[SelfRefTable] 
   SET FK_Parent_PK_id = (Parent_PK_id/2) 
   FROM [dbo].[SelfRefTable] pt
   WHERE pt.Parent_PK_id > 1
GO

/*
SELECT * -- Confirm only one row with NULL parent id key
  FROM [dbo].[SelfRefTable] 
 WHERE FK_Parent_PK_id IS NULL
*/ 


-----------------------------------------------------
-- Create Parent and Childed tables with foreign keys
USE TestSQL
GO
IF  EXISTS (SELECT * FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[ChildTable2]') AND type in (N'U'))
    DROP TABLE [dbo].[ChildTable2]
GO
IF  EXISTS (SELECT * FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[ChildTable1]') AND type in (N'U'))
    DROP TABLE [dbo].[ChildTable1]
GO
IF  EXISTS (SELECT * FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[ParentTable]') AND type in (N'U'))
    DROP TABLE [dbo].[ParentTable]
GO
CREATE Table [dbo].[ParentTable] (
	Parent_PK_id INT NOT NULL,
	Added_dt DATETIME  DEFAULT (getdate())
    CONSTRAINT PK_ParentTable PRIMARY KEY (Parent_PK_id) )
GO


IF  EXISTS (SELECT * FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[ChildTable1]') AND type in (N'U'))
    DROP TABLE [dbo].[ChildTable1]
GO
CREATE Table [dbo].[ChildTable1] (
	[Child_PK_id] [int] IDENTITY(1,1) NOT NULL,
	Parent_PK_id INT NOT NULL,
	Added_dt DATETIME  DEFAULT (getdate())
    CONSTRAINT PK_ChildTable1 PRIMARY KEY (Child_PK_id) )
GO
ALTER TABLE [dbo].[ChildTable1]  WITH CHECK ADD  CONSTRAINT [FK_ChildTable1_ParentTable] FOREIGN KEY([Parent_PK_id])
REFERENCES [dbo].[ParentTable] ([Parent_PK_id])
GO


IF  EXISTS (SELECT * FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[ChildTable2]') AND type in (N'U'))
    DROP TABLE [dbo].[ChildTable2]
GO
CREATE Table [dbo].[ChildTable2]
(
	[Child_PK_id] [int] IDENTITY(1,1) NOT NULL,
	Parent_PK_id INT NOT NULL,
	Added_dt DATETIME  DEFAULT (getdate())
    CONSTRAINT PK_ChildTable2 PRIMARY KEY (Child_PK_id)
)
GO
ALTER TABLE [dbo].[ChildTable2]  WITH CHECK ADD  CONSTRAINT [FK_ChildTable2_ParentTable] FOREIGN KEY([Parent_PK_id])
REFERENCES [dbo].[ParentTable] ([Parent_PK_id])
GO
ALTER TABLE [dbo].[ChildTable2] CHECK CONSTRAINT [FK_ChildTable2_ParentTable]
GO

-------------------------------------------------------
-- Populate ParentTable, can be repeated for more rows
Declare @MaxID INT; SELECT @MaxID = ISNULL(MAX(Parent_PK_id),0) FROM [dbo].[ParentTable];
INSERT INTO [dbo].[ParentTable] (Parent_PK_id)
(
SELECT (ROW_NUMBER() OVER(ORDER BY a.name) + @MaxID) AS 'Parent_PK_id'
FROM sys.objects a, sys.objects b
)

-------------------------------------------------------
-- Populate [ChildTable1], can be repeated for more rows
INSERT INTO [dbo].[ChildTable1] (Parent_PK_id ) (
 SELECT a.Parent_PK_id
 FROM [dbo].[ParentTable] a
 WHERE (a.Parent_PK_id %2) = 0
 GROUP BY a.Parent_PK_id  ) 

INSERT INTO [dbo].[ChildTable1] (Parent_PK_id ) (
 SELECT a.Parent_PK_id
 FROM [dbo].[ParentTable] a
 WHERE (a.Parent_PK_id %7) = 0
 GROUP BY a.Parent_PK_id  ) 

INSERT INTO [dbo].[ChildTable1] (Parent_PK_id ) (
 SELECT a.Parent_PK_id
 FROM [dbo].[ParentTable] a
 WHERE (a.Parent_PK_id %13) = 0
 GROUP BY a.Parent_PK_id  ) 
 
-------------------------------------------------------
-- Populate [ChildTable2], can be repeated for more rows
INSERT INTO [dbo].[ChildTable2] (Parent_PK_id ) (
 SELECT a.Parent_PK_id
 FROM [dbo].[ParentTable] a
 WHERE (a.Parent_PK_id %4) = 0
 GROUP BY a.Parent_PK_id  ) 
 GO