Competition: What’s your favorite Redgate tool? Enter now.

UPDATE without WHERE clause warning for single-row tables

rsocolrsocol Posts: 4 Bronze 1
edited November 5, 2016 2:40AM in SQL Prompt
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)

Comments

Sign In or Register to comment.