Distinct Row - Based on two fields

Question

Is there a way to have a table definition that bases its Non-Duplication on two fields?

By this i have 3 columns gid, cid, price

Criteria:

There can only be 1 cid for 1 gid
There can be multiple cid's as long as there are multiple gid's and criteria 1 is not violated

I basically want to have the restriction in the Table Design, if possible. Not sure if its possible just figured i would ask.

Edit (2010-08-27 11:18am CST)

Ok a little clarification, i have two indexes as well, one on gid and other is on cid, can i have the two indexes determine Uniqueness? The table is basically a Join table since the two tables drawn in have a 1-Many relationship, in so far as their counterpart is unique to its id.

Table/Indexes declaration:

gid {Numeric:Long} (Primary: No, Unique: No, Ignore Nulls: No) [FK]
cid {Numeric:Long} (Primary: No, Unique: No, Ignore Nulls: No) [FK]
price {Numeric:Single}

Usage:

cid 1-Many gid
- cid is derived from tblCat (as the PK)
gid 1-Many cid
- gid is derived from tblGrp (as the PK)

If you are asking can you have a unique index based on two field, yes, you can. You can even have nulls as long as it is not primary. — Fionnuala, Aug 27 '12 at 16:07
Added more detail, still getting used to the new (to me) Access design. — GoldBishop, Aug 27 '12 at 16:23
Are you asking how to create an index using two fields in MS Access? Existing indexes are not relevant. — Fionnuala, Aug 27 '12 at 16:25
Are you talking about a composite primary key? If so, hold shift or ctrl in design mode. Highlight all the relevant fields, then click the primary key button this will give the appearance of multiple primary keys. — Daniel, Aug 27 '12 at 17:04
I remember being able to do composite keys in another DBMS (cant remember which one) but it allowed me to restrict a distinct row entry based on two or more fields, similar to doing a PK/FK restriction. I am currently doing the restriction through SQL Statements but would be alot easier if i could locate the restriction at the Table level instead of various points in VBA. I'll try the suggestion by `Daniel` and see if get the restriction needed. — GoldBishop, Aug 27 '12 at 17:37
If you use @ in front of a user name, they will receive notification and be able to respond. — Fionnuala, Aug 28 '12 at 14:27

score 2 · Accepted Answer · answered Aug 28 '12 at 14:26

2

There is no difficulty in creating multi-field indexes in MS Access. You can simply click the indexes button (lightning flash) and choose what you need.

enter image description here

answered Aug 28 '12 at 14:26

Fionnuala

90,370
7
114
152

Never knew you could do that with the PK, to create a Constraint from multiple fields, in Access. – GoldBishop Aug 29 '12 at 18:11
How to use the composite in a relationship http://stackoverflow.com/questions/11730619/reference-to-composite-primary-key-in-access-2007/11737106#11737106 – Fionnuala Aug 29 '12 at 19:11
I am having so much fun with this found ability in Access. My fun has JUST BEGUN :) – GoldBishop Aug 30 '12 at 17:58

score 1 · Answer 2 · answered Aug 27 '12 at 18:36

Your first bullet point is ...

There can only be 1 cid for 1 gid

But later you said ...

gid 1-Many cid

So I'm unsure what you want. If this data set represents acceptable sample values ...

... I created that table using the procedure below. The primary key is a composite index on gid and cid. That prohibits Null values for those fields, and ensures only unique pairs of values may be stored in the table.

One gid value may be paired with multiple different cid values. And one cid may be paired with multiple different gid values.

If this is not what you're after, please clarify what you want instead. If you have a need for an additional index for cid only, you can add one ... it won't interfere with the primary key index.

Here's the procedure I used.

Public Sub CreateTable_GoldBishop()
    Const cstrTable As String = "tblGoldBishop"
    Dim cn As Object
    Dim strSql As String

    Set cn = CurrentProject.Connection
    strSql = "CREATE TABLE " & cstrTable & "(" & vbCrLf & _
        "gid INTEGER," & vbCrLf & _
        "cid INTEGER," & vbCrLf & _
        "price SINGLE," & vbCrLf & _
        "CONSTRAINT pkey PRIMARY KEY" & _
        "(gid, cid)" & vbCrLf & _
        ");"
    Debug.Print strSql
    cn.Execute strSql
    Set cn = Nothing
End Sub

BTW, I'm not trying to tell you to use DDL to create your tables, but if this is the table design you want, you could run the procedure and then examine the table in Design View.

Yeah its a join table where i can do a ad-hoc style lookup for a specific gid and get all the cid's associated with it and vice-versa, lookup a cid and get all the gid's. But in either instance there is only supposed to be one cid paired with a specific gid, even though there might be multiple entries for that specific cid, only one is paired with a unique gid — GoldBishop, Aug 29 '12 at 17:18

Distinct Row - Based on two fields

Edit (2010-08-27 11:18am CST)

2 Answers2

Linked