clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805483 fiddles created (40812 in the last week).

-- Configurations CREATE TABLE dbo.Configs ( ConfigID integer NOT NULL, ConfigName varchar(100) NOT NULL, CONSTRAINT [PK dbo.Configs ConfigID] PRIMARY KEY CLUSTERED (ConfigID), -- Assuming configuration names are unique CONSTRAINT [UQ dbo.Configs ConfigName] UNIQUE NONCLUSTERED (ConfigName), );
 hidden batch(es)


-- Have multiple revisions CREATE TABLE dbo.Revisions ( RevisionID integer NOT NULL, ConfigID integer NOT NULL, CurrentRevisionID integer NOT NULL -- Revision is current if CurrentRevisionID = RevisionID -- Zero otherwise (see check constraints) DEFAULT 0, CreatedAt datetimeoffset NOT NULL DEFAULT SYSDATETIMEOFFSET(), RevisionDescription varchar(200) NULL, Foo integer NOT NULL, Bar bit NOT NULL, -- Convenient computed column, persisted or not IsCurrent AS CONVERT(bit, IIF(CurrentRevisionID = RevisionID, 'true', 'false')), IsDeployed bit NOT NULL DEFAULT CONVERT(bit, 'false'), CONSTRAINT [PK dbo.Revisions RevisionID] PRIMARY KEY CLUSTERED (RevisionID), CONSTRAINT [FK dbo.Revisions -> dbo.Configs ConfigID] FOREIGN KEY (ConfigID) REFERENCES dbo.Configs (ConfigID), CONSTRAINT [CK dbo.Revisions Valid CreatedAt] CHECK (CreatedAt <= SYSDATETIMEOFFSET()), -- RevisionID = 0 is a reserved value, must not be used CONSTRAINT [CK dbo.Revisions Valid RevisionID] CHECK (RevisionID != 0), -- CurrentRevisionID must be zero or match RevisionID CONSTRAINT [CK dbo.Revisions Valid CurrentRevisionID] CHECK (CurrentRevisionID IN (0, RevisionID)), -- A revision can only be deployed if it is current CONSTRAINT [CK dbo.Revisions Only Deployed If Current] CHECK (IsDeployed = 'false' OR (CurrentRevisionID = RevisionID AND IsDeployed = 'true')), -- For denormalization via FK to dbo.PublicNames CONSTRAINT [UQ dbo.Revisions RevisionID, CurrentRevisionID] UNIQUE NONCLUSTERED (RevisionID, CurrentRevisionID), -- Unique current revision per config INDEX [UQ dbo.Revisions One Current Revision Per Config] UNIQUE (ConfigID, IsCurrent) INCLUDE (CurrentRevisionID) WHERE CurrentRevisionID != 0 );
 hidden batch(es)


-- Configurations have multiple 'public names' CREATE TABLE dbo.PublicNames ( PublicNameID integer NOT NULL, RevisionID integer NOT NULL, CurrentRevisionID integer NOT NULL DEFAULT 0, PublicName varchar(100) NOT NULL, CONSTRAINT [PK dbo.PublicNames PublicNameID] PRIMARY KEY CLUSTERED (PublicNameID), CONSTRAINT [FK dbo.PublicNames -> dbo.Revisions RevisionID] FOREIGN KEY (RevisionID) REFERENCES dbo.Revisions (RevisionID), -- Denormalized, kept in sync via cascade CONSTRAINT [FK dbo.PublicNames -> dbo.Revisions RevisionID, CurrentRevisionID] FOREIGN KEY (RevisionID, CurrentRevisionID) REFERENCES dbo.Revisions (RevisionID, CurrentRevisionID) ON UPDATE CASCADE, -- Public names unique within a revision CONSTRAINT [UQ dbo.PublicNames PublicName, RevisionID] UNIQUE NONCLUSTERED (PublicName, RevisionID), -- To support foreign key INDEX [IX dbo.PublicNames RevisionID, CurrentRevisionID] NONCLUSTERED (RevisionID, CurrentRevisionID), -- Public names unique across all current revisions INDEX [UQ dbo.PublicNames Unique Current Public Names] UNIQUE NONCLUSTERED (PublicName) INCLUDE (CurrentRevisionID) WHERE CurrentRevisionID != 0 );
 hidden batch(es)


INSERT INTO dbo.Configs (ConfigID, ConfigName) VALUES (17, 'config_foo'), (42, 'config_bar'); INSERT INTO dbo.Revisions (RevisionID, ConfigID, CreatedAt, RevisionDescription, Foo, Bar) VALUES (11, 17, '2021-05-29 09:07:18', 'Foo configuration, first draft', 81, 'true'), (19, 17, '2021-05-29 10:42:17', 'Foo configuration, second draft', 73, 'true'), (23, 42, '2021-05-29 09:36:52', 'Bar configuration, first draft', 118, 'false'); INSERT INTO dbo.PublicNames (PublicNameID, RevisionID, PublicName) VALUES -- public names for foo configuration, first draft (83, 11, 'some.name'), (84, 11, 'other.name'), -- public names for foo configuration, second draft (85, 19, 'revised.name'), (86, 19, 'other.name'), (87, 19, 'third.name'), -- public names for bar configuration, first draft; -- some of the names here are the same used by foo configurations (88, 23, 'some.name'), (89, 23, 'unique.name'), (90, 23, 'other.name');
13 rows affected
 hidden batch(es)


-- Foo configuration has a current, published revision: UPDATE dbo.Revisions SET CurrentRevisionID = 19, IsDeployed = 'true' WHERE ConfigID = 17 AND RevisionID = 19;
1 rows affected
 hidden batch(es)


WITH NumberedRevisions AS ( SELECT R.*, Revision = ROW_NUMBER() OVER ( PARTITION BY R.ConfigID ORDER BY R.CreatedAt, R.RevisionID) FROM dbo.Revisions AS R ) SELECT C.ConfigName, R.Revision, R.IsDeployed, R.IsCurrent, Names = STRING_AGG(P.PublicName, ',') WITHIN GROUP ( ORDER BY P.PublicName) FROM dbo.Configs AS C JOIN NumberedRevisions AS R ON R.ConfigID = C.ConfigID JOIN dbo.PublicNames AS P ON P.RevisionID = R.RevisionID GROUP BY C.ConfigID, C.ConfigName, R.Revision, R.IsDeployed, R.IsCurrent ORDER BY C.ConfigID, Revision;
ConfigName Revision IsDeployed IsCurrent Names
config_foo 1 False False other.name,some.name
config_foo 2 True True other.name,revised.name,third.name
config_bar 1 False False other.name,some.name,unique.name
 hidden batch(es)


-- Attempt to set the third row current UPDATE dbo.Revisions SET CurrentRevisionID = 23 WHERE ConfigID = 42 AND RevisionID = 23;
Msg 2601 Level 14 State 1 Line 2 Cannot insert duplicate key row in object 'dbo.PublicNames' with unique index 'UQ dbo.PublicNames Unique Current Public Names'. The duplicate key value is (other.name). Msg 3621 Level 0 State 0 Line 2 The statement has been terminated.
 hidden batch(es)