Nov 9, 2009 Posted by John MacIntyre

How to enforce a foreign key constraint against multiple tables

I am building a web app with Ben Alabaster, and one of the requirements is for the user to be able to flag items for moderators. So the user can flag entity A, entity B, entity C, etc… So I created a single flag table. Which I then tried to tie it to the entity tables, hoping for something like Where all the foreign key relationships were from [flag].[entity_id] to [EntityX].[id] Then when I wanted the top 10 flags from a particular entity (B in this case), I could run a query like select top 20 e.[name], count(*) "count" from entityB as e    left join flag as f     on f.entity_id = e.id where f.entity_type='B' group by e.[name] order by count(*) desc Unfortunately, if you were to create the above table relationship, and run the following inserts insert into EntityA( id, name) values (1, 'EntityA'); insert into EntityB( id, name) values (2, 'EntityB'); insert into EntityC( id, name) values (3, 'EntityC'); insert into EntityD( id, name) values (4, 'EntityD'); The following statement insert into flag(entity_id, flag_reason) values(5, 'Testing without a valid FK value.'); would fail as expected, as expected, with the following error. “The INSERT statement conflicted with the FOREIGN KEY constraint “FK_flag_EntityA”. The conflict occurred in database “test”, table “dbo.EntityA”, column ‘id’.” But insert into flag(entity_id, flag_reason) values(1, 'Testing the FK to entity A.'); would also fail, which was undesired, with the following error: “The INSERT statement conflicted with the FOREIGN KEY constraint “FK_flag_EntityB”. The conflict occurred in database “test”, table “dbo.EntityB”, column ‘id’.” + So, my options with regards to referential integrity are : Ditch the referential integrity, which I am vehemently opposed to. ++ Create multiple flag tables, each with the exact same schema, but a different Foreign Key relationship, which just seems wrong. Managing referential integrity via triggers. While I’m not a big fan of triggers, the ‘Managing referential integrity via triggers.’ option seems like the only tolerable one. So I added the [entity_type] column to my flag table. Removed the relationships And wrote the following trigger to manage the foreign key relationship. -- ============================================= -- Description: maintain referential integrity on -- a column which is a FK for different tables -- ============================================= CREATE TRIGGER flag_entity_id_fk ON flag AFTER INSERT,UPDATE AS BEGIN declare @entity_type char(1); declare @entity_id int; declare @cnt int;  -- SET NOCOUNT ON added to prevent extra result sets from  -- interfering with SELECT statements.  SET NOCOUNT ON;  -- get info  select @entity_type=entity_type,    @entity_id=entity_id,... read more

Nov 7, 2009 Posted by John MacIntyre

How to hook up NUnit as an option in the ASP.NET MVC application wizard

Ben Alabaster and I have started a project, lets call it Project X for now (original eh? ;-). Anyway, we have decided to blog about its development. I will be posting something in the next few days, and Ben just published his first post “How do I hook my version of nUnit into the ASP.NET MVC template?“. Here’s a blurb: If you’ve been looking for a way to integrate nUnit into your ASP.NET MVC 1.0 template – that is, when you create a new ASP.NET MVC application and it asks you if you’d like to create a test project, nUnit shows up in the list along with the usual Visual Studio Unit Test option. There are a number of longwinded ways of doing things. There’s also a relatively simple way touted on the Visual Web Developer Team Blog which I’ll spare you the headache of running it and finding the same problems I did…. Ben’s post can be found on his blog... read more