Does Access support the concept of constraints or some equivalent? Like maybe you have a single AutoNumber primary key for a row but it also has fields like Product and Color. Product might be Bike, Color might be red. You only want one entry in the table with a value of Bike/Red. One way to deal with this would be to drop the primary autonumber field and create a compound key.

I believe in SQL Server you can create a contstraing for these two fields. Is there some equivalent in Access 2010?

Try to avoid compound keys... they are evil!
You can create a combined index for that purpose.
Indexes are great, but use them wisely.
I'd highly recommend you to read the documentation about indexes first.
Regards,
Diego

I'd keep the autonumber and add the two fields as a compound index, set to no duplicates.

Cybercow

It sounds like this is effectively a Constraint by another name. Y/N?

I am creating a Junction table and my instincts tell me that even the Junction Table should have a primary key, but I dont want the two foreign keys to get duplicated either. In SQL Server there is a Constraint which sounds like it solves the problem. In Access it sounds like a compound index (no duplicates) does the same.

Dipetete

I've always suspected that Compound keys were evil but have not been able to exaplain to myself why?

Why?

Being very simplistic 2 main things come to my mind (it can be others)
-. Maintenance
-. Usability
You can try for example
-. Create a table with a compound primary key and relate it in a one to many relationship with another table.
-. Build a form and a subform with these tables.
-. Input some records in both your form and subform.
Sometimes it gets tricky to make it work.
In fact, I always leave as a primary key the natural autonumber field that access puts.
Access loves autonumbers for primary keys!
I had troubles with a number field as primary key once and took me a lot of time to fix it.
Well I would like to give you more examples, but my english is a little bit rusty.
But you can do some research in these topics.
I'm pretty sure this question is very common and you will find great discussions on the theme.

On the other hand, the compound index will do what you want to do, and since you can't get duplicates, the primary key will serve well for your junction tables.
Regards,
Diego

Compound Keys are indeed a bother. Compound Indexes however, are bit more friendly; still I would employ a good error trapping routine to top it off.
And yes, "Constraint" by virtue of the data model is what it summarizes to.

>> Does Access support the concept of constraints or some equivalent? <<

Most definately.

CONSTRAINT Clause

>> am creating a Junction table and my instincts tell me that even the Junction Table should have a primary key, but I dont want the two foreign keys to get duplicated either. <<

You seem to be making the assumption that a primary key cannot be compound? With junction tables, I consider it quite a legitimate design practice to have a compound primary key index. Especially if the junction table will not have any child tables. If the junction table does not have any child tables, and you create a surrogate primary key index with one field (ie: an autonumber field declared as the primary key), then you are just consuming disk space needlessly with the extra column and the primary key index.

Nice Brent! (as usual)

PS: Well, from my point of view this is the only case in which compound primary keys don't bother... when they are not performing any other task than serving as a junction.
I didn't know the constraint clause in Access so thanks for the reference.
If the junction table had child tables I won't use compound primary keys... I'll stick to the autonumber field and a compound index.

Thanks dipetete, twas nothing really! ... we had some fun with the CONSTRAINT clause a while back with this thread.

>> If the junction table had child tables I won't use compound primary keys... I'll stick to the autonumber field and a compound index. <<

I've done both. Either way worked fine for me -- yes even with child tables -- but I definately sympathize with the sentiment!

Nice post!
I still need to get more level to participate in that kind of discussions but surely I will.
It seems to me that I can avoid the use of WorkSpaces in some circumstances by using Constraints and CHECK... I'll have to play with it a little bit but definitely it is a great tool!

Well I bet you can make everything work just fine just by using the Shell!
But for apprentices like me, is tricky and probably dangerous (in a productive environment) to use that approach.
Regards,
Diego

WSplawn,

Composite keys (meaning keys consisting of more than one attribute) are not something to be avoided any more than simple (single attribute) keys are. They are a critically important part of database design and data integrity.

A "unique index" is just a method of implementing a composite key - not necessarily much different to any other method of implementing keys. The important distinction to be clear about is that a key is a set of attributes which is irreducibly unique and non-nullable; an index or constraint is a DBMS feature that enforces the uniqueness of those attributes. It's best to avoid mixing up the terminology of keys and indexes - as already seems to have happened in this thread. They are different concepts, different concerns and one is in no way a substitute or alternative for the other.

Uniqueness and identification of data are obviously extremely important to any user of a database so it's important that all the desired keys of a table are correctly enforced. Very often that will include composite keys. I'd say that an accurate data model of almost any non-trivial business process is pretty certain to require a composite key somewhere. Composite keys are therefore a force for good, not "evil".

Unfortunately a lot of the apparent criticism you may hear or read about composite keys is based on confusion about what keys actually are. If you read such criticisms carefully the confusion is usually obvious: the critics of composite keys will often start by saying "avoid composite keys" but then go on to say "enforce uniqueness with an index"!

Where you do have otherwise equivalent alternatives of using a constraint-based or an index-based method for keys it is probably a good idea to prefer the constraint method over the index method. Constraints tend to be more industry standard (the ISO SQL standard covers constraints but not indexes) and more likely to be supported by developer tools and data modelling tools. Unfortunately, DBMS features differ so much that it is hard to give absolute rules. There is no substitute for knowing the features and limitations of your DBMS.

Adding a compound Index seems to do the job and is easy to do - no remembering syntax that I don't use regularly. Since this seems to work is there any real reason for doing it with a constraint instead?

Think of it this way ... a UNIQUE INDEX (composite key or not) IS a constraint! So, in Access, a method for defined the type of constraint you want is via a UNIQUE INDEX.

>Think of it this way ... a UNIQUE INDEX (composite key or not) IS a constraint! So, in Access, a method for defined the type of constraint you want is via a UNIQUE INDEX.

To my way of thinking, that's the best way to look at it. But, did I misuderstand earlier postings. They seemed to indicate that a 'formal" constraint can be created on an Access BE if you use syntax at the prompt level. Even if that be true, the question to me was why?

>> To my way of thinking, that's the best way to look at it. <<

Well, maybe I should have reworded my statement ...

From ...
"Think of it this way ... a UNIQUE INDEX (composite key or not) IS a constraint!"

To ...

"A UNIQUE INDEX (composite key or not) IS a constraint!"

In other words, you are creating a formal constraint on your table by creating a UNIQUE INDEX through the Access UI.

As you see I'm just aware about the use of Constraint in msaccess, thanks to Brent.
I think one can see an index as an indexed constraint, and a Constraint as a non-indexed constraint.
Please correct me if I'm wrong.
Regards,
Diego

Thank you all for your help. This has given me a great deal of clarification.

>> I think one can see an index as an indexed constraint, and a Constraint as a non-indexed constraint. <<

Well, if I understand things correctly, a UNIQUE CONSTRAINT builds a UNIQUE INDEX in order to enforce the UNIQUE CONSTRAINT, however, I think the INDEX that is build is hidden. So, the difference between the two is that one is a hidden, and one is not.

Yeah you're right Brent.
I read this article and some others and all point to the same http://msdn.microsoft.com/en-us/library/aa...=sql.80%29.aspx