U++ forum: Welcome to the forum

Re: SQL : how to prevent duplicates ? [message #42378 is a reply to message #42377]

Mon, 10 March 2014 21:43

dolik.rce
Messages: 1791
Registered: August 2008
Location: Czech Republic

Ultimate Contributor

Hi jibe,

Unique index can be made simply with UNIQUE:

TABLE_(CUSTOMERS)
   INT_  (CODE) PRIMARY_KEY
   STRING_  (LASTNAME, 40) UNIQUE
   STRING_  (FIRSTNAME, 40)
END_TABLE

It should work for most of the supported engines, including sqlite, I believe.

For two unique columns, there is DUAL_UNIQUE:

TABLE_(CUSTOMERS)
   INT_  (CODE) PRIMARY_KEY
   STRING_  (LASTNAME, 40)
   STRING_  (FIRSTNAME, 40) DUAL_UNIQUE(FIRSTNAME, LASTNAME)
END_TABLE

If you need even more columns to be unique, there is UNIQUE_LIST(u, l), where u stands for identifier and l is list of columns. I never used it but I think it should work like this:

TABLE_(CUSTOMERS)
   INT_  (CODE) PRIMARY_KEY
   STRING_  (LASTNAME, 40)
   STRING_  (FIRSTNAME, 40)
   STRING_  (CITY, 40) UNIQUE_LIST(address_key, FIRSTNAME, LASTNAME, CITY)
END_TABLE

If you have some troubles, it might be a good idea to check the schema of the created table, from command line (or other) client. Sometimes there are subtle bugs that prevent some parts of the schema to be created properly, and they're simply missing. Also any error messages and or logs might tell you if there is any hidden problem. Hope that helps Wink

Best regards,
Honza

Report message to a moderator