2

I'm trying to add on to an existing database where we have a tblUsers table. As of right now, we're storing user images in a file system, and now we're moving away from that by storing User images in the database in a new tblUserImages table.

Here's what the two tables look like.

Table Structure

I want to add a constraint that only allows one active picture per user, but I don't know how to do this. I've tried looking it up to no avail. Any help is greatly appreciated! I still have a lot to learn about SQL Server.

Charlie Pugh
  • 342
  • 2
  • 18

3 Answers3

6

If you are using SQL Server 2008, you can create a filtered unique index like the following: 

create unique index uniqueUserActiveImages 
    ON tblUserImage(UserID, Active) 
WHERE Active = 1;

This allows the user to have multiple inactive images but only one active image.

vee
  • 38,255
  • 7
  • 74
  • 78
1

I'd recommend you consider having a separate entity (a separate table) for inactive images. That way you won't need an "active" attribute in tblUserImages, all images in this table will be active. You would only reference the inactive image table if you wanted to query the image history. The active image table would either have a unique constraint on UserId, or it could simply use UserId as a primary key.

This probably sounds like an esoteric solution, but it is based on experience of having seen this same data modelling problem come up in other systems. The core issue is this: the active state of the system is not the same as the historical state of the system. An entity that represents a part of the active state is not the same as an entity that logs historical state, even if the information in both entities overlap. If you evolve your data model using this idea, you should see that in fact the attributes of historical entities do tend to vary from their active "operational" counterparts. In other words you may find that the field layout of the active image table may not be exactly the same as the ideal field layout for historical image table.

The only drawback I can see in structuring the data in this way is the necessity to copy the active image into the historical table at the point that it is replaced. In several important respects this solution is superior - queries will be faster, especially if you use a clustered key for user id. Most importantly the solution will be easier for other coders to understand. Creating a filter index is a complete solution and a good use of SQL Server features, but it may not be as clear to future maintenance programmers.

Paul Keister
  • 12,851
  • 5
  • 46
  • 75
0

I have never been much of a fan of triggers, but in this case I think it can help you accomplish your task.

Suggest writing a trigger to check, on insert or update, to see if there are any other active images associated with the user. If there are, you can either throw a custom error, or set all other images inactive.

Mike Henderson
  • 1,305
  • 15
  • 27
  • I make the trigger suggestion as this will allow one to n number of images, but only on _active_ images. The unique index should limit entries to one _active_ and one _inactive_ per user. – Mike Henderson Jul 13 '13 at 01:50