hi,

I think it is not possible in OS, u need to create index with multiple attributes.

regards

No you can not store more than one null value in the column with unique index.

Although null values represent unknown values, when it comes to indexing, a null value is treated as being equal to other null values. Therefore, if a unique index consists of a single column, only one null value is allowed-more than one null value would violate the unique constraint. 

What you're looking for is indeed part of the ANSI standards SQL:92, SQL:1999 and SQL:2003, ie a UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values.

In the Microsoft world of SQL Server however, a single NULL is allowed but multiple NULLs are not...

There are some solutions but it is specific to databases like MySQL has some different solution than MSSQL

In OS you can do one thing create unique index with multiple columns and one column can have null value and another column should have unique value.

Hey, 

it is possible to create a unique index on an attribute while allowing NULL values. However, OutSystems' default unique index considers multiple NULL values as a violation.

Solution:

To allow multiple NULL values while ensuring uniqueness for non-null values, you can use a filtered index (if using SQL Server) or a partial index (if using PostgreSQL). However, OutSystems does not directly expose this in the platform UI, so you will need to use Advanced SQL queries in the database.

Workarounds:

1. Use a Database-Specific Unique Index (Advanced SQL)

   • If you are using SQL Server, create a filtered index:

     CREATE UNIQUE INDEX Unique_NonNull_AttributeON EntityTable(Attribute)WHERE Attribute IS NOT NULL;

   • If you are using PostgreSQL, use a partial index:

   • CREATE UNIQUE INDEX Unique_NonNull_AttributeON EntityTable(Attribute)WHERE Attribute IS NOT NULL;

   • This allows multiple NULL values while enforcing uniqueness for non-null entries.

2. Use Business Logic Validation

   • Before inserting or updating a record, check if the value already exists.
   • Example:
     • If NULL, allow insertion.
     • If NOT NULL, check uniqueness before inserting.

3. Use a Database Trigger (Not Recommended)

   • A trigger can enforce uniqueness while allowing NULLs, but it's less efficient.

Best Approach in OutSystems

OutSystems does not provide built-in support for filtered indexes, so business logic validation or Advanced SQL are the best options. If working with an on-premises database, you may be able to manually apply a filtered index.

Thank You