The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

The SYSIDX system view has a column "unique" that classifies the logical index as follows:

Indicates whether the index is a unique index (1), a non-unique index (4), or a unique constraint (2). A unique index prevents two rows in the indexed table from having the same values in the index columns

What does a value of 5 mean? An unique index with the new "CREATE INDEX ... WITH NULLS NOT DISTINCT" clause?

(At least that seems to be the case in my tests...)

asked 22 May '12, 07:58

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 23 May '12, 03:22

You are correct. The values are:

  • 1 - index is a unique index
  • 2 - index is for a UNIQUE constraint
  • 3 - reserved
  • 4 - index is not unique
  • 5 - index is WITH NULLS NOT DISTINCT

I will make sure that the documentation is modified appropriately. Thanks for reporting the oversight.

permanent link

answered 22 May '12, 17:29

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

I may be hairsplitting but for the docs, I guess the description for values 1 and 5 should be worded differently as both are for unique indizes, such as:

  • 1 - index is a unique index (not declared WITH NULLS NOT DISTINCT)
  • 5 - index is a unique index WITH NULLS NOT DISTINCT

(Apparently, that would only make sense for v12 and above...)

(23 May '12, 03:21) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 22 May '12, 07:58

question was seen: 872 times

last updated: 23 May '12, 03:22