Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: UNQIUE constraint on string column?

    What do you guys think about creating a unique constraint on a string column in a database?

    Currently, I'm enforcing uniqueness through the stored procedure that inserts rows into the table. E.g.
    Code:
    PROCEDURE addRow( name )
    
    DECLARE r INT;
    SELECT COUNT(*) INTO r FROM foo WHERE foo.foo_name = name;
    IF r = 0 THEN
        INSERT INTO foo (foo_name) values(name);
    ELSE
        // Not unique throw an error
    
    END PROCEDURE
    What do you guys think?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm strongly in favor of the unique constraint, for many reasons.

    First and foremost, that makes the database engine aware of your (the data modeler's) expectations of uniqueness, which the engine itself will then enforce. That means that NOTHING will violate that expectation unless it can disable the constraint (such as a DBA with a death wish). No coding error, no renegade code, no accidental fat-fingers will rob you of sleep once you get the constraint in place.

    As a side benefit, this means that you no longer need to code the check for uniqueness into every snippet of code that you write... A violation of the uniquenss constraint will be rejected by the database engine and raise an error. You can deal with the error in error handling code instead of data preparation, which makes the code easier for me to read (since I don't need to even know that the code for handling uniqueness exists until the constraint is violated).

    Let's see... You get better performance, cleaner code, and more sleep. Who doesnt' want that ?!?!

    -PatP

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Heh, Thanks for the reply Pat. I'm starting to realize how much less of a headache it will be if do just add the constraint.

    But isn't there a significant perfomance drop by applying a UNIQUE constraint on a string column?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try adding the constraint and seeing if you can measure a difference. Given enough data, you might be able to measure some difference.

    Keep in mind that by adding the UNIQUE constraint, SQL Server actually implements a unique index on the column, and that often improves performance of many retrieval operations far more than it slows changes (INSERT, UPDATE, and DELETE) operations.

    -PatP

  5. #5
    Join Date
    Jan 2008
    Posts
    186
    Hmmm interesting point.

    I don't mind if there's a "small" decrease in performance, just don't want it too be too large.

    Also, the majority of the operations taking place are going to be retrieval (SELECT statements) so largely I'm concerned with the performance of those

    So if the performance of SELECT actually gets better (even if it's at the expense of UPDATE/INSERT/DELETE) I might actually be okay with that.

    Hmmm, I'll have to think about this. Thanks for the insight

  6. #6
    Join Date
    Nov 2005
    Posts
    122
    Quote Originally Posted by dbguyfh
    Hmmm, I'll have to think about this. Thanks for the insight
    What's there to think about? With your procedure you will need an index on the column anyway, so why not put the index and your "uniqueness procedure" together into a unique index?

  7. #7
    Join Date
    Jan 2008
    Posts
    186
    Well, I alrady have a primary key (an ID field) for that table which is automatically indexed.

    Should I make my string column a secondary key, then?

  8. #8
    Join Date
    Nov 2005
    Posts
    122
    Yes, create a unique index on the column.
    Code:
    create index [indexname] on [tablename]([columnname])
    You can have many unique indexes (and non-unique indexes for that matter) on a table if you absolutely want to.

  9. #9
    Join Date
    Jan 2008
    Posts
    186
    Okay I'll give that a shot and see how things go.

    The database might get extremely large, but likely, we will only be running SELECT queries on it

    Thanks guys

  10. #10
    Join Date
    Jul 2007
    Posts
    96
    Hey dbguyfh

    First of all welcome to the boards

    Secondly, I'd like to point out something that is a bit outside the scope of what has been said already. It's out of the scope in the sense that you should indeed apply a unique constraint...

    However, what I'd like to point out, is that your SP wasn't much of a performance friend either

    PROCEDURE addRow( name )

    DECLARE r INT;
    SELECT COUNT(*) INTO r FROM foo WHERE foo.foo_name = name;
    IF r = 0 THEN
    INSERT INTO foo (foo_name) values(name);
    ELSE
    // Not unique throw an error

    END PROCEDURE
    Next time you need to do some kind of check of this kind please consider using the following, if a constraint is not appliable:

    IF EXISTS (SELECT f.foo_id FROM foo f WHERE f.foo_name = @name)
    -- NOT UNIQUE
    ELSE
    -- UNIQUE
    END

  11. #11
    Join Date
    Jan 2008
    Posts
    186
    Ahhhhh much better!

    Thanks, that makes my code look so much more cleaner!

    Heh, yeah I thought what I had was kind of excessive there

  12. #12
    Join Date
    Jul 2007
    Posts
    96
    If your DB is only for selects then you should consider searching for settings, developments, configurations, etc, that you can make to read-only databases that can significantly boost the database performance.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would strongly recommend using DRI (Declarative Referential Integrity) instead of creating the unique index. DRI will create the unique index for you, but it also does many other things now (at least for any software that checks for DRI), and will do even more for you later. You also get the side benefit from the fact that DRI is portable between different SQL database engines, while the CREATE INDEX statement usually isn't portable.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •