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.
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);
// Not unique throw an error
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 ?!?!
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.
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.
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.