0

I created a table: CREATE TABLE myTable ( id int GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE)PRIMARY KEY, pcdurl varchar(2000) NOT NULL )

And I would like to make the pcdurl attribute a unique constraint.

But I get an SQL 633 error. I guess it is because the size exceeds the maximum number allowed. My first question would be what is this number?

Here it says the number is 255-n, where n is the number of columns allowing null values.

But I was also reading on this page: "Maximum combined length of columns in a UNIQUE constraint (supported through a UNIQUE index, in bytes) 8192"

So my second question would be how can I overcome this problem while keeping the attribute as unique key constraint?

Thank you very much in advance

BPL
  • 277
  • 1
  • 3
  • 4

1 Answers1

0

Essentially, you're going to have to find a smaller respesentation of the pdcurl so that you and enforce the unique constraint on that representation.

You could have an additional field which is the hash of the pdcurl, and make that hashed value a unique field. Depending on the hashing function you have a small (possibly tiny) chance of a collision. But it will always prevent duplicates.

Or you may find that all your urls start with the same path, and so replace that part with .\ or something?

MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Thank you very much for the answer, Dems. But it would be better if I can find a solution without any collision possibility. And the same part of the urls is only of 10 characters length. But do you have any idea what is the maximum size allowed on 9.7? – BPL Jun 28 '12 at 10:11
  • @BPL - Don't dimiss hashing so easily. See this answer on how likely a collision is with MD5 *(when I say tiny, I really do mean tiny)* http://stackoverflow.com/questions/201705/how-many-random-elements-before-md5-produces-collisions – MatBailie Jun 28 '12 at 10:22
  • @BPL - And wikipedia shows that with MD5 (128 bit) you need to generate 26,000,000,000 values to have a 0.0000000000000001% chance that any of them collided with any of the others. *(Which is better than most hardward failure rates afaik)* http://en.wikipedia.org/wiki/Birthday_attack – MatBailie Jun 28 '12 at 10:30
  • Thank you for the replies, we decided to go with a hash function which returns 256 bit output. Now I should find an algorithm for that. – BPL Jun 29 '12 at 13:09