I've answered a few questions regarding this on the MS Forums and it was also something that we needed in my current project. I thought I would post something here in case anybody else needs it.

Here's the scenario: you want to make sure that items added to a list are unique, or at least that a certain column is unique – perhaps some type of ID column, or some other key value that cannot be duplicated or else bad things happen. Here's what you need to do:

1. Add an Event handler to the list. Here are a few good postings that cover the basics of event handlers:

http://www.sharepoint-tips.com/2006/08/bad-news-synchronous-list-events-bug.html

http://blah.winsmarts.com/2006-7-Sharepoint_2007__List_Events_Practical_Example__Creating_a_rigged_survey.aspx

1. Add the following code for the ItemAdding event:

   public override void ItemAdding(SPItemEventProperties properties)

   {

   try

   {

   SPList list = properties.OpenWeb().Lists[properties.ListId];

   string sConcernName = properties.AfterProperties["Title"].ToString();

   SPQuery query = new SPQuery();

   query.Query = @"<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>" + sConcernName + "</Value></FieldRef></Eq></Where>";

   SPListItemCollection items = list.GetItems(query);

   if (items.Count > 0)

   {

   properties.Cancel = true;

   properties.ErrorMessage = "A Concern with that Name already exists";

   }

   }

   catch (Exception ex)

   {

   properties.Cancel = true;

   properties.ErrorMessage = ex.Message;

   }

   }

2. You'll need to edit the query string to work off of whichever column or columns you need to be unique, but that's just some simple CAML
3. Register the event handler (see the articles from #1 above for help)
4. Test. If you've lived a righteous life, you will not be able to add columns with duplicate titles (at least for the example above). Instead, you'll get the standard SharePoint error page with your custom message.

Hope that helps somebody.