I'm a bit new to PL-SQL coming from T-SQL.

I have a requirement that only one phone number is allowed per user ID, but the phone number column can be null as many times as required.

So table is:

It's that last one I need to block, although the first three are fine. In this case I'm talking about the sample table I've posted, not the actual table order. I just need to allow the NULLs through but block if there are duplicate phone numbers per a given User ID.

I've read about functional indexes but not sure exactly how to apply them here.

CodePudding user response：

CREATE UNIQUE INDEX my_index ON my_table (
  CASE WHEN phone_number IS NULL THEN NULL ELSE user_id END,
  phone_number
)

With this logic, if phone_number is NULL, then both values in the index will be NULL, so that row will be excluded from the index. If phone_number is not NULL, then the row will be included in the index with the actual values for user_id and phone_number, and uniqueness will be enforced.

P.S. This is not "PL/SQL", it is Oracle SQL. PL/SQL is the procedural language used to write such things as triggers, functions, etc.