Oliver Peters wrote: > I can't use UNIQUE CONSTRAINTs because of strange behaviour in my frontend > (reason is assumably in the ODBC-driver). The PK is already used for an > autoincrement column. > > Is there another way to enforce UNIQUE CONSTRAINTs despite INSERT/UPDATE > triggers? I'd prefer CHECK-CONSTRAINTs but don't see a way to formulate > something like "if the string you want to insert/update is already present in > the table you are not permitted to insert/update".
What efforts have you made in trying to fix the front-end instead? If the front-end complains about doing something as common and proper as using UNIQUE constraints (which are the best solution for the job), then I wouldn't be surprised if its also going to give you trouble with other reasonable things you'd want to do. Are you having problems with all UNIQUE constraints or just some of them? Perhaps the difference is whether or not the column in question has nulls in it. I found from experience, years ago when using ODBC to access an Oracle database, that sometimes ODBC had a fit with some things that Oracle itself handled fine, as if ODBC was implicitly doing some of its own constraint enforcement that was stricter or different than Oracle's, and so certain changes to the database might result in ODBC producing errors when simply trying to read from a table and one had to change the data in Oracle to something that met ODBC's stricter requirements. Maybe your problem is related to this? The problem I faced was years ago, but it might have had to do with nulls, or perhaps a multi-column foreign key where one column was null and the other not, but I'm not sure. Check your nulls. An alternate thing you could do is split your tables so each column you want unique is in its own table and then you can make that a primary key. Not that I actually advise this since then you're just gaining a new problem or two in place of the one you lost, such as ensuring there's not more than one row in the other table per row in the parent. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
