The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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
30.9k310457668
accept rate: 33%

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
10.7k571104
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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×412
×81
×26
×24

question asked: 22 May '12, 07:58

question was seen: 976 times

last updated: 23 May '12, 03:22