Bad Idea Cowboy Hat: Using a foreign key to prevent updates

2

May 20, 2019 by Kenneth Fisher

When I decided to rip off of Brent Ozar’s (b/t) Bad Idea Jeans series (yes I asked) I decided that since I live in Texas it would be a cowboy hat (now I need to go buy one I guess). So, putting on my imaginary cowboy hat here is one of the strangest things I’ve seen or come up with in years.

I have to admit, this one wasn’t my idea. But the other day I saw a pair of tables, with identical data with a foreign key between them. Which seemed a little weird. Why two identical tables? Why a foreign key between them?

It got weirder. The tables were of the format (id, description, value). The foreign key was on those last two columns. Normally you use a foreign key to maintain referential integrity. Make sure that the values in ColumnA exist in TableB. In this case though, since they are including the value column it has a strange (side?) effect. You can’t update those columns.

Quick demo because I find they explain things far better than I ever can.

-- Setup code

USE Test;
GO

CREATE TABLE Table1 (
	ID INT NOT NULL IDENTITY(1,1)
	, Descrip varchar(50)
	, Val varchar(50)
	, CONSTRAINT pk_Table1 PRIMARY KEY (Descrip, Val)
	);

CREATE TABLE Table2 (
	ID INT NOT NULL IDENTITY(1,1)
	, Descrip varchar(50)
	, Val varchar(50)
	, CONSTRAINT pk_Table2 PRIMARY KEY (Descrip, Val)
	, CONSTRAINT fk_Table2_Table1 FOREIGN KEY (Descrip, Val)
		REFERENCES Table1 (Descrip, Val)
	);

INSERT INTO Table1 VALUES 
	('Property1','Value1')
	,('Property2', 'Value2')
	,('Property3', 'Value3')
	,('Property4', 'Value4')
	,('Property5', 'Value5');

INSERT INTO Table2 VALUES
	('Property1','Value1')
	,('Property2', 'Value2')
	,('Property3', 'Value3')
	,('Property4', 'Value4')
	,('Property5', 'Value5');

So obviously inserts work (as long as you do them in the right order), but how about updates?

UPDATE Table1 SET Val = 'NewValue'
	WHERE Descrip = 'Property1';

UPDATE Table2 SET Val = 'NewValue'
	WHERE Descrip = 'Property1';

Msg 547, Level 16, State 0, Line 39
The UPDATE statement conflicted with the REFERENCE constraint “fk_Table2_Table1”. The conflict occurred in database “Test”, table “dbo.Table2”.

The statement has been terminated.
Msg 547, Level 16, State 0, Line 42
The UPDATE statement conflicted with the FOREIGN KEY constraint “fk_Table2_Table1”. The conflict occurred in database “Test”, table “dbo.Table1”.

The statement has been terminated.

I’ll be honest, I have no idea why you would do this. At this point, the only way to update the data is to remove the foreign key, update, and add it back again. Or wipe out the rows (in the right table order of course) and add them back again.

If you really don’t want people to update a table don’t grant the permissions. Or if you are really desperate:

DENY UPDATE ON Table1 TO MyUser;

Note: If you want to hit everyone you can always do the DENY on the public role. Well, everyone but dbo and members of sysadmin.

2 thoughts on “Bad Idea Cowboy Hat: Using a foreign key to prevent updates

  1. Lawrence Ness says:

    This is clever in a diabolical way. Perhaps the user who created the tables was concerned that others might not manage permissions in the future in the way that he or she would want (i.e., they might add a new user to the database in some unknown role).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,528 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: