Bad Idea Cowboy Hat: Using a foreign key to prevent updates2
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.
Category: Bad idea Cowboy hat, Microsoft SQL Server, SQLServerPedia Syndication | Tags: Bad idea Cowboy hat, foreign key, Microsoft SQL Server
2 thoughts on “Bad Idea Cowboy Hat: Using a foreign key to prevent updates”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
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).
Probably. Still looks like a real pain to me though. And it only blocks updates not inserts or deletes.