Create CONSTRAINT (not unique)

[dw85745]

How do you create a CONSTRAINT (index) for Access in SQL that is NOT:
1) a PrimaryKey
2) or UNIQUE

In other words I want a DateTime Index where the date may appear more than once.

============
These work:

& "CONSTRAINT PrimaryKey PRIMARY KEY (fldID)), " _
& "CONSTRAINT idxDateTime UNIQUE (fldDateTime));"


==============================
This fails:

& "CONSTRAINT idxDateTime (fldDateTime));"

[GaryMazzone]

Just create a standard index. If it is not a PK and not Unique then just a simple index is what you want. (Create Index statement)

[dw85745]

Thanks for response Gary:

Trying to create a table and put the constraint on at the same time without having to run a separate Create Index statement. Anyway to do this and get my index?

[corwin_ranger]

Quite simply, you don't create a constaint. You create an index. The key to understand here is that when you create a PRIMARY KEY or UNIQUE constraint, an index gets created as well, but the indexes and the constraints are NOT the same thing.

You can have 3 types of indexes, unique, clustered and nonclustered. A unique index is exactly as it sounds and index where all values must be unique. Creating a unique index is exactly the same as creating a unique constraint. They both create a constraint AND an index. A clustered index is one where the data in the table is physically stored in the order of the index. There can only be one of these on a table. By default, if you don't specify otherwise, your primary key will be the clustered index on that table. Finally the nonclustered index does not store data in the order of the index and you can have any number of these in theory.

So, in the scenario you describe, you would want to :

CREATE INDEX IDX_DateTime ON TableName(FieldName)

This may be more than you need, but I figured it might help someone else who's reading the thread.

Hope it helps,

Steve

[corwin_ranger]

Not to my knowledge, but you can just run the CREATE INDEX commands as part of the same batch immediately after the code that creates the table.

Steve

[dw85745]

Corwin_Ranger

Good explanation.

Find it interesting you can create a "constraint" but not an "index" at the same time table is being created.

Thanks for response and explanation.

[corwin_ranger]

Actually it makes sense. A constraint is really part of the table. A constraint is really just a rule about the data that can be contained in the table. That's why you either create it along with the table or create it after the fact with an ALTER TABLE statement. There is no CREAET CONSTRAINT command.

In the case of an index, it's actually a separate entity. In most cases, it's even a separate file. Even when you create a constraint that is also an index (e.g. Primary Key), two objects are created. The constraint itself is created as part of the table. Then, behind the scenes, the index is create after the fact.

It isn't obvious because they overlap some, but the distinction is there.

Steve