Only allow a table to have exactly one row

  • 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?

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

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • 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

  • 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.

  • 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)

  • 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

    END

  • 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

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

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