--- a/cubicweb/hooks/syncschema.py	Wed Jun 22 17:13:10 2016 +0200
+++ b/cubicweb/hooks/syncschema.py	Mon Jun 20 17:59:43 2016 +0200
@@ -475,14 +475,6 @@
         # then make necessary changes to the system source database
         syssource = cnx.repo.system_source
         attrtype = y2sql.type_from_rdef(syssource.dbhelper, rdef)
-        # XXX should be moved somehow into lgdb: sqlite doesn't support to
-        # add a new column with UNIQUE, it should be added after the ALTER TABLE
-        # using ADD INDEX
-        if syssource.dbdriver == 'sqlite' and 'UNIQUE' in attrtype:
-            extra_unique_index = True
-            attrtype = attrtype.replace(' UNIQUE', '')
-        else:
-            extra_unique_index = False
         # added some str() wrapping query since some backend (eg psycopg) don't
         # allow unicode queries
         table = SQL_PREFIX + rdefdef.subject
@@ -497,10 +489,9 @@
             # the entity's type has just been added or if the column
             # has not been previously dropped
             self.error('error while altering table %s: %s', table, ex)
-        if extra_unique_index or entity.indexed:
+        if entity.indexed:
             try:
-                syssource.create_index(cnx, table, column,
-                                       unique=extra_unique_index)
+                syssource.create_index(cnx, table, column, unique=False)
             except Exception as ex:
                 self.error('error while creating index for %s.%s: %s',
                            table, column, ex)
@@ -654,7 +645,6 @@
             self.cnx.vreg.schema.add_relation_def(rdef)
 
 
-
 class RDefUpdateOp(MemSchemaOperation):
     """actually update some properties of a relation definition"""
     rschema = rdefkey = values = None # make pylint happy
@@ -766,11 +756,6 @@
     def precommit_event(self):
         cnx = self.cnx
         rdefentity = self.entity.reverse_constrained_by[0]
-        # when the relation is added in the same transaction, the constraint
-        # object is created by the operation adding the attribute or relation,
-        # so there is nothing to do here
-        if cnx.added_in_transaction(rdefentity.eid):
-            return
         rdef = self.rdef = cnx.vreg.schema.schema_by_eid(rdefentity.eid)
         cstrtype = self.entity.type
         if cstrtype in UNIQUE_CONSTRAINTS:

--- a/cubicweb/server/schema2sql.py	Wed Jun 22 17:13:10 2016 +0200
+++ b/cubicweb/server/schema2sql.py	Mon Jun 20 17:59:43 2016 +0200
@@ -26,12 +26,24 @@
 
 from yams.constraints import (SizeConstraint, UniqueConstraint, Attribute,
                               NOW, TODAY)
+from logilab import database
+from logilab.common.decorators import monkeypatch
 
 # default are usually not handled at the sql level. If you want them, set
 # SET_DEFAULT to True
 SET_DEFAULT = False
 
 
+# backport fix for lgdb #6662663
+@monkeypatch(database._GenericAdvFuncHelper)
+def sql_create_index(self, table, column, unique=False):
+    idx = self._index_name(table, column, unique)
+    if unique:
+        return 'ALTER TABLE %s ADD CONSTRAINT %s UNIQUE(%s);' % (table, idx, column)
+    else:
+        return 'CREATE INDEX %s ON %s(%s);' % (idx, table, column)
+
+
 def rschema_has_table(rschema, skip_relations):
     """Return True if the given schema should have a table in the database"""
     return not (rschema.final or rschema.inlined or rschema.rule or rschema.type in skip_relations)
@@ -146,6 +158,9 @@
         rschema, attrschema = attrs[i]
         if attrschema is None or eschema.rdef(rschema).indexed:
             w(dbhelper.sql_create_index(table, prefix + rschema.type))
+        if attrschema and any(isinstance(cstr, UniqueConstraint)
+                              for cstr in eschema.rdef(rschema).constraints):
+            w(dbhelper.sql_create_index(table, prefix + rschema.type, unique=True))
     for columns, index_name in iter_unique_index_names(eschema):
         cols = ['%s%s' % (prefix, col) for col in columns]
         sqls = dbhelper.sqls_create_multicol_unique_index(table, cols, index_name)
@@ -198,7 +213,7 @@
     """write an attribute schema as SQL statements to stdout"""
     attr = rschema.type
     rdef = rschema.rdef(eschema.type, aschema.type)
-    sqltype = type_from_rdef(dbhelper, rdef, creating)
+    sqltype = type_from_rdef(dbhelper, rdef)
     if SET_DEFAULT:
         default = eschema.default(attr)
         if default is not None:
@@ -222,23 +237,19 @@
     return sqltype
 
 
-def type_from_rdef(dbhelper, rdef, creating=True):
+def type_from_rdef(dbhelper, rdef):
     """return a sql type string corresponding to the relation definition"""
     constraints = list(rdef.constraints)
-    unique, sqltype = False, None
-    for constraint in constraints:
-        if isinstance(constraint, UniqueConstraint):
-            unique = True
-        elif (isinstance(constraint, SizeConstraint)
-              and rdef.object.type == 'String'
-              and constraint.max is not None):
-            size_constrained_string = dbhelper.TYPE_MAPPING.get(
-                'SizeConstrainedString', 'varchar(%s)')
-            sqltype = size_constrained_string % constraint.max
+    sqltype = None
+    if rdef.object.type == 'String':
+        for constraint in constraints:
+            if isinstance(constraint, SizeConstraint) and constraint.max is not None:
+                size_constrained_string = dbhelper.TYPE_MAPPING.get(
+                    'SizeConstrainedString', 'varchar(%s)')
+                sqltype = size_constrained_string % constraint.max
+                break
     if sqltype is None:
         sqltype = sql_type(dbhelper, rdef)
-    if creating and unique:
-        sqltype += ' UNIQUE'
     return sqltype
 
 

--- a/cubicweb/server/sources/native.py	Wed Jun 22 17:13:10 2016 +0200
+++ b/cubicweb/server/sources/native.py	Mon Jun 20 17:59:43 2016 +0200
@@ -121,7 +121,7 @@
     """
     if not rdef.object.final:
         return dbhelper.TYPE_MAPPING['Int']
-    coltype = y2sql.type_from_rdef(dbhelper, rdef, creating=False)
+    coltype = y2sql.type_from_rdef(dbhelper, rdef)
     allownull = rdef.cardinality[0] != '1'
     return coltype, allownull
 

--- a/cubicweb/server/test/unittest_migractions.py	Wed Jun 22 17:13:10 2016 +0200
+++ b/cubicweb/server/test/unittest_migractions.py	Mon Jun 20 17:59:43 2016 +0200
@@ -186,7 +186,15 @@
             # test default value set on existing entities
             self.assertEqual(cnx.execute('Note X').get_entity(0, 0).shortpara, 'hop')
             # test default value set for next entities
-            self.assertEqual(cnx.create_entity('Note').shortpara, 'hop')
+            self.assertEqual(cnx.create_entity('Note', shortpara=u'hophop').shortpara, u'hophop')
+
+    def test_add_attribute_unique(self):
+        with self.mh() as (cnx, mh):
+            self.assertNotIn('unique_id', self.schema)
+            mh.cmd_add_attribute('Note', 'unique_id')
+            # test unique index creation
+            dbh = self.repo.system_source.dbhelper
+            self.assertTrue(dbh.index_exists(cnx.cnxset.cu, 'cw_Note', 'cw_unique_id', unique=True))
 
     def test_add_datetime_with_default_value_attribute(self):
         with self.mh() as (cnx, mh):

--- a/cubicweb/server/test/unittest_schema2sql.py	Wed Jun 22 17:13:10 2016 +0200
+++ b/cubicweb/server/test/unittest_schema2sql.py	Mon Jun 20 17:59:43 2016 +0200
@@ -65,13 +65,14 @@
 CREATE INDEX epermission_name_idx ON EPermission(name);
 
 CREATE TABLE Eetype(
- name varchar(64) UNIQUE NOT NULL,
+ name varchar(64) NOT NULL,
  description text,
  meta boolean,
  final boolean,
  initial_state integer REFERENCES entities (eid)
 );
 CREATE INDEX eetype_name_idx ON Eetype(name);
+ALTER TABLE Eetype ADD CONSTRAINT eetype_name_key UNIQUE(name);
 CREATE INDEX eetype_initial_state_idx ON Eetype(initial_state);
 
 CREATE TABLE Employee(
@@ -122,7 +123,7 @@
 CREATE TABLE Societe(
  nom varchar(64),
  web varchar(128),
- tel integer UNIQUE,
+ tel integer,
  fax integer,
  rncs varchar(32),
  ad1 varchar(128),
@@ -132,6 +133,7 @@
  ville varchar(32)
 , CONSTRAINT cstra0a1deaa997dcd5f9b83a77654d7c287 CHECK(fax <= tel)
 );
+ALTER TABLE Societe ADD CONSTRAINT societe_tel_key UNIQUE(tel);
 
 CREATE TABLE State(
  eid integer PRIMARY KEY REFERENCES entities (eid),