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)

Answer 1

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.

Answer 2

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 ...

gid cid
  1   1
  2   1
  2   2

... 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.