THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Arnie Rowland

Discussion of issues related to SQL Server, the MSDN SQL Support Forums, the complex interplay between Developers and SQL Server Administrators, and our sometimes futile attempts to have a 'normal' life.

There should only be one ...

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.

Published Tuesday, November 16, 2010 1:07 AM by ArnieRowland

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Erik. said:

And in my experience, the table will ultimately grow wide, while all the usages of it still read the whole row, but utilize only a column or two.

If you're building from scratch, forget the dark magic and make a narrow table with key-value pairs to store the metadata.  When you're dealing with other apps that already rely on the 1-row idea, the 1-row table can become a 1-row view based on the backing data in the key-value pair table.

Is this not a more future-proof design?

November 16, 2010 5:41 AM
 

Mateus said:

really elegant solution

November 16, 2010 10:33 AM
 

Justin Dearing said:

Such a simple idea, but well executed and explained.

November 16, 2010 10:34 AM
 

Tom Wilson said:

1.  Why not just constrain RowId = 1 instead of >0 and < 2?

2.  RowId doesn't even have to be an identity - just give it a default value of 1.

November 16, 2010 12:07 PM
 

Tek4Dev said:

very helpful one

November 16, 2010 1:17 PM
 

AaronBertrand said:

I agree with Erik, I prefer EAV (a properties table) to a magic table with one row.  When we get global variables in SQL Server, this will be much easier - look ma, there is no spoon.

November 16, 2010 6:07 PM
 

drsql said:

So having a single table with a single row is an interesting solution, and I guess the single row answer is equally interesting. I suppose it really does depend on how you use this table.  If it is a table used like global persisted variables (particularly ones that the user can add to), then EAV and (perhaps) a function would seem like the best direction (though clearly caution with the function is warranted (don't use it in a WHERE clause, for instance).  This solution kind of removes it from the model per ce and elevates it to a general bucket of metadata.

But the single row solution seems like the best way to go if the attributes share a common purpose. Like if it is company metadata, where you only have a single company.  Name, location, that sort of stuff that you would definitely have a table if there ware commonly > 1 company. Then a table makes more sense, maybe even > 1 table per purpose. This table would be an integral part of the model.

To implement the single row, I don't like the idea of using an identity column. I would personally use a UNIQUE constraint and Just set a check constraint on a column to a permanent value and you are done. It does add an index but the overhead is super minimal, and users/developers can immediately what they did wrong:

create table singleRow

(

SingleRowId int

constraint singleRow$enforceOneRow check (SingleRowId = 1)

constraint PKsingleRow primary key,

ColumnName varchar(100)

)

insert into singleRow

values (1,'Metadata')

Break the law:

insert into singleRow

values (2,'More Metadata')

Msg 547, Level 16, State 0, Line 10

The INSERT statement conflicted with the CHECK constraint "singleRow$enforceOneRow". The conflict occurred in database "tempdb", table "dbo.singleRow", column 'SingleRowId'.

Of course, when you say this:

"accidentally inserted rows."

all I can think of is slapping the developer who even tried :)

Of course preventing this row from being deleted would require a trigger.

Finally, if your users aren't dbo, create a schema for utility tables like this and only grant read rights (and/or update rights if they can modify the values)... Then you can let the system slap them with a really clear error message of DENIED, sucker.

November 17, 2010 11:10 AM
 

BD said:

You need a PK or UNIQUE constraint to prevent multiple rows with RowId=1.  Like the earlier posters, I tend to prefer an EAV-type approach for configuration tables.

November 21, 2010 10:21 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement