Hi everyone,
I'm facing an issue with a UNIQUE constraint in OutSystems (Oracle database). When I try to insert a record with Code = "Pelotão" and another one with Code = "Pelotao", I get this error:
ORA-00001: unique constraint (OSADMIN_OSDEV1.OSIDX_OSUSR_9JY_BASELIST_DT3QT) violated
However, these values are visually different (one has an accent).
Does Oracle treat these strings as equal due to accent-insensitive collation? How can I configure the entity or the index to allow both values?
Thanks!
Set the column to an accent-sensitive collation such as BINARY or any other that distinguishes between accented and non-accented characters. After altering the collation, rebuild the unique index to enforce the new behavior.
Hi,
In short - yes. Same thing with SQL server. Assuming you are running on-premise, here's some additional information on handling Oracle national language support quirks: https://success.outsystems.com/support/enterprise_customers/maintenance_and_operations/configuring_linguistic_sorting_in_an_outsystems_environment_using_an_oracle_database/
Excerpts from above documentation:"Default string sorting algorithm (NLS_SORT) for Oracle used by the platform is BINARY_AI, which is accent insensitive."
and
"Starting with Platform Server 11.7.0, OutSystems system administrators can change the linguistic sorting configuration (the value of the NLS_SORT parameter) of the Oracle database from BINARY_AI (the default value) to BINARY_CI on the OutSystems platform database. Changing this configuration requires you to republish applications for the setting to become active."
If I've understood correctly, those excerpts explain how sort parameter is affecting to queries, but are also pointing to case and accent insensitive direction. Again, if I've understood correctly, inserting / updating has more to do with table/db charsets. My experience on Oracle databases with OutSystems is quite limited, but DB character set can and probably will also affect your results. If query defaults are as explained above, it would make no sense to set any other kind of default charsets to actual tables.
You can check what charset(s) you are using by querying the database:select * from nls_database_parameters where parameter like '%CHARACTERSET';
Hi
Open Data → Entities in Service Studio.
Select your entity (e.g., BaseList).
Select the Code attribute.
In the right-side properties, uncheck “Is Unique”.
If there’s an index defined as “Unique” on Code, open the Indexes section and:
Edit the index
Uncheck “Is Unique” you can follow this steps