Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ruahm
Starting Member
2 Posts |
 Posted - 2004-08-09 : 20:02:39
|
Anyone know how to add a constraint to a text column to force rows to be unique?I tried UNIQUE, msdn doesn't state that it can't be used on text columns, but the error is that an index can't be created (UNIQUE creates one right?) on text columns:Column 'ExperimentName'. Cannot create index on a column of text, ntext, or image data type. Any ideas how to enforce this constraint in the table definition (rather than, for example, manually testing for duplicates before perfroming an INSERT)?cheersRua HM. |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-08-09 : 20:04:31
|
| What is the maximum expected length of the field? It might be a better idea to stick with a big varchar. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-09 : 20:08:03
|
| When you create a unique constraint, a unique index is created to enforce the uniqueness. And no you can't create an index on a text column.From MSDN and SQL Server Books Online:"Columns consisting of the ntext, text, or image data types cannot be specified as columns for an index."So you will need to test for the duplicate prior to the insert or even update.Tara |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-08-09 : 20:19:18
|
| I'm with Timmy. You probably need a VARCHAR field instead of a TEXT field. Then, you could add your UNIQUE contraint on it.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
ruahm
Starting Member
2 Posts |
Posted - 2004-08-09 : 20:54:00
|
| Sweet then - I'm happy to use varchar (I inherited the table design).I was assuming that using varchar would have its own problems..cheers team, that was fast! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-09 : 20:55:24
|
| If your data will be less than 8000, then varchar is the way to go.Tara |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-08-10 : 05:08:08
|
| A workaround would be to make a MD5-hash of the text and compare that instead. An MD5-hash is 32 or 35 characters (can't remenber which) and is often used for creating checksum values for files. Two strings with only a slight difference would not generate the same hash so it's pretty safe. Let me know if this is something you might consider...it involves creating an extended stored procedure and I have to find it first.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
|
|
UNIQUE text column?