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.