How to prevent duplicates of non-primary key

[Fran B.]

I am using Access as a front-end to an SQL database. I need to be able to
prevent having duplicate keys without using a composite primary key.

I have a table that allows an employee to have multiple skill sets:
SkillID - as my primary unique ID field
EmpName - relates back to Employee table
Skill - points to table of valid skill sets

I can validate the values in EmpName and Skill, either of which can occur in
this table 'many' times. However, I want to make the combination value of
EmpName + Skill unique. This could be done by making them a composite
primary key but that is causing me other problems.

Example data:

Key EmpName Skill
1 Joe Blow Cobol Programming
2 Joe Blow DBA
3 Sam Smith DBA

It shouldn't let me add another:

4 Sam Smith DBA

I appreciate any help you can offer.

 

[Stefan Hoffmann]

hi Fran,

I can validate the values in EmpName and Skill, either of which can occur in
this table 'many' times. However, I want to make the combination value of
EmpName + Skill unique. This could be done by making them a composite
primary key but that is causing me other problems.

You don't need a primary key for that. A unique index consisting of
these two fields is sufficent as far as I understand your problem.



mfG
--> stefan <--

 

[Jeff Boyce]

Fran

First, using "EmpName" as a key is fraught with danger ... a scan of this
(and other) newsgroups will reveal MANY instances when two different
individuals share the same name. I know of one fellow at my work that
shares both name AND birthday with someone else.

If you have fields in a table you wish to be unique, open the table in
design view and create a "unique" index on the combination of fields.

Also note, if your "Skill" is actually a text skill, you may be saving more
than you need to. Your "Skills" table could consist of an ID field and a
SkillName field, requiring that you only store the SkillID, not the full
SkillName.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[tedmi]

Your requirement is that the combination of EmpID and SkillID be unique.
That, by definition, is what a unique compound index does - there is no other
way. Just what problems are you having with compound keys? BTW, it is not
necessary to have the compound key be primary, although that is the most
efficient way. You could have SkillID as primary (preferably generated via
Autonumber), and the compound EmpID-SkillID a unique but non-primary index.
Incidentally, you should not be using EmpName, because names are not unique.
Instead, assign each employee an ID (most businesses have already done that,
or use SSN).

 

[Fran B.]

I received three replies all basically telling me the same thing - create a
"unique" index on the combination of the two fields. Apparently, I can do
this in Table Design, but I can't figure out how.

 

[Fran B.]

I received three replies all basically telling me the same thing - create a
"unique" index on the combination of the two fields. Apparently, I can do
this in Table Design, but I can't figure out how.

 

[Fran B.]

I received three replies all basically telling me the same thing - create a
"unique" index on the combination of the two fields. Apparently, I can do
this in Table Design, but I can't figure out how.

 

[John W. Vinson]

I received three replies all basically telling me the same thing - create a
"unique" index on the combination of the two fields. Apparently, I can do
this in Table Design, but I can't figure out how.

Open the table in design view. Select the Indexes tool from the toolbar (looks
like lightning hitting a datasheet, which would be a nice idea some days....)

Put a distinctive name for an index in the left column (uniSale for example);
put the first field of the desired index adjacent to it in the right column.
Put the second field in the right column on the next line down (and so on for
up to ten fields if needed).

Check the Unique checkbox on the lower left of the popup window.

Close the index tool and save the table. You'll get an error message at this
point if there are already records in the table which violate the uniqueness
criterion; you'll need to fix them first before creating the index.

John W. Vinson [MVP]

 

[Fran B.]

My lightning bolt on a spreadsheet says "New Object: Autoform" and the drop
down does not list Indexes. I am using MS Access 2002 project to update an
SQL database. Does this make a difference?

 

[Geo]

My lightning bolt on a spreadsheet says "New Object: Autoform" and the drop
down does not list Indexes.

Wrong tool button - that is lightning+form. Look for another button with
lightning. Tool tip should say "indexes". Mine is just to the right of the
Primary Key button.

Geo

 

[Jeff Boyce]

Open table def in design view.

Click on the Indexes button.

In the window (below the list of fields), enter a name for the index.
Select the first field in the index. Go to the next line. DO NOT put
another name in ... just select the second field for the index.

Select the first row (the one with the index name). Change the Unique
property to Yes.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[John W. Vinson]

I am using MS Access 2002 project to update an
SQL database. Does this make a difference?

Yes. You must create indexes *IN SQL Server*, either using SQL Management
Studio or a DDL query such as

CREATE UNIQUE INDEX idxXYZ ON...

See the online help for CREATE INDEX.

John W. Vinson [MVP]

 

[Jamie Collins]

Your requirement is that the combination of EmpID and SkillID be unique.
That, by definition, is what a unique compound index does - there is no other
way.

In software development there is rarely "no other way" e.g. some
things that spring instantly to my mind:

1) A UNIQUE CONSTRAINT.
2) A trigger.
3) Front end code (though personally I wouldn't recommend this alone).

Jamie.

--

 

[tedmi]

On the index design grid, enter a name for your index in the first column,
and select one of the fields for the second.
On the next line of the grid, leave the name column blank, and select the
other field of your compound index.
Now at the bottom of the grid, set the index to unique, and optionally to
Primary, if you remove the other redundant single-field primary key.

 

[tedmi]

1. Most databases implement a UNIQUE constraint with an index.
2. The Jet DB engine does not support triggers
3. Agree with you that UI code is inappropriate