So I worked out how to, in a TSQL batch script, identity 1:? and M:N relationships.
If any SQL gurus out there can help streamline it or identify problems with my logic I would appreciate it.
DDL for test database
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[DataTypes]'
GO
CREATE TABLE [dbo].[DataTypes]
(
[imageField] [image] NULL,
[binaryField] [binary] (50) NULL,
[varbinaryField] [varbinary] (50) NULL,
[timestampField] [timestamp] NULL,
[textField] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_DataTypes_textField] DEFAULT ('a'),
[ntextField] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_DataTypes_ntextField] DEFAULT ('a'),
[charField] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_DataTypes_charField] DEFAULT ('a'),
[ncharField] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_DataTypes_ncharField] DEFAULT ('a'),
[varcharField] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_DataTypes_varcharField] DEFAULT ('a'),
[nvarcharField] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_DataTypes_nvarcharField] DEFAULT ('a'),
[xmlField] [xml] NULL,
[uniqueidentifierField] [uniqueidentifier] NULL CONSTRAINT [DF_DataTypes_uniqueidentifierField] DEFAULT (newid()),
[dateField] [date] NULL CONSTRAINT [DF_DataTypes_dateField] DEFAULT (getdate()),
[timeField] [time] NULL,
[datetime2Field] [datetime2] NULL,
[datetimeoffsetField] [datetimeoffset] NULL,
[tinyintField] [tinyint] NULL CONSTRAINT [DF_DataTypes_tinyintField] DEFAULT ((1)),
[smallintField] [smallint] NULL CONSTRAINT [DF_DataTypes_smallintField] DEFAULT ((1)),
[intField] [int] NULL CONSTRAINT [DF_DataTypes_intField] DEFAULT ((1)),
[smalldatetimeField] [smalldatetime] NULL,
[realField] [real] NULL CONSTRAINT [DF_DataTypes_realField] DEFAULT ((1)),
[moneyField] [money] NULL CONSTRAINT [DF_DataTypes_moneyField] DEFAULT ((1)),
[datetimeField] [datetime] NULL CONSTRAINT [DF_DataTypes_datetimeField] DEFAULT (getdate()),
[floatField] [float] NULL CONSTRAINT [DF_DataTypes_floatField] DEFAULT ((1)),
[sql_variantField] [sql_variant] NULL,
[bitField] [bit] NULL CONSTRAINT [DF_DataTypes_bitField] DEFAULT ((1)),
[decimalField] [decimal] (18, 0) NULL CONSTRAINT [DF_DataTypes_decimalField] DEFAULT ((1)),
[numericField] [numeric] (18, 0) NULL CONSTRAINT [DF_DataTypes_numericField] DEFAULT ((1)),
[smallmoneyField] [smallmoney] NULL CONSTRAINT [DF_DataTypes_smallmoneyField] DEFAULT ((1)),
[bigintField] [bigint] NULL CONSTRAINT [DF_DataTypes_bigintField] DEFAULT ((1)),
[hierarchyidField] [sys].[hierarchyid] NULL,
[geometryField] [sys].[geometry] NULL,
[geographyField] [sys].[geography] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[M2MParentA]'
GO
CREATE TABLE [dbo].[M2MParentA]
(
[M2MParentAId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_M2MParentA] on [dbo].[M2MParentA]'
GO
ALTER TABLE [dbo].[M2MParentA] ADD CONSTRAINT [PK_M2MParentA] PRIMARY KEY CLUSTERED ([M2MParentAId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[M2MInter]'
GO
CREATE TABLE [dbo].[M2MInter]
(
[M2MParentAId] [int] NOT NULL,
[M2MParentBId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_M2MInter] on [dbo].[M2MInter]'
GO
ALTER TABLE [dbo].[M2MInter] ADD CONSTRAINT [PK_M2MInter] PRIMARY KEY CLUSTERED ([M2MParentAId], [M2MParentBId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[M2MParentB]'
GO
CREATE TABLE [dbo].[M2MParentB]
(
[M2MParentBId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_M2MParentB] on [dbo].[M2MParentB]'
GO
ALTER TABLE [dbo].[M2MParentB] ADD CONSTRAINT [PK_M2MParentB] PRIMARY KEY CLUSTERED ([M2MParentBId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[M2MMChildA]'
GO
CREATE TABLE [dbo].[M2MMChildA]
(
[M2MMChildAId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_M2MMChildA] on [dbo].[M2MMChildA]'
GO
ALTER TABLE [dbo].[M2MMChildA] ADD CONSTRAINT [PK_M2MMChildA] PRIMARY KEY CLUSTERED ([M2MMChildAId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[M2MMInter]'
GO
CREATE TABLE [dbo].[M2MMInter]
(
[M2MMParentAId] [int] NOT NULL,
[M2MMChildId] [int] NOT NULL,
[SomeData] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_M2MMInter] on [dbo].[M2MMInter]'
GO
ALTER TABLE [dbo].[M2MMInter] ADD CONSTRAINT [PK_M2MMInter] PRIMARY KEY CLUSTERED ([M2MMParentAId], [M2MMChildId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[M2MMChildB]'
GO
CREATE TABLE [dbo].[M2MMChildB]
(
[M2MMChildBId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_M2MMChildB] on [dbo].[M2MMChildB]'
GO
ALTER TABLE [dbo].[M2MMChildB] ADD CONSTRAINT [PK_M2MMChildB] PRIMARY KEY CLUSTERED ([M2MMChildBId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[M2MMParentA]'
GO
CREATE TABLE [dbo].[M2MMParentA]
(
[M2MMParentAId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_M2MMParentA] on [dbo].[M2MMParentA]'
GO
ALTER TABLE [dbo].[M2MMParentA] ADD CONSTRAINT [PK_M2MMParentA] PRIMARY KEY CLUSTERED ([M2MMParentAId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[M2MUQParentA]'
GO
CREATE TABLE [dbo].[M2MUQParentA]
(
[M2MUQParentAId] [int] NOT NULL,
[M2MUQParentASKey] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_M2MUQParentA] on [dbo].[M2MUQParentA]'
GO
ALTER TABLE [dbo].[M2MUQParentA] ADD CONSTRAINT [PK_M2MUQParentA] PRIMARY KEY CLUSTERED ([M2MUQParentAId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [IX_M2MUQParentA_M2MUQParentASKey] on [dbo].[M2MUQParentA]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_M2MUQParentA_M2MUQParentASKey] ON [dbo].[M2MUQParentA] ([M2MUQParentASKey])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[M2MUQInter]'
GO
CREATE TABLE [dbo].[M2MUQInter]
(
[M2MUQParentASKey] [int] NOT NULL,
[M2MUQParentBSKey] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_M2MUQInter] on [dbo].[M2MUQInter]'
GO
ALTER TABLE [dbo].[M2MUQInter] ADD CONSTRAINT [PK_M2MUQInter] PRIMARY KEY CLUSTERED ([M2MUQParentASKey], [M2MUQParentBSKey])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[M2MUQParentB]'
GO
CREATE TABLE [dbo].[M2MUQParentB]
(
[M2MUQParentBId] [int] NOT NULL,
[M2MUQParentBSKey] [int] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_M2MUQParentB] on [dbo].[M2MUQParentB]'
GO
ALTER TABLE [dbo].[M2MUQParentB] ADD CONSTRAINT [PK_M2MUQParentB] PRIMARY KEY CLUSTERED ([M2MUQParentBId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating index [IX_M2MUQParentB_M2MUQParentBSKey] on [dbo].[M2MUQParentB]'
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_M2MUQParentB_M2MUQParentBSKey] ON [dbo].[M2MUQParentB] ([M2MUQParentBSKey])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[M2OChild]'
GO
CREATE TABLE [dbo].[M2OChild]
(
[M2OChildId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_M2OChild] on [dbo].[M2OChild]'
GO
ALTER TABLE [dbo].[M2OChild] ADD CONSTRAINT [PK_M2OChild] PRIMARY KEY CLUSTERED ([M2OChildId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[M2OParent]'
GO
CREATE TABLE [dbo].[M2OParent]
(
[M2OParentId] [int] NOT NULL,
[M2OChildId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_M2OParent] on [dbo].[M2OParent]'
GO
ALTER TABLE [dbo].[M2OParent] ADD CONSTRAINT [PK_M2OParent] PRIMARY KEY CLUSTERED ([M2OParentId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[O2MParent]'
GO
CREATE TABLE [dbo].[O2MParent]
(
[O2MParentId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_O2MParent_1] on [dbo].[O2MParent]'
GO
ALTER TABLE [dbo].[O2MParent] ADD CONSTRAINT [PK_O2MParent_1] PRIMARY KEY CLUSTERED ([O2MParentId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[O2MChild]'
GO
CREATE TABLE [dbo].[O2MChild]
(
[O2MChildId] [int] NOT NULL,
[O2MParentId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_O2MChild_1] on [dbo].[O2MChild]'
GO
ALTER TABLE [dbo].[O2MChild] ADD CONSTRAINT [PK_O2MChild_1] PRIMARY KEY CLUSTERED ([O2MChildId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[O2ONFKParent]'
GO
CREATE TABLE [dbo].[O2ONFKParent]
(
[O2ONFKParentId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_O2ONFKParent] on [dbo].[O2ONFKParent]'
GO
ALTER TABLE [dbo].[O2ONFKParent] ADD CONSTRAINT [PK_O2ONFKParent] PRIMARY KEY CLUSTERED ([O2ONFKParentId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[O2ONFKChild]'
GO
CREATE TABLE [dbo].[O2ONFKChild]
(
[O2ONFKChildId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_O2ONFKChild] on [dbo].[O2ONFKChild]'
GO
ALTER TABLE [dbo].[O2ONFKChild] ADD CONSTRAINT [PK_O2ONFKChild] PRIMARY KEY CLUSTERED ([O2ONFKChildId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[O2ONUQParent]'
GO
CREATE TABLE [dbo].[O2ONUQParent]
(
[O2ONUQParentId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_O2ONUQParent] on [dbo].[O2ONUQParent]'
GO
ALTER TABLE [dbo].[O2ONUQParent] ADD CONSTRAINT [PK_O2ONUQParent] PRIMARY KEY CLUSTERED ([O2ONUQParentId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[O2ONUQChild]'
GO
CREATE TABLE [dbo].[O2ONUQChild]
(
[O2ONUQChildId] [int] NOT NULL,
[O2ONUQParentId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_O2ONUQChild] on [dbo].[O2ONUQChild]'
GO
ALTER TABLE [dbo].[O2ONUQChild] ADD CONSTRAINT [PK_O2ONUQChild] PRIMARY KEY CLUSTERED ([O2ONUQChildId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[O2OPKChild]'
GO
CREATE TABLE [dbo].[O2OPKChild]
(
[O2OPKChildId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_O2OPKChild] on [dbo].[O2OPKChild]'
GO
ALTER TABLE [dbo].[O2OPKChild] ADD CONSTRAINT [PK_O2OPKChild] PRIMARY KEY CLUSTERED ([O2OPKChildId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[O2OPKParent]'
GO
CREATE TABLE [dbo].[O2OPKParent]
(
[O2OPKParentId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_O2OPKParent] on [dbo].[O2OPKParent]'
GO
ALTER TABLE [dbo].[O2OPKParent] ADD CONSTRAINT [PK_O2OPKParent] PRIMARY KEY CLUSTERED ([O2OPKParentId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[ControlParent]'
GO
CREATE TABLE [dbo].[ControlParent]
(
[ControlParentId] [int] NOT NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_ControlParent] on [dbo].[ControlParent]'
GO
ALTER TABLE [dbo].[ControlParent] ADD CONSTRAINT [PK_ControlParent] PRIMARY KEY CLUSTERED ([ControlParentId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding constraints to [dbo].[O2ONUQChild]'
GO
ALTER TABLE [dbo].[O2ONUQChild] ADD CONSTRAINT [IX_O2ONUQChild_O2ONUQ] UNIQUE NONCLUSTERED ([O2ONUQParentId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[M2MInter]'
GO
ALTER TABLE [dbo].[M2MInter] ADD
CONSTRAINT [FK_M2MInter_M2MParentA] FOREIGN KEY ([M2MParentAId]) REFERENCES [dbo].[M2MParentA] ([M2MParentAId]),
CONSTRAINT [FK_M2MInter_M2MParentB] FOREIGN KEY ([M2MParentBId]) REFERENCES [dbo].[M2MParentB] ([M2MParentBId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[M2MMInter]'
GO
ALTER TABLE [dbo].[M2MMInter] ADD
CONSTRAINT [FK_M2MMInter_M2MMChildA] FOREIGN KEY ([M2MMChildId]) REFERENCES [dbo].[M2MMChildA] ([M2MMChildAId]),
CONSTRAINT [FK_M2MMInter_M2MMChildB] FOREIGN KEY ([M2MMChildId]) REFERENCES [dbo].[M2MMChildB] ([M2MMChildBId]),
CONSTRAINT [FK_M2MMInter_M2MMParentA] FOREIGN KEY ([M2MMParentAId]) REFERENCES [dbo].[M2MMParentA] ([M2MMParentAId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[M2MUQInter]'
GO
ALTER TABLE [dbo].[M2MUQInter] ADD
CONSTRAINT [FK_M2MUQInter_M2MUQParentA] FOREIGN KEY ([M2MUQParentASKey]) REFERENCES [dbo].[M2MUQParentA] ([M2MUQParentAId]),
CONSTRAINT [FK_M2MUQInter_M2MUQParentB] FOREIGN KEY ([M2MUQParentBSKey]) REFERENCES [dbo].[M2MUQParentB] ([M2MUQParentBId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[M2OParent]'
GO
ALTER TABLE [dbo].[M2OParent] ADD
CONSTRAINT [FK_M2OParent_M2OChild] FOREIGN KEY ([M2OChildId]) REFERENCES [dbo].[M2OChild] ([M2OChildId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[O2MChild]'
GO
ALTER TABLE [dbo].[O2MChild] ADD
CONSTRAINT [FK_O2MChild_O2MParent] FOREIGN KEY ([O2MParentId]) REFERENCES [dbo].[O2MParent] ([O2MParentId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[O2ONFKChild]'
GO
ALTER TABLE [dbo].[O2ONFKChild] ADD
CONSTRAINT [FK_O2ONFKChild_O2ONFKParent] FOREIGN KEY ([O2ONFKChildId]) REFERENCES [dbo].[O2ONFKParent] ([O2ONFKParentId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[O2ONUQChild]'
GO
ALTER TABLE [dbo].[O2ONUQChild] ADD
CONSTRAINT [FK_O2ONUQChild_O2ONUQParent] FOREIGN KEY ([O2ONUQParentId]) REFERENCES [dbo].[O2ONUQParent] ([O2ONUQParentId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Adding foreign keys to [dbo].[O2OPKParent]'
GO
ALTER TABLE [dbo].[O2OPKParent] ADD
CONSTRAINT [FK_O2OPKParent_O2OPKChild] FOREIGN KEY ([O2OPKParentId]) REFERENCES [dbo].[O2OPKChild] ([O2OPKChildId])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
Script
/*
Will identify immediate 1:? and m:n relationships
*/
-- TODO: puzzle: work out the set-based equivalent
SET NOCOUNT ON
DECLARE @keys TABLE
--CREATE TABLE #keys
(
CONSTRAINT_SCHEMA NVARCHAR(128),
CONSTRAINT_NAME NVARCHAR(128),
TABLE_SCHEMA NVARCHAR(128),
TABLE_NAME NVARCHAR(128),
COLUMN_NAME NVARCHAR(128),
ORDINAL_POSITION INT,
CONSTRAINT_TYPE NVARCHAR(11),
UNIQUE_CONSTRAINT_SCHEMA NVARCHAR(128),
UNIQUE_CONSTRAINT_NAME NVARCHAR(128),
UNIQUE_TABLE_SCHEMA NVARCHAR(128),
UNIQUE_TABLE_NAME NVARCHAR(128),
UNIQUE_COLUMN_NAME NVARCHAR(128),
UNIQUE_ORDINAL_POSITION INT,
UNIQUE_CONSTRAINT_TYPE NVARCHAR(11)
)
BEGIN -- Get a table full of PK and UQ columns
DECLARE @unique_key_columns TABLE
(
-- contains PK and UQ indexes
[schema_name] NVARCHAR(128),
table_name NVARCHAR(128),
index_name NVARCHAR(128),
column_id INT,
column_name NVARCHAR(128),
is_primary_key BIT,
is_unique_constraint BIT,
is_unique BIT
)
INSERT INTO @unique_key_columns
(
[schema_name],
table_name,
index_name,
column_id,
column_name,
is_primary_key,
is_unique_constraint,
is_unique
)
-- selects PK and UQ indexes
SELECT S.name AS [schema_name],
T.name AS table_name,
IX.name AS index_name,
IC.column_id,
C.name AS column_name,
IX.is_primary_key,
IX.is_unique_constraint,
IX.is_unique
FROM sys.tables AS T
INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
INNER JOIN sys.indexes AS IX ON T.object_id = IX.object_id
INNER JOIN sys.index_columns AS IC ON IX.object_id = IC.object_id
AND IX.index_id = IC.index_id
INNER JOIN sys.columns AS C ON IC.column_id = C.column_id
AND IC.object_id = C.object_id
WHERE ( IX.is_unique = 1 )
AND ( T.name <> 'sysdiagrams' )
AND IX.is_unique = 1
ORDER BY schema_name,
table_name,
index_name,
C.column_id
END
BEGIN -- Get a table full of FK columns
DECLARE @foreign_key_columns TABLE
(
constraint_name NVARCHAR(128),
base_schema_name NVARCHAR(128),
base_table_name NVARCHAR(128),
base_column_id INT,
base_column_name NVARCHAR(128),
unique_schema_name NVARCHAR(128),
unique_table_name NVARCHAR(128),
unique_column_id INT,
unique_column_name NVARCHAR(128)
)
INSERT INTO @foreign_key_columns
(
constraint_name,
base_schema_name,
base_table_name,
base_column_id,
base_column_name,
unique_schema_name,
unique_table_name,
unique_column_id,
unique_column_name
)
SELECT FK.name AS constraint_name,
S.name AS base_schema_name,
T.name AS base_table_name,
C.column_id AS base_column_id,
C.name AS base_column_name,
US.name AS unique_schema_name,
UT.name AS unique_table_name,
UC.column_id AS unique_column_id,
UC.name AS unique_column_name
FROM sys.tables AS T
INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
INNER JOIN sys.foreign_keys AS FK ON T.object_id = FK.parent_object_id
INNER JOIN sys.foreign_key_columns AS FKC ON FK.object_id = FKC.constraint_object_id
INNER JOIN sys.columns AS C ON FKC.parent_object_id = C.object_id
AND FKC.parent_column_id = C.column_id
INNER JOIN sys.columns AS UC ON FKC.referenced_object_id = UC.object_id
AND FKC.referenced_column_id = UC.column_id
INNER JOIN sys.tables AS UT ON FKC.referenced_object_id = UT.object_id
INNER JOIN sys.schemas AS US ON UT.schema_id = US.schema_id
WHERE ( T.name <> 'sysdiagrams' )
ORDER BY base_schema_name,
base_table_name
END
DECLARE @constraint_name NVARCHAR(128),
@base_schema_name NVARCHAR(128),
@base_table_name NVARCHAR(128),
@unique_schema_name NVARCHAR(128),
@unique_table_name NVARCHAR(128)
-- The foreign key side of the constraint is always singular, we need to check from the perspective
-- of the unique side of the constraint.
-- for each FK constraint in DB
DECLARE tmpC CURSOR READ_ONLY
FOR SELECT DISTINCT
constraint_name,
base_schema_name,
base_table_name,
unique_schema_name,
unique_table_name
FROM @foreign_key_columns
OPEN tmpC
FETCH NEXT FROM tmpC INTO @constraint_name, @base_schema_name, @base_table_name, @unique_schema_name, @unique_table_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- get the columns in the base side of the FK constraint
DECLARE @fkc TABLE
(
column_name NVARCHAR(128)
)
DELETE FROM @fkc
INSERT INTO @fkc ( column_name )
SELECT base_column_name
FROM @foreign_key_columns
WHERE constraint_name = @constraint_name
-- check for one to one/none
-- If the base side columns of the constraint fit into any one of the base side tables unique constraints
-- AND the column count is the same then we have a one-to-one/none and should be realized as a singular
-- object reference
-- I realize that if the base side unique constraint has more columns than the unique side unique constraint
-- AND all of those columns DO represent a 1:? that would actually qualify but it seems like an edge case and
-- beyond the scope of this question.
DECLARE @uk_schema_name NVARCHAR(128),
@uk_table_name NVARCHAR(128),
@uk_index_name NVARCHAR(128),
@is_may_have_a BIT,
@many_to_many_fk_schema NVARCHAR(128),
@many_to_many_fk_name NVARCHAR(128),
@many_to_many_fk_table_schema NVARCHAR(128),
@many_to_many_fk_table_name NVARCHAR(128)
SET @is_may_have_a = 0
-- have to open another cursor over the unique keys of the base table - i want
-- a distinct list of unique constraints for the base table
DECLARE cKey CURSOR READ_ONLY
FOR SELECT DISTINCT
[schema_name],
table_name,
index_name
FROM @unique_key_columns
WHERE [schema_name] = @base_schema_name
AND table_name = @base_table_name
OPEN cKey
FETCH NEXT FROM cKey INTO @uk_schema_name, @uk_table_name, @uk_index_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- get the unique constraint columns
DECLARE @pkc TABLE
(
column_name NVARCHAR(128)
)
DELETE FROM @pkc
INSERT INTO @pkc ( column_name )
SELECT column_name
FROM @unique_key_columns
WHERE [schema_name] = @uk_schema_name
AND table_name = @uk_table_name
AND index_name = @uk_index_name
-- if count is same and columns are same
DECLARE @count1 INT,
@count2 INT
SELECT @count2 = COUNT(*)
FROM @pkc
-- select all from both on name and exclude mismatches
SELECT @count1 = COUNT(*)
FROM @fkc F
FULL OUTER JOIN @pkc P ON f.column_name = p.column_name
WHERE NOT p.column_name IS NULL
AND NOT f.column_name IS NULL
IF @count1 = @count2
BEGIN
-- the base side of the fk constraint corresponds exactly to
-- at least on unique constraint making it effectively 1:?
SET @is_may_have_a = 1
BREAK
END
-- not 1:? - check for many-to-many
-- if the base side of the FK constraint fits into ONE of the base table's unique constraints but the unique constraint
-- has more columns then it is a many-to-one (from the perspective of the unique side) and may be many-to-many relationship
-- that needs to be surfaced and the reference will be plural in both cases (e.g. intermediate records or related records)
-- if the base side table consists solely of keys then it is definitely a many-to-many.
-- if the base side has data fields as well as other FK constraints then it should be surfaced along with the related table.
-- given the above lets first check to see if the FK will fit into the UT PK
SELECT @count1 = COUNT(*)
FROM @pkc P
full OUTER JOIN @fkc F ON f.column_name = p.column_name
WHERE NOT p.column_name IS NULL
IF @count1 != 0
BEGIN -- the fk fits into the pk
-- get the pk columns that are not in the fk and look for another fk
DECLARE @opkc TABLE
(
column_name NVARCHAR(128)
)
DELETE FROM @opkc
INSERT INTO @opkc ( column_name )
SELECT p.column_name
FROM @pkc P
full OUTER JOIN @fkc F ON f.column_name = p.column_name
WHERE f.column_name IS NULL
-- find the fk constraints that contains the columns in @opkc and render a collection property for each
DECLARE @mtmfk TABLE( many_to_many_fk_schema NVARCHAR(128),
many_to_many_fk_name NVARCHAR(128),
many_to_many_fk_table_schema NVARCHAR(128),
many_to_many_fk_table_name NVARCHAR(128)
)
DELETE FROM @mtmfk
INSERT INTO @mtmfk (many_to_many_fk_schema ,many_to_many_fk_name ,many_to_many_fk_table_schema ,many_to_many_fk_table_name )
SELECT DISTINCT fkc.base_schema_name,constraint_name,fkc.unique_schema_name,fkc.unique_table_name
FROM @foreign_key_columns fkc FULL OUTER JOIN @opkc o ON fkc.base_column_name = o.column_name
WHERE fkc.base_schema_name=@base_schema_name AND fkc.base_table_name = @base_table_name AND NOT o.column_name IS NULL
SELECT @count1 = COUNT(*) FROM @mtmfk
IF @count1 !=0
BEGIN
DECLARE cManyToManyFK CURSOR READ_ONLY FOR
SELECT many_to_many_fk_schema ,many_to_many_fk_name ,many_to_many_fk_table_schema ,many_to_many_fk_table_name FROM @mtmfk
OPEN cManyToManyFK
FETCH NEXT FROM cManyToManyFK INTO @many_to_many_fk_schema ,@many_to_many_fk_name ,@many_to_many_fk_table_schema ,@many_to_many_fk_table_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- is many to many -
-- TODO: check to see if the intermediate table is all keys, if not, it should be surfaced as many to one as well
-- for now just surface it
PRINT 'for ' + @unique_schema_name + '.' + @unique_table_name + ' constraint ' + +@constraint_name + ' is m:n with ' + @many_to_many_fk_table_name
FETCH NEXT FROM cManyToManyFK INTO @many_to_many_fk_schema ,@many_to_many_fk_name ,@many_to_many_fk_table_schema ,@many_to_many_fk_table_name
END
CLOSE cManyToManyFK
DEALLOCATE cManyToManyFK
-- SELECT @many_to_many_fk_schema=NULL,@many_to_many_fk_name =NULL, @many_to_many_fk_table_schema =NULL,@many_to_many_fk_table_name=NULL
-- run a cursor over the qualifying fk constraints and render a collection property. in most cases there will be only one.
BREAK
END
END
FETCH NEXT FROM cKey INTO @uk_schema_name, @uk_table_name, @uk_index_name
END
CLOSE cKey
DEALLOCATE cKey
IF @is_may_have_a = 1
PRINT 'for ' + @unique_schema_name + '.' + @unique_table_name + ' constraint ' + +@constraint_name + ' is 1:? '
FETCH NEXT FROM tmpC INTO @constraint_name, @base_schema_name, @base_table_name, @unique_schema_name, @unique_table_name
END
CLOSE tmpC
DEALLOCATE tmpC
Technorati tags:
SQL