Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save StephanMoeller/7102a47ed1c8c3d184dc359f82909dfd to your computer and use it in GitHub Desktop.
Save StephanMoeller/7102a47ed1c8c3d184dc359f82909dfd to your computer and use it in GitHub Desktop.
-- Create a table with a ref to itself
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RefToSelf] [int] NULL,
[SomeText] [nvarchar](100) NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestTable] WITH CHECK ADD CONSTRAINT [FK_TestTable_TestTable] FOREIGN KEY([RefToSelf])
REFERENCES [dbo].[TestTable] ([Id])
GO
ALTER TABLE [dbo].[TestTable] CHECK CONSTRAINT [FK_TestTable_TestTable]
GO
-- Now insert some initial data
TRUNCATE TABLE TestTable
INSERT INTO TestTable(SomeText, RefToSelf) VALUES('Inserted', NULL)
-- Validate data
SELECT 'Now selecting content, expecting "inserted" to be the text'
SELECT * FROM TestTable
-- Run a transaction which one would expect to be rolled back as a transaction signals 'all-or-nothing'
BEGIN TRAN
UPDATE TestTable SET SomeText = 'All updated text (only one row)'
INSERT INTO TestTable(SomeText, RefToSelf) VALUES('New inserted with invalid ref', -1)
COMMIT TRAN
-- Now select the content
SELECT 'Now selecting content, expecting "inserted" to be the text'
SELECT * FROM TestTable
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment