How to make composite unique index?

I understand it's possible to make a composite Primary Key by holding down
the control key and selecting multiple fields, then right-clicking and
selecting Primary Key. But I've heard that's not a good idea. What I want
to do is use table constraints to ensure only unique transactions - defined
by a combination of amount, date, account and customer - are entered in the
table while leaving the AutoNumber Transaction_ID the sole field in the PK
index. But how do I define a composite unique index?

Thanks in advance.

re: How to make composite unique index?

"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in
message news:BLZKd.17817$5R.16804@newssvr21.news.prodigy.c om...[color=blue]
> I understand it's possible to make a composite Primary Key by[/color]
holding down[color=blue]
> the control key and selecting multiple fields, then right-clicking[/color]
and[color=blue]
> selecting Primary Key. But I've heard that's not a good idea. What[/color]
I want[color=blue]
> to do is use table constraints to ensure only unique transactions -[/color]
defined[color=blue]
> by a combination of amount, date, account and customer - are entered[/color]
in the[color=blue]
> table while leaving the AutoNumber Transaction_ID the sole field in[/color]
the PK[color=blue]
> index. But how do I define a composite unique index?
>
> Thanks in advance.
>
>[/color]

You can use the GUI or SQL.

GUI:

--Open the Table in Design View.
--Click on the "key" symbol on the toolbar.
--In the dialog box that appears, name the index, and then select
which columns will be a part of it.
--Close the dialog box.
--Each column that was added into the index will now display a "key"
symbol at the leftmost of its row.


SQL

CREATE TABLE YourTable
(Amount LONG
,YourDate DATETIME
,Account LONG
,Customer LONG
,CONSTRAINT pk_YourTable PRIMARY KEY
(Amount
,YourDate
,Account
,Customer)
)


Sincerely,

Chris O.

re: How to make composite unique index?

> --Open the Table in Design View.[color=blue]
> --Click on the "key" symbol on the toolbar.
> --In the dialog box that appears, name the index, and then select
> which columns will be a part of it.
> --Close the dialog box.
> --Each column that was added into the index will now display a "key"
> symbol at the leftmost of its row.[/color]

hmmm... I think I understand what you meant. I'm using Access 2003, so
perhaps that accounts for the differences (?)

--Open the table in design view
--Click on the lightning bolt icon in the toolbar
--In the "Indexes" dialog enter an Index Name
--Select the fields that will be part of the index

The Indexes dialog will look like this:

Index Name . . . Field Name . . . Sort Order
MyIndex Amount Ascending
TxDate Ascending
Account Ascending
etc.

--In the Index Properties section (bottom) of the
dialog, Select "Yes" for Unique
--Close the dialog and close the table, saving on close
--No key icon will appear to the left of the table field

Is this correct?

re: How to make composite unique index?

"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in
news:ui_Kd.17822$5R.2749@newssvr21.news.prodigy.co m:
[color=blue][color=green]
>> --Open the Table in Design View.
>> --Click on the "key" symbol on the toolbar.
>> --In the dialog box that appears, name the index, and then
>> select which columns will be a part of it.
>> --Close the dialog box.
>> --Each column that was added into the index will now display
>> a "key" symbol at the leftmost of its row.[/color]
>
> hmmm... I think I understand what you meant. I'm using Access
> 2003, so perhaps that accounts for the differences (?)
>
> --Open the table in design view
> --Click on the lightning bolt icon in the toolbar
> --In the "Indexes" dialog enter an Index Name
> --Select the fields that will be part of the index
>
> The Indexes dialog will look like this:
>
> Index Name . . . Field Name . . . Sort Order
> MyIndex Amount Ascending
> TxDate Ascending
> Account Ascending
> etc.
>
> --In the Index Properties section (bottom) of the
> dialog, Select "Yes" for Unique
> --Close the dialog and close the table, saving on close
> --No key icon will appear to the left of the table field
>
> Is this correct?[/color]

Almost.

--In the Index Properties section (bottom) of the
dialog, Select "Yes" for Primary

--
Bob Quintal

PA is y I've altered my email address.

re: How to make composite unique index?

> --In the Index Properties section (bottom) of the[color=blue]
> dialog, Select "Yes" for Primary[/color]

But I already have Primary Key - the AutoNumber field that's the ID for the
table. That's the issue. I need a composite unique index to prevent dupe
transactions (same amount, same date, same account, same customerID), but
want a single field for a PK. What I described above seems to do the
trick - but a new question arises.

What about the CustomerID field - which is a Foreign Key? Should that have
another index applied separately? Also the date and account fields are used
extensively for searching - should those each have a separate index applied
as well? I assume the answer is yes. This makes for a heavily indexed
table, but AFAIK there is no problem with that.

re: How to make composite unique index?

"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:Hh2Ld.18051$wZ2.5181@newssvr13.news.prodigy.c om...[color=blue][color=green]
>> --In the Index Properties section (bottom) of the
>> dialog, Select "Yes" for Primary[/color]
>
> But I already have Primary Key - the AutoNumber field that's the ID for
> the
> table. That's the issue. I need a composite unique index to prevent dupe
> transactions (same amount, same date, same account, same customerID), but
> want a single field for a PK. What I described above seems to do the
> trick - but a new question arises.
>
> What about the CustomerID field - which is a Foreign Key? Should that
> have
> another index applied separately? Also the date and account fields are
> used
> extensively for searching - should those each have a separate index
> applied
> as well? I assume the answer is yes. This makes for a heavily indexed
> table, but AFAIK there is no problem with that.
>[/color]

That all seems OK to me. I would also have a separate index for the
CustomerID. The only question is whether you need same account and same
customer ID for the index. You don't if the account is unique to the
customer - but that might not be the case.

re: How to make composite unique index?

deko wrote:[color=blue][color=green]
>>--In the Index Properties section (bottom) of the
>> dialog, Select "Yes" for Primary[/color]
>
>
> But I already have Primary Key - the AutoNumber field that's the ID for the
> table. That's the issue. I need a composite unique index to prevent dupe
> transactions (same amount, same date, same account, same customerID), but
> want a single field for a PK. What I described above seems to do the
> trick - but a new question arises.
>
> What about the CustomerID field - which is a Foreign Key? Should that have
> another index applied separately? Also the date and account fields are used
> extensively for searching - should those each have a separate index applied
> as well? I assume the answer is yes. This makes for a heavily indexed
> table, but AFAIK there is no problem with that.[/color]

What is a primary key other than a unique index (not null) which the
dbengine uses as its primary (first) organizer? Can there be other
unique indexes, on one or more fields? Sure, there can.

The notion of "primary key" is neither sacred nor mysterious. I think it
might better be called the "default (unique) index".

"How many indexes?" is another question. There is a trade-off between
indexes and their uses, and the overhead that indexing requires. I have
read that in MS-SQL a primary key is the data. This puzzles me on one
hand, but seems entirely logical on the other, if an autonumber identity
column is the primary key.

You, as db designer, must decide where and when the advantages of
indexing out weigh the disadvantages.

I liked (maybe still do) older database engines where indexes were
external files, and could be applied or not applied, updated or not
updated, all-inclusive or subsets (index on lastname where
municipality='Amboy'), based on columns, functions or even columns in
other tables, or a combination there-of. Yes, you had to manage them
yourself, but this was OK with me.

--
--
Lyle
--
From ADO28.chm

Deprecated Components
Each of the following components is considered obsolete. While these
components are still supported in this release of the Microsoft® Data
Access Components (MDAC), they may be removed in the future. When
writing new applications, you should avoid using these deprecated
components. When modifying existing applications, you are strongly
encouraged to remove any dependency on these components.

ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the OLE
DB NET Data Provider.

Remote Data Services (RDS)
Remote Data Services (RDS) is a proprietary Microsoft mechanism for
accessing remote data across the Internet or intranet. Microsoft is now
shipping the Microsoft Simple Object Access Protocol (SOAP) Toolkit 2.0
that enables you to access remote data using an open, XML-based
standard. Given the availability of the SOAP Toolkit 2.0, you should
migrate from RDS to SOAP. The SOAP 2.0 Toolkit 2.0 also includes sample
code for remotely accessing Microsoft ActiveX® Data Objects (ADO)
Recordsets.

Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6. Microsoft has deprecated the Microsoft Jet
Engine, and plans no new releases or service packs for this component.
As a result, the Jet and Replication Objects (JRO) is being deprecated
in this release and will not be available in any future MDAC releases.

.....

re: How to make composite unique index?

> What is a primary key other than a unique index (not null) which the[color=blue]
> dbengine uses as its primary (first) organizer? Can there be other
> unique indexes, on one or more fields? Sure, there can.[/color]

I got some bad advice a while back that using a composite PK was the way to
enforce the type of constraint I described earlier. Now I realize that it's
best to have a single field PK where the data does not change. I was used
to just right-clicking a composite PK when I needed a unique constraint so I
had not used the Indexes dialog very much. Now I see the light...
[color=blue]
> You, as db designer, must decide where and when the advantages of
> indexing out weigh the disadvantages.[/color]

I've heard that if the field is used on the right side of a where clause in
a query (or a DLookup), it should be indexed. But if it's not, the only
downside is speed.

Thanks for the comments.

re: How to make composite unique index?

"Bob Quintal" <rquintal@sPAmpatico.ca> wrote in message
news:1107066088.845a2965d99739cf9c8af20572b06af5@t eranews...[color=blue]
> "deko" <www.clearpointsystems.com@use_contact_form.com> wrote in
> news:ui_Kd.17822$5R.2749@newssvr21.news.prodigy.co m:
>[/color]

<snip>
[color=blue][color=green]
> > --No key icon will appear to the left of the table field
> >
> > Is this correct?[/color]
>
> Almost.
>
> --In the Index Properties section (bottom) of the
> dialog, Select "Yes" for Primary[/color]

D'oh!

I forgot one step . . . that one. :(


Sincerely,

Chris O.

re: How to make composite unique index?

>>>I understand it's possible to[color=blue][color=green][color=darkred]
>>>make a composite Primary Key[/color][/color][/color]
[color=blue]
>But I already have Primary Key >- the AutoNumber field that'sthe >ID[/color]
for the[color=blue]
>table. That's the issue.[/color]

You asked for primary key ;-)

You can have several unique indexes, as candidates for the primar keyy.
The choice to use a natural (multi-field) key or a surrogate
(autonumber) one is entirely up to you. If you want the autonumber use
it. I write code that may get updated by somebody else. It is more
intuitive to see the real key as primary. If you are the only developer
in your group, it's irrelevant.

re: How to make composite unique index?

> You asked for primary key ;-)

My mistake, perhaps I was not clear...
[color=blue]
> You can have several unique indexes, as candidates for the primar keyy.
> The choice to use a natural (multi-field) key or a surrogate
> (autonumber) one is entirely up to you. If you want the autonumber use
> it. I write code that may get updated by somebody else. It is more
> intuitive to see the real key as primary. If you are the only developer
> in your group, it's irrelevant.[/color]

10-4