CODEWITHSUNDEEP
sql-server
Ej.
Ej.

Reputation: 5619

Can you limit the number of rows in a (database) table?

We have a database (SQL Server 2005) which we would like to get under source control. As part of that we are going to have a version table to store the current version number of the database. Is there a way to limit that table to only holding one row? Or is storing the version number in a table a bad idea?

Ended up using this approach:

CREATE TABLE [dbo].[DatabaseVersion]
    (
        [MajorVersionNumber] [int]  NOT NULL,
        [MinorVersionNumber] [int]  NOT NULL,
        [RevisionNumber] [int]  NOT NULL
    )
GO

Insert DataBaseVersion (MajorVersionNumber,  MinorVersionNumber,  RevisionNumber) values (0, 0, 0)
GO

CREATE TRIGGER DataBaseVersion_Prevent_Delete
ON DataBaseVersion INSTEAD OF DELETE
AS
BEGIN
    RAISERROR ('DatabaseVersion must always have one Row. (source = INSTEAD OF DELETE)', 16, 1) 
END
GO

CREATE TRIGGER DataBaseVersion_Prevent_Insert
ON DataBaseVersion INSTEAD OF INSERT
AS
BEGIN
    RAISERROR ('DatabaseVersion must always have one Row. (source = INSTEAD OF INSERT)', 16, 1) 
END
GO

Upvotes: 4

Views: 6012

Answers (7)

Neil McGuigan
Neil McGuigan

Reputation: 48297

You can use Joe Celko's default+primary+check technique:

create table database_version (

  lock char(1) primary key default 'x' check (lock='x'),
  major_version_number int NOT NULL,
  minor_version_number int NOT NULL,
  revision_number int NOT NULL
);

Fiddle with it

Upvotes: 2

Martynnw
Martynnw

Reputation: 10915

Keeping a version number for the database makes total sense. However I prefer to have a Version table that can contain multiple rows with fields for the version number, the time the update occured and the user that performed the upgrade.

That way you know which upgrade scripts have been run and can easily see if they have been run out of sequence.

When you want to read the current version number you can just read the most recent record.

If you only store one record you have know way of knowing if a script has been missed out. If you want to be really clever you can put checks in you upgrade scripts so they won't run unless the previous version of the database is correct.

Upvotes: 1

Mike Hofer
Mike Hofer

Reputation: 17032

Based on your comments to other responses, it seems that:

  1. You don't want users to just modify the value.
  2. You only ever want one value returned.
  3. The value is static, and scripted.

So, might I suggest that you script a function that returns the static value? Since you'll have to script an update to the version number anyway, you'll simply drop and recreate the function in your script when you update the database.

This has the advantage of being usable from a view or a procedure, and since a function's return value is read-only, it can't be modified (without modifying the function).

EDIT: You also wouldn't have to worry about convoluted solutions for keeping a table constrained to one row.

Just a suggestion.

Upvotes: 1

Ole
Ole

Reputation: 1224

Not at all. You can simply add another, ascending column to that table (date, id, whatever), and then order the query by that other column descendingly and limit the result to 1 row:

SELECT v.version FROM version v ORDER by v.date DESC LIMIT 1;

This way you even get a history of when each version was reached.

Edit:

The above sql query wouldn't work on SQL Server since it doesn't support the LIMIT statement. One would have to circumvent that deficiency, possibly as described in this "All Things SQL Server" blog entry.

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146603

By creating the one allowable original row as part of the database initialization script, and (also in that script) removing Insert permissions to that table for all logins (Only Updates will be allowed)

You might also want to disallow deletes as well...

Upvotes: 0

WOPR
WOPR

Reputation: 5393

Generalize the table to hold "settings" and make it a key/value pair

CREATE TABLE Settings (Key nvarchar(max), Value nvarchar(max))

Then make a unique index on Key.

CREATE UNIQUE INDEX SettingsIDX ON Settings (Key)

That will create a table with unique key value pairs, one of which can be Version.

INSERT INTO Settings (Key, Value) VALUES ('Version','1');

Upvotes: 6

Otávio Décio
Otávio Décio

Reputation: 74320

Use a trigger.

Upvotes: 7

Related Questions