Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql-server

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
like image 524
Ej. Avatar asked Dec 02 '22 08:12

Ej.


2 Answers

Use a trigger.

like image 78
Otávio Décio Avatar answered Feb 12 '23 10:02

Otávio Décio


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');
like image 34
WOPR Avatar answered Feb 12 '23 11:02

WOPR