1

Is there a way for SQL to enforce unique column values, that are not a primary key to another table?

For instance, say I have TblDog which has the fields:

  1. DogId - Primary Key
  2. DogTag - Integer
  3. DogNumber - varchar

The DogTag and DogNumber fields must be unique, but are not linked to any sort of table.

The only way I can think of involves pulling any records that match the DogTag and pulling any records that match the DogNumber before creating or editing (excluding the current record being updated.) This is two calls to the database before even creating/editing the record.

My question is: is there a way to set SQL to enforce these values to be unique, without setting them as a key, or in Entity Frameworks (without excessive calls to the DB)?

I understand that I could group the two calls in one, but I need to be able to inform the user exactly which field has been duplicated (or both).

Edit: The database is SQL Server 2008 R2.

Cody
  • 8,686
  • 18
  • 71
  • 126

5 Answers5

4

As MilkywayJoe suggests, use unique key constraints in the SQL database. These are checked during inserts + Updates.

ALTER TABLE TblDog ADD CONSTRAINT U_DogTag UNIQUE(DogTag)

AND 

ALTER TABLE TblDog ADD CONSTRAINT U_DogNumber UNIQUE(DogNumber)
StuartLC
  • 104,537
  • 17
  • 209
  • 285
3

I'd suggest setting unique constraints/indexes to prevent duplicate entries.

spender
  • 117,338
  • 33
  • 229
  • 351
  • Ok, this makes sense. From what I read, there can only be one `NULL` value in the entire table? These are fields that also can be `NULL`. Or am I reading it wrong? – Cody Jul 09 '12 at 15:47
  • 2
    IIRC, you're right. However from sql server 2008+ you're in luck... http://stackoverflow.com/a/767702/14357 ... you can set constraint to ignore null columns. – spender Jul 09 '12 at 15:49
1

ALTER TABLE TblDog ADD CONSTRAINT U_DogTag UNIQUE(DogTag)

Inkey
  • 2,189
  • 9
  • 39
  • 64
0

It doesn't appear as though Entity Framework supports it (yet), but was on the cards. Looks like you are going to need to do this directly in the database using Unique Constraints as mentioned in the comments.

Adam Houldsworth
  • 63,413
  • 11
  • 150
  • 187
0

CREATE UNIQUE INDEX idxUniqueDog ON TblDog (DogTag, DogNUmber)

paul
  • 21,653
  • 1
  • 53
  • 54
  • 1
    You really need two separate indexes, not one. If you just have one index you can still have hundreds of records with the same DogTag or the same DogNumber – Harv Jul 09 '12 at 15:42
  • well, it depends on the desired uniqueness. The OP is not specific on whether or not (D, 1) and (D, 2) should be allowed. This single index is, I believe, the quickest mechanism for his task of checking to see whether a dog exists before editing/inserting. – paul Jul 09 '12 at 15:59