Description
Tobias Grimm created an issue — 5th October 2007, 7:02:59:
When a property / column has the unique="true" or unique-key="name" attribute, the created schema will contain something like this:
create table Foo ( Id BIGINT not null, Number VARCHAR(20) unique )or
create table Foo ( Id BIGINT not null, Number VARCHAR(20), unique (Number) )At least Firebird will then assign these constraints some more or less "random" names, like "INTEG_23". If you need to drop such a constraint later on, it's not trivial to figure out the name of the constraint in order to be able to drop it. This can make schema migrations quite tricky.
Firebird allows to assign a name to a constraint:
create table Foo ( Id BIGINT not null, Number VARCHAR(20) constraint constraint_name unique )or
create table Foo ( Id BIGINT not null, Number VARCHAR(20), constraint constraint_name unique (Number) )In the first case, when unique="true" is used, the constraint name could be created the same way, as the foreign key constraint names. When using unique-key="name", the constraint name can be derived from the unique-key attribute.
I'm talking here about Firebird only, I'm not sure, if other dialects support this kind of named constraint, so this probably requires to be delegated to the Dialect.
If there's a chance to get such a feature into NHibernate, I can provide an appropriate patch.
Doug Mayer added a comment — 7th November 2009, 11:25:17:
Tobias,
I realize this is an old issue. Have you done any work on this? I'd be willing to pick it up if it had a chance of making it into the core.
It's my understanding that this would be a divergence from Hibernate, though particularly helpful to those who want to actually use NHibernate to generate their schema. Can someone comment on at least whether such a patch would be entertained for being included in core?
Stephane Verlet added a comment — 29th January 2010, 7:54:53:
I encountered this issue while working on migration tools.
This is how I made it work for me, I did not test this extensively but it seem to work with SQL Server with a fairly complex model.This works with unique-key , not with unique.
Rev : 2.1.0 GA
NHibernate.Mapping.UniqueKey Line 50, method SqlConstraintString(Dialect.Dialect dialect, string constraintName, string defaultCatalog, string defaultSchema) : replaced first statement with:
StringBuilder buf = new StringBuilder( /**d.GetAddPrimaryKeyConstraintString(constraintName)**/ " constraint " <ins> constraintName </ins> " unique " ) .Append(StringHelper.OpenParen); NHibernate.Mapping,Table : Line 419 , replace statement with buf.Append(',').Append(uk.SqlConstraintString(dialect,uk.Name,defaultCatalog,defaultSchema));
Peter O'Connell added a comment — 6th May 2010, 12:46:45:
Based on rev: 2.1.2 GA
This zip contains:
- A new test fixture (NH1165) to verify naming of primary keys and unique-keys.
- Modified versions of Table.cs, PrimaryKey.cs, and UniqueKey.cs.
- Slightly modified versions of several other unit tests for compatibility with the above.
The changes impact schema creation in the following way:
- primary keys output: "constraint PK_TableName primary key (columnName)"
- unique="true": output is unchanged.
- unique-key="KeyName" output: "constraint KeyName unique (columnName)"
- for unique-keys in subclasses, the subclass table name is appended as a suffix to the Keyname
- if needed, names are quoted according to the dialect
- compound keys are supported.
All NHibernate tests pass using SQL Server 2008. I have not tested with other databases/dialects.
Fabio Maulo added a comment — 4th August 2010, 21:13:40:
unique-key tag solve the issue.
For PK please open another new ticket if you still need it
Hung Tran added a comment — 3rd January 2011, 23:05:04:
the patch works fine, i am wondering why it's not migrated into trunk yet
Michael Hallett added a comment — 6th June 2012, 20:41:59:
What's the status of this? It is still an issue and like Hung said the patch hasn't been applied in future versions.
David Roth added a comment — 13th December 2012, 15:02:38:
Still not merged in NHibernate 3.x, so unique keys still have this random key name.
Oskar Berggren added a comment — 13th December 2012, 15:47:49:
I must admit I'm a bit confused about Fabio's comment from 2010 - I can't really find anything in e.g. the Table class and UniqueKey class to support that it's implemented. I'm reopening for now.
To make this happen, someone should preferably take the proposed patch, port it to current master or 3.3.x branch and submit a clean pull request.
David Roth added a comment — 17th January 2013, 16:47:15:
Done, see my pull request: #188
Oskar Berggren added a comment — 18th August 2013, 17:35:48:
David's pull request backported to the 3.3.x branch, with additional tests: https://github.com/oskarb/nhibernate-core/commits/NH1165-3.3.x/
However, I think this patch breaks on Informix, since it requires the "CONSTRAINT name" part after the constraint definition, instead of before like just about any other dialect does it.
We could add something to the dialect to fix this, but it appears Hibernate have refactored how unique constraints are generated. It might be better to port their solution, which seem to include support for constraint names.
See:
https://hibernate.atlassian.net/browse/HHH-7797
https://github.com/hibernate/hibernate-orm/tree/master/hibernate-core/src/main/java/org/hibernate/dialect/uniqueAnyone want to have a look at this?