Is there a way to create a unique constraint containing several columns ?

Topics: Core, Customizing Orchard, General, Installing Orchard, Troubleshooting, Writing modules
Feb 22, 2013 at 9:42 PM
Edited Feb 22, 2013 at 10:05 PM
Through what I found on Google, there was a problem with this very frequent usage of SQL language.
No way to do this in migration.
It seems that we can create a unique constraint for one column but not a constraint spending through several columns.

I will apply it with SQL Studio on the table but this is totally non pro.


ADDENDUM

in migration, this kind of syntax works for SQL Server, all.

SchemaBuilder.ExecuteSql("ALTER TABLE 'here_enter_final_table_name' ADD CONSTRAINT uc_constraint_name UNIQUE (col1,col2...coln)");
Developer
Feb 23, 2013 at 10:15 PM
I don't think migrations currently give you a tool for this (you can add indices for multiple columns however).
Feb 24, 2013 at 7:35 AM
Edited Feb 24, 2013 at 7:36 AM
The syntax ALTER TABLE 'here_enter_final_table_name' ADD CONSTRAINT uc_constraint_name UNIQUE (col1,col2...coln) seems to be ok for MySQL and Oracle.
I have no MysQL in my area to test.

Modifying the migration builder would be just adding another AlterTable, it could be interesting as far as migrating to Document DB seems very interesting ..... but it could be another project, 'Orchard2'. SQL servers reprents so many users that it will be impossible to stop the 'relational' Orchard thread.
And for this, managing seriously SQL contraints is a must.

From what I understand from builder code, the building phase is just creation of a large SQL statement that goes applyed directly to ve DB through a connection. Very few NHibernate in this phase ?

There could be another way to allow multiple columns constraints, just allowing to add the constraint name to the actual Unique column modifier, so no necessity to do an alter table after the create.
Coordinator
Feb 24, 2013 at 5:11 PM
It's hardly a must, as far as I can remember you're the first to ask. Can't you do it in plain sql?
Feb 24, 2013 at 7:28 PM
Edited Feb 24, 2013 at 7:30 PM
Problem with the first success is that you get new questions... you should be happy :(
Just read my first message's ADDENDUM: executesql works.
For a better integration I had a look on code and its seem easy to add a property 'uniqueContstraintName, to column and use it to generate correct Unique property in the create table.
Have a good day and thanks for presence :)
Coordinator
Feb 24, 2013 at 7:42 PM
I am very happy with the success of Orchard, don't get me wrong. It's a question of priorities: as this is a rather uncommon request, and we have lots of other improvements to do, I would rank this pretty low, especially as there is a workaround. Of course, if this is important to you and you have a patch for it, we'll be happy to evaluate and include it. The way to do this is to file a bug and attach the patch. Thanks.
Feb 25, 2013 at 6:26 AM
Edited Feb 25, 2013 at 6:27 AM
I will try to propose something ASAP via bugs.
On the other side, it could be time for Orchard to 'hire' middle management :), this is usually the answer in front of task overflow
Coordinator
Feb 25, 2013 at 8:21 AM
Hire? Sure, if a company that uses Orchard is ready to pay the salary of someone working full or part time on the project, I can find people to do the job.
Feb 25, 2013 at 12:43 PM
Edited Feb 25, 2013 at 12:44 PM
I was joking...but isn't the foundation partly for this purpose: finding financial support.
As an exemple, hundred of people work today worldwide for Redhat open source projets and are paid by its foundation.
Coordinator
Feb 25, 2013 at 4:57 PM
And I'm being totally serious. I think we need that to happen in the near future.

No, the foundation's role is to provide a legal framework for code ownership, and that's pretty much it. We would need a different org for that sort of thing. Redhat/WordPress/etc. are completely different beast, as their scale is orders of magnitude larger. We're at a point where a few people can make a decent living from full-time Orchard consulting jobs. That's a good place to be, but we need to go much farther and bigger before we can sustain paid contributors. In any case, we need corporate sponsorship (beyond Microsoft, which we have to thank for continuing to pay Sébastien to work full-time on Orchard) because we don't have any other sources of revenue for the moment. There are efforts underway to change that but it won't happen overnight.