Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    14

    Unanswered: Unique Field Across Multiple Tables

    Hi,

    I have four or five tables and in each table there is an "assettag" field. I have these set with an index that they must be unique in each table but i need to make sure that this field is unique across all of these tables. So basically no asset tag can be the same anywhere in the entire database. I figure the easiest way to do this would be through some type of join table but i am not really sure. What do you guys suggest as the best way to do this?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if assettag is unique across the entire database then it belongs in it's own table. all the other guys look at it as a foreign key.

    my "house style" would be NOT to use assettag as primary key, but to have an autonumber as PK and assetag as unique-indexed. i have a "thing" about meaningful PKs.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    May 2004
    Posts
    14
    ok i understand what you are saying by that but that has some other implications that make me worry. There is a data entry portion of this database where people enter data into these tables including this asset tag. So my question is that on this data entry form i would then have to insert the value into the newly created assettag table rather than into the original table, but the asset tag is specific to each of these records so how would i go about storing it in both tables?

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you absolutely must not store it in both tables.

    your new assettag goes in a table of assettags

    tblAssetTags
    IDassettag, autonumber, primary key
    assettag, text or whatever it is, index-unique

    only the IDassettag goes in the related table(s)


    one way would be to popup / modal an assettag-create-form in response to a user event ( e.g. _click() on a "New Asset" button). add your new tag, use some code to stick the new assettag into a combo on your main form, close the popup.
    you could also use the combo's _notinlist() either by using the add-to-combo possibility or using the notinlist to popup the create-form.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •