Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2007
    Posts
    6

    Unanswered: same attributes on a table

    Hi, i have a table with 3 attributes, for example

    id, name, address

    how can i stop from having attributes with the same values like this

    id name address
    1 joe houston
    2 joe houston
    3 joe houston

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Make the 3 fields combined your primary key?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by georgev
    Make the 3 fields combined your primary key?
    Because the combination of the three fields is more unique than the id field alone?


    sakfree: are you sure there aren't three guys called Joe in living Houston?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sorry, I just re-read my answer and it's just shockingly bad! Ivon has raised the correct question here.
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    declare a unique constraint on name and address
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    declare a unique constraint on name and address
    ...but will a unique constraint on name and address cut it.

    for that to work then there must not be any other people with the same name and address.. something I'm not sure you can rely on, esepcially when parents name their children after themselves.

    In the absence of something truly unique (eg NI or SS number) then probably the best you could do is enforce a constraint that tied in the name, address and date of birth.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my philosophy, which is the result of answering thousands of forum questions (not just here but on other sites too), is to answer the question based on the data provided, and not venture out into areas which were not asked

    in this case, there are only two data columns, name and address

    therefore the question of whether birthdate is needed to determine true uniqueness is moot

    something else i've learned is that people often try to "dumb down" their real situations in an attempt to simplify their question

    i cannot begin to guess how many times i've been in threads like this:

    original poster: my situation is X and i'm trying to do Y
    me: the solution is Z
    original poster: thanks, but that won't work, my real situation is X1

    know what i do when that happens? i say "well, good luck, then"

    in fact, i have a snippet of text that i've saved so that i can copy/paste it whenever that scenario arises:
    if i take the time to write a very detailed solution to the problem
    that you have described, then you must take the time to adapt my
    explanation to your actual situation
    so in this case the answer is still: declare a unique constraint on name and address
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    ....n fact, i have a snippet of text that i've saved so that i can copy/paste it whenever that scenario arises:so in this case the answer is still: declare a unique constraint on name and address
    good point.....
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    So would the correct answer be?: Find a uniqueness in a combination of the "required" fields you have to establish your primary key where you don't want to duplicate that specific combined set of information.

    As a side point, I don't like using the first name as part of a primary key (to many Thomas verses Tom's, Richard verses Dick's, and Roberts verses Rob's.) I will use LastName and possibly the first XX characters of the street address and/or possibly the City and/or Birthdate or Phone, etc...

    Even the first XX characters of the street address can be tricky (1 W Park St verses 1 Park St W or 1113 E. Avenue verses 1113443 Somewhere land.)

    (or if SS is available, I establish that as my primary key as this is really never duplicated (unless for example, they can apply for a loan multiple times...but if you set up the relational structure correctly....) But SS is also rarely legally required to be given unless it's "typically" financially related.) Keep in mind that you also need to look at what fields are "legally" required to be given by the person for the specific application (we had a big issue where a person's phone number was not "legally" required to be given for a marketing program I designed.) It may come up at some point and could bite you if it's part of your primary key.

    Looks like I rambled on here. Sorry about that. In general though, Georgev's initial reponse to your question (how can i stop from having attributes with the same values like this?) is correct - establish a primary key on the combined fields. What fields they consist of are for you to decide and can probably be debated with many different opinions.
    Last edited by pkstormy; 10-01-07 at 19:29.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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