When visiting clients, I often find that one or more databases have a table (or several) containing metadata. Most often, these tables have only a single row of data containing metadata about the company, the application, or the database itself. Quite likely, there should only be a single row of data in these metadata tables.
Sometimes, I find an INSERT TRIGGER employed to make sure that another row of data is not accidentally added to the metadata table. The TRIGGER may count the rows in the table, or it may call a function that counts the rows in the table. I've even discovered SQL Agent jobs running on some schedule to remove accidentally inserted rows.
By far, the simplest solution is this:
- Add a column with an integer IDENTITY value.
- Set a table constraint where the integer IDENTITY value column has to be greater than zero, and less than 2.
The code example below demonstrates the simplicity of this approach.
-- Use a scratch pad, don't make permanent database changes for a test
USE tempdb;
GO
-- Create a table with an IDENTITY column and a CHECK constraint
CREATE TABLE [dbo].[MetaDataTable]
( [RowId] [INT] IDENTITY(1, 1) NOT NULL
CHECK (([RowId]>(0) AND [RowId]<(2))),
[MetaData1] [NVARCHAR](15) NOT NULL,
[MetaData2] [NVARCHAR](15) NULL
);
GO
-- Add the First (and should be ONLY) row of metadata
INSERT INTO [dbo].[MetaDataTable]
VALUES ('Test, Row 1', 'Some data');
-- Verify the data
SELECT *
FROM [dbo].[MetaDataTable];
-- Try adding a second row of metadata
INSERT INTO [dbo].[MetaDataTable]
VALUES ('Test, Row 2', 'Some other data');
-- Error 547, constraint violation
-- Clean up
DROP TABLE [dbo].[MetaDataTable];
A constraint acts quicker and with less impact than a trigger.