Only allow a table to have exactly one row

Question

Post reply

Only allow a table to have exactly one row

M Chabot

SSCrazy

Points: 2511
More actions
February 18, 2008 at 2:14 am

#182185

I have a database table that keeps track of the date of the most recent data import. This date is used to simplify many queries that are retrieving the latest info.
What is the best way to ensure that this database table only ever has exactly one row it?
The first idea that came to mind is adding a check constraint, but the expression syntax doesn't seem to like "count(*)" in it. The solution is to call a function like CountDate() and I can write count(*) in the function. Am I missing something obvious with this?
Check constraints are not fired for delete statements, so my check constraint won't protect me form an accidental delete. Maybe I need a trigger instead of a constraint?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

Yes.. Even I feel you should go for a Trigger.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Carl Federl One Orange Chip Points: 25384 More actions · Answer 2

Easiest is to have a uniqueness constraint and a check constraint on the same column

create table OOAK -- (One Of A Kind)

(OOAK_id tinyint not null

, constraint OOAK_P unique (OOAK_id )

, constraint OOAK_C_id check (OOAK_id = 1 )

)

SQL = Scarcely Qualifies as a Language

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 3

Use an insert trigger.

create trigger mytrigger on mytable for insert

rollback

return

that's it. If you have a row in the table, you won't get more.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Heh... if you don't care so much about a trigger providing a friendly error, you can always just have the table lock itself up when you create it...

CREATE TABLE YourTable (SomeID INT IDENTITY(1,1) PRIMARY KEY, SomeString VARCHAR(10))

INSERT INTO YourTable (SomeString) SELECT 'Test'

SELECT * FROM YourTable

--===== This is what locks the table up. Won't allow the next Identity of 1 which was used by the first insert above.

DBCC CHECKIDENT ('DODAH',RESEED,0)

INSERT INTO Dodah (STring) SELECT 'Test'

--DROP TABLE YourTable

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

M Chabot SSCrazy Points: 2511 More actions · Answer 5

Thank you for the suggestions. The idea of using both a unique constraint and a check constraint is a nifty solution to that problem.

I am going to go with the below trigger, which protects against an accidental delete in addition to the accidental insert.

-- =============================================

-- Make sure this database table only ever has exactly one row in it

-- =============================================

ALTER TRIGGER [dbo].[tr_aid_business_date]

ON [dbo].[business_date]

AFTER INSERT,DELETE

AS

BEGIN

SET NOCOUNT ON;

DECLARE @CT int

SELECT @CT = COUNT(*)

FROM dbo.business_date

IF @CT <> 1

BEGIN

RAISERROR ('Business date table must have exactly one row', 16, 1)

ROLLBACK TRAN

RETURN

END

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 6

I would go with a check constraint on a column with unique constraint to make sure there can only be one row.

Add a instead of delete trigger to prevent deleting the single row.

create trigger [dbo].[TR_PREVENT_DELETE__MyTable]

on [dbo].[MyTable]

instead of delete

as

set nocount on

declare @ErrorMessagevarchar(400)

select@ErrorMessage=

'Trigger [dbo].[TR_PREVENT_DELETE__MyTable] - '+

'Delete is not allowed on table [dbo].[MyTable]'

raiserror( @ErrorMessage, 16, 1 )

if @@trancount > 0 begin rollback end

return