No, the constraint name is completely unpredictable. If you want your names to be consistent, you can name them correctly by applying a predictable / repeatable name manually. I have no idea how you would do this in the code you have, but in T-SQL instead of:
CREATE TABLE dbo.foo(bar INT PRIMARY KEY);
CREATE TABLE dbo.blat(bar INT FOREIGN KEY REFERENCES dbo.foo(bar));
(The above end up with constraints having names like PK__foo__DE90ECFF6CF25EF6
and FK__blat__bar__1B1EE1BE
.)
You would say:
CREATE TABLE dbo.foo(bar INT, CONSTRAINT PK_foo PRIMARY KEY (bar));
CREATE TABLE dbo.blat(bar INT, CONSTRAINT fk_foobar FOREIGN KEY(bar)
REFERENCES dbo.foo(bar));
You do not want a gigantic gen_clust_index (Internal Clustered Index). That size is ungodly huge even for a secondary index.
You may have to resort to triggers or stored procedures to check for the key well in advance.
You could also think about performing an SHA1 function call using the VARCHAR(3071)
field. SHA1 will return a 40-character field. This hash may be just what you need to index.
Suppose you have this
CREATE TABLE mytable
(
id int not null auto_increment,
txt VARCHAR(3071),
primary key (id)
) ENGINE=InnODB;
and you want to make a UNIQUE
index on txt. Try the SHA1 approach
CREATE TABLE mytablenew LIKE mytable;
ALTER TABLE mytable ADD txtsha1 CHAR(40);
ALTER TABLE mytable ADD UNIQUE KEY (txtsha1);
INSERT INTO mytablenew (id,txt,txtsha1)
SELECT id,txt,SHA1(txt) FROM mytable;
Then, count them
SELECT COUNT(1) FROM mytable;
SELECT COUNT(1) FROM mytablenew;
If the Counts are the Same, CONGRATULATIONS !!! Now you have a unique index of length 40. You can finish up with:
ALTER TABLE mytable RENAME mytableold;
ALTER TABLE mytablenew RENAME mytable;
DROP TABLE mytableold;
This could be more atomically as pointed out in the comments below:
RENAME TABLE mytable TO mytableold, mytablenew TO mytable;
DROP TABLE mytableold;
Perform this on whatever table you intend to have this big column. You have to remember to add the SHA1 of the data along with the data upon INSERT
.
The odds of duplicate keys is 1 in 2 to the 160th power (that 1.4615016373309029182036848327163e+48. If I get the exact figure, I'll post it someday).
Give it a Try !!!
Best Answer
Are you saying constraint names can't be given at table creation time? That isn't correct:
dbfiddle here
It's exactly the same for
NOT NULL
constraints:dbfiddle here
Of course, just like any other constraint:
dbfiddle here