473,729 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.
Nov 13 '05 #1
10 16515

"deko" <www.clearpoint systems.com@use _contact_form.c om> wrote in
message news:BL******** **********@news svr21.news.prod igy.com...
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.


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.
Nov 13 '05 #2
> --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.


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?

Nov 13 '05 #3
"deko" <www.clearpoint systems.com@use _contact_form.c om> wrote in
news:ui******** *********@newss vr21.news.prodi gy.com:
--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.


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?


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.
Nov 13 '05 #4
> --In the Index Properties section (bottom) of the
dialog, Select "Yes" for Primary


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.
Nov 13 '05 #5

"deko" <www.clearpoint systems.com@use _contact_form.c om> wrote in message
news:Hh******** **********@news svr13.news.prod igy.com...
--In the Index Properties section (bottom) of the
dialog, Select "Yes" for Primary


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.


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.
Nov 13 '05 #6
deko wrote:
--In the Index Properties section (bottom) of the
dialog, Select "Yes" for Primary

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.


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='A mboy'), 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.

.....
Nov 13 '05 #7
> 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.
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...
You, as db designer, must decide where and when the advantages of
indexing out weigh the disadvantages.


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.
Nov 13 '05 #8

"Bob Quintal" <rq******@sPAmp atico.ca> wrote in message
news:1107066088 .845a2965d99739 cf9c8af20572b06 af5@teranews...
"deko" <www.clearpoint systems.com@use _contact_form.c om> wrote in
news:ui******** *********@newss vr21.news.prodi gy.com:


<snip>
--No key icon will appear to the left of the table field

Is this correct?


Almost.

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


D'oh!

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

Chris O.
Nov 13 '05 #9
>>>I understand it's possible to
make a composite Primary Key
But I already have Primary Key >- the AutoNumber field that'sthe >ID for thetable. That's the issue.


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.

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2835
by: Mike Coppinger | last post by:
I have a problem that has raised a couple of questions. SITUATION: I have a table called pr_persona that has a composite key comprising pr_persona_db CHAR(2) pr_persona_id INT(11) auto increment On a separate table, rv_reservation, I have a foreign key that links to pr_persona as follows rv_reservation.rv_agent_id VARCHAR(13)
5
14951
by: onder | last post by:
i have a master table with around 15 columns and i am trying to find the appropriate primary keys and indexes for the table. To make the records unique, i need to include two datetime columns ( start and end dates ) and two integer columns ( attributes of the record ) to make up a composite primary key. Both of these four columns are found in the WHERE clause of my queries. Is it acceptable in the view of performance and how should i...
4
25123
by: Peter Scott | last post by:
I created a table that has a column in that needs to contain a full Unix file path. Since 2048 was too long for a VARCHAR, I made it TEXT. I since populated the table. Now I want to make the path column a primary key, and I can't figure out how. (I googled the web and groups without luck, looked over the reference manual also, especially reading the entry on BLOBs.) I was able to make a fulltext index with: create fulltext index...
5
6927
by: John | last post by:
Specifically for joint tables... tblStudents tblClasses tblClasses_Students Is it be good programming to use a composite primary key in tblClasses_Students (where the key is ClassID and StudentID)? I enventually will convert the Access db to an SQL db. I've always used single primary keys based on long integers, usually autonumbered.
18
12660
by: Thomas A. Anderson | last post by:
I am a bit confused in creating a composite primary key. I have three table with two of the tables containing primary keys. I have two of the tables (each with a primary key) having one to many relations with the table containing no primary key. Do I just create two primary keys on the table that does not contain any primary key for this to become a composite primary key? Thank you in advance! Still a newbie,
2
2235
by: Peter Lindquist | last post by:
I had a fun issue this morning, and now I'm wondering if I violated a rule I didn't know about. Any insight would be much appreciated. All operations involving this particular table may be assumed to have functioned normally before today. bash-2.05a$ cat PG_VERSION 7.3 Last night, we added 'index_t_payment_param' to this table. payment_id is not a unique entry, hence the composite primary key, and I've since
1
1658
by: lists | last post by:
When using the (tbl).field notation for selecting a specific field from a composite field then the query returning the field is executed once per field. An example is giving below. The runtime is directly related to the number of fields accessed. The following tests is done with seqscan disabled - but I have confirmed the exact same behavior with seqscan enabled. Another side effect of this is the case where f_all_acl() actually...
13
2922
by: Baihao Yuan | last post by:
Hi, I created a composite index (lastname, firstname). I know the following queries will use this index: WHERE lastname = ... WHERE lastname = ... AND firstname = ... Also this won't use the index: WHERE firstname = ...
14
5035
by: dave.dolan | last post by:
Basically I'd like to implement the composite design pattern with leaves that are either of reference or value types, but even using generics I can't seem to avoid boxing (using ArrayList or Object) Is this even possible, or is the composite pattern doomed to use the System.Object type forever? I have tried using interfaces with generics, but I always stumble on the Value property (when trying to return the value of a particular node)
0
8911
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9280
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9200
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8140
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6015
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4525
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3238
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2673
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2162
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.