UPDATE without WHERE clause warning for single-row tables

rsocol

We have several tables with various configuration settings, which will always have a single row. To ensure this, we always use some constraints, like these:

CREATE TABLE ConfigurationTable (
	DummyID tinyint CONSTRAINT [DK_ConfigurationTable_DummyID] DEFAULT (1),
		CONSTRAINT [PK_ConfigurationTable] PRIMARY KEY (DummyID),
		CONSTRAINT [CK_ConfigurationTable_DummyID] CHECK (DummyID=1),
	SomeSetting BIT NOT NULL,
	AnotherSetting DATETIME NULL
	-- ...
)

SQL Prompt always produces warnings when we update such tables, because we did not write any WHERE condition. Of course, we could add a WHERE clause (such as "WHERE DummyID=1") in all the UPDATE statements, but there are a lot of statements (and many configuration tables). We could also disable the warning, but I fear that someday I will see such a warning that is actually useful (although this did not happen yet, in the last few months since I'm using this feature).

My suggestion is that SQL Prompt should recognize the tables that have constraints such as these (CK with constant value on the PK column) and should not produce warnings for UPDATE-s without WHERE clauses on such tables. Is that possible?

Thank you,
Razvan Socol
(former SQL Server MVP)

Alex B

Hi Razvan,

The best course of action is to raise this on the Uservoice forum for the developers consideration (and commenting on the options they have already marked as started). You can see in the latest beta release they have already implemented some other suggestions.

Kind regards,
Alex

rsocol

I have posted the suggestion here: https://redgate.uservoice.com/forums/94 ... single-row

Thanks,
Razvan