CODEWITHSUNDEEP
constraintshanaunique-constraint
Dorad
Dorad

Reputation: 3713

Creating index with unique constraint for new blank field

A new column was added to an existing DB table (PA0023).

DB: HANA

The column should be unique, therefore i tried to create a unique index constraint via SE11.

SE11

Activation succeeded. However, while creating the index via Utilities... Database Utility, an error showed up:

Request: Create Index PA0023-Z01

...

sql:

CREATE UNIQUE INDEX 'PA0023~Z01' ON 'PA0023'
  ('MANDT',
   'RECORD_KEY')

cannot CREATE UNIQUE INDEX; duplicate key found [5] Several documents with the same ID exist in the index;SAPABAP1:PA0023.$uc_PA0023~Z01$ content not unique, cannot define unique constraint. rowCount != distinctCount.

There aren't rows with the same value filled in that column. There are rows with blank value, which are considered duplicates. After replacing blanks in development environment, index was created well. It's less possible in production, because there are many records with an empty value in that new field.

So my question is: Is there a way to create the unique constraint without replacing the blanks?

Upvotes: 0

Views: 1578

Answers (2)

Tony Marston
Tony Marston

Reputation: 57

While it is true that a compound primary key cannot contain any nullable columns it is permitted for a compound unique/candidate key to be defined with nullable columns. The only golden rule is that when adding or updating a record if any column in the unique key contains a NULL value then the index entry is NOT written to the database.

MySQL does this by default. SQL Server will do this provided that you add "WHERE columnX IS NOT NULL" to the key's definition. ORACLE is the same as SQL Server, except that the syntax is more complicated.

Upvotes: 0

Rob Lambden
Rob Lambden

Reputation: 2293

You cannot create a unique constraint if the existing data does not provide uniqueness. So no you can't do this if you have multiple NULL values for the key. You would need to ensure the data is unique before creating the constraint.

This is normal database practice, it's not HANA specific.

Upvotes: 1

Related Questions