This project has moved. For the latest updates, please go here.

Unique indexes not picked up?

Aug 5, 2014 at 9:35 PM
Hi,
I have a table with a primary key and a unique index on it. The schema reader picks up the PK, but does not recognize the unique key. I am using MSSSQL 2012. I checked IsUnique and also used the method DatabaseIndex.IsUniqueKeyIndex(<table>).

The DDL is as follows:

CREATE TABLE [dbo].[Customers](
[Id] [int] NOT NULL,
[FirstName] [varchar](15) NOT NULL,
[MiddleInitial] [varchar](1) NULL,
[LastName] [varchar](20) NOT NULL,
[Age] [int] NULL,
[Description] [text] NULL,
[Company] [varchar](30) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX [UNQ_Customers_Name] ON [dbo].[Customers]
(
[FirstName] ASC,
[MiddleInitial] ASC,
[LastName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Any suggestions?
Thanks a lot,
Alessandro
Coordinator
Aug 6, 2014 at 7:29 AM
Edited Aug 6, 2014 at 7:30 AM
The SQLServer indexes collection doesn't include unqiueness.
Unique constraints are a separate collection, so they work fine. Unique indexes have a "unique" flag on other databases, but apparently not on SqlServer.
Looks like I'll have to customize the collection.
Aug 6, 2014 at 12:42 PM
Just in case it matters, I just checked the UniqueKeys collection and it is empty.
I was hoping to do a quick name lookup in there...
Aug 6, 2014 at 1:37 PM
I am also having the same problem with the Oracle managed provider (index "UNQ_CUSTOMERS_NAME" below).

The DDL is this:


CREATE TABLE "TEST"."CUSTOMERS"
( "ID" NUMBER(9,0) NOT NULL ENABLE,
"FIRSTNAME" VARCHAR2(15 BYTE) NOT NULL ENABLE, 
"MIDDLEINITIAL" VARCHAR2(1 BYTE), 
"LASTNAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
"AGE" NUMBER(9,0), 
"DESCRIPTION" CLOB, 
"COMPANY" VARCHAR2(30 BYTE), 
 CONSTRAINT "PK_CUSTOMERS" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TESTDATA" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TESTDATA"
LOB ("DESCRIPTION") STORE AS BASICFILE (
TABLESPACE "TESTDATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;


CREATE INDEX "TEST"."IX_PERSONS_FIRSTANDLAST" ON "TEST"."CUSTOMERS" ("FIRSTNAME", "LASTNAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TESTDATA" ;


CREATE UNIQUE INDEX "TEST"."UNQ_CUSTOMERS_NAME" ON "TEST"."CUSTOMERS" ("FIRSTNAME", "MIDDLEINITIAL", "LASTNAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TESTDATA" ;
Coordinator
Aug 6, 2014 at 2:27 PM
I've got a fix for the SqlServer indexes, coming shortly.
The Oracle.ManagedDataAccess.Client is working for me, but maybe it's because I've got the SqlServer fix :)