How to check record duplication before saving?

Discussion in 'Ruby' started by Frank Tsao, Apr 11, 2008.

  1. Frank Tsao

    Frank Tsao Guest

    Hi, everyone,

    I am a beginner in ruby programming and I have a minor question about
    data checking, if someone who could give me a suggestion that would be
    great.

    The system needs to prevent a duplicated record happened in database,
    the record is consisted of two fields which are column A and column B.
    Supposing a record has been saved in database that the A=1, B=1.
    Afterward, the same set of record can not be accepted again, for
    example, record: A=1, B=2 and A=2, B=1 both are acceptable. In other
    words, record: A=1, B=1 can not be saved in database.

    So how to check and prevent a new record that has the same columns with
    same values tend to be saved in database?


    Frank
    --
    Posted via http://www.ruby-forum.com/.
     
    Frank Tsao, Apr 11, 2008
    #1
    1. Advertising

  2. Frank Tsao

    Dave Thomas Guest

    On Apr 11, 2008, at 3:36 PM, Frank Tsao wrote:
    > So how to check and prevent a new record that has the same columns
    > with
    > same values tend to be saved in database?


    Try creating a unique database index that spans the two columns.


    Regards


    Dave
     
    Dave Thomas, Apr 11, 2008
    #2
    1. Advertising

  3. -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    Frank Tsao wrote:
    | Hi, everyone,
    |
    | I am a beginner in ruby programming and I have a minor question about
    | data checking, if someone who could give me a suggestion that would be
    | great.
    |
    | The system needs to prevent a duplicated record happened in database,
    | the record is consisted of two fields which are column A and column B.
    | Supposing a record has been saved in database that the A=1, B=1.
    | Afterward, the same set of record can not be accepted again, for
    | example, record: A=1, B=2 and A=2, B=1 both are acceptable. In other
    | words, record: A=1, B=1 can not be saved in database.
    |
    | So how to check and prevent a new record that has the same columns with
    | same values tend to be saved in database?

    Most database systems can handle that, be defining a UNIQUE constraint
    on columns.

    However, that means that A = 1 and B = 2 won't work.

    Different methods to access databases have different ways to handle
    these kinds of collisions, or at least different ways to implement the
    collision detection.

    (For example, you can fetch all records, and check if they are already
    set, but that is rather bad for memory consumption and speed, but might
    be enough in your case.)

    So, if you can tell us how you access your database (and what it is), we
    can help you out much better.

    - --
    Phillip Gawlowski
    Twitter: twitter.com/cynicalryan

    ~ - You know you've been hacking too long when...
    ...your brain keeps hallucinating random "system error: collision with
    stack heap" or "Guru Meditation Mode # Three billion and fifty-two,
    press left button to continue" or even "This is not a DOS disk." error
    messages.
    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.4.8 (MingW32)
    Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

    iEYEARECAAYFAkf/zngACgkQbtAgaoJTgL/2mwCfU4Ndpir25pE11+V9JyTYvAmB
    CAkAn0N6erBRi8ISLPD/ptHkM/GhFMGU
    =oV1Q
    -----END PGP SIGNATURE-----
     
    Phillip Gawlowski, Apr 11, 2008
    #3
  4. Frank Tsao

    Dave Thomas Guest

    On Apr 11, 2008, at 3:47 PM, Phillip Gawlowski wrote:
    > Most database systems can handle that, be defining a UNIQUE constraint
    > on columns.
    >
    > However, that means that A = 1 and B = 2 won't work.


    Sure it will- you define the unique index to span the two columns, and
    then only non-unoque combinations of those columns will fail.

    Dave
     
    Dave Thomas, Apr 11, 2008
    #4
  5. Frank Tsao

    Guest

    If you are using Rails and migrations

    add_index:)table, [:column1, :column2], :unique => true)

    if not something like this sql

    CREATE UNIQUE INDEX index_name ON table(column1,column2);

    Becker

    On Fri, Apr 11, 2008 at 2:02 PM, Dave Thomas <> wrote:
    >
    > On Apr 11, 2008, at 3:47 PM, Phillip Gawlowski wrote:
    >
    > > Most database systems can handle that, be defining a UNIQUE constraint
    > > on columns.
    > >
    > > However, that means that A = 1 and B = 2 won't work.
    > >

    >
    > Sure it will- you define the unique index to span the two columns, and then
    > only non-unoque combinations of those columns will fail.
    >
    > Dave
    >
    >
     
    , Apr 11, 2008
    #5
  6. Frank Tsao

    Frank Tsao Guest

    wrote:
    > If you are using Rails and migrations
    >
    > add_index:)table, [:column1, :column2], :unique => true)
    >
    > if not something like this sql
    >
    > CREATE UNIQUE INDEX index_name ON table(column1,column2);
    >
    > Becker


    Thanks for your help, but the situation in A=1, B=2 or A=2, B=1 that
    wont be accepted if using unique.

    My point is that when record(A=1, B=1) was existed in database, any new
    record(A=1, B=1) can not be valided. They can be (A=1, B=2), (A=1, B=3)
    or (A=5, B=1) etc.
    --
    Posted via http://www.ruby-forum.com/.
     
    Frank Tsao, Apr 12, 2008
    #6
  7. Frank Tsao

    Frank Tsao Guest

    Dave Thomas wrote:
    > On Apr 11, 2008, at 3:36 PM, Frank Tsao wrote:
    >> So how to check and prevent a new record that has the same columns
    >> with
    >> same values tend to be saved in database?

    >
    > Try creating a unique database index that spans the two columns.
    >
    >
    > Regards
    >
    >
    > Dave


    Thanks for your help, but the situation in A=1, B=2 or A=2, B=1 that
    wont be accepted if using unique.

    My point is that when record(A=1, B=1) was existed in database, any new
    record(A=1, B=1) can not be valided. They can be (A=1, B=2), (A=1, B=3)
    or (A=5, B=1) etc.
    --
    Posted via http://www.ruby-forum.com/.
     
    Frank Tsao, Apr 12, 2008
    #7
  8. Frank Tsao

    Frank Tsao Guest

    Thanks for your help. I just used Instant Rails 2.0 for developing.
    Mongrel and Sqlite.
    --
    Posted via http://www.ruby-forum.com/.
     
    Frank Tsao, Apr 12, 2008
    #8
  9. -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    Frank Tsao wrote:
    | Thanks for your help. I just used Instant Rails 2.0 for developing.
    | Mongrel and Sqlite.

    As far as I can see, you have to iterate of all the present records to
    check for uniqueness of values, since SQLite3 doesn't allow for a
    multi-column uniqueness.

    With MySQl and other RDBMSes, Dave Thomas' idea should work (I don't
    know, since I never had to handle this situation yet myself).

    - --
    Phillip Gawlowski
    Twitter: twitter.com/cynicalryan

    Rule of Open-Source Programming #13:

    Your first release can always be improved upon.
    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.4.8 (MingW32)
    Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

    iEYEARECAAYFAkf/8hUACgkQbtAgaoJTgL88EgCfWkL9pAPPKoEJ/8fBNWdaoa48
    PbUAoINqSXEudAti8l6/YlauOAgPp1/f
    =ZaP/
    -----END PGP SIGNATURE-----
     
    Phillip Gawlowski, Apr 12, 2008
    #9
  10. Frank Tsao

    Todd Benson Guest

    On Fri, Apr 11, 2008 at 6:01 PM, Frank Tsao <> wrote:
    > wrote:
    > > If you are using Rails and migrations
    > >
    > > add_index:)table, [:column1, :column2], :unique => true)
    > >
    > > if not something like this sql
    > >
    > > CREATE UNIQUE INDEX index_name ON table(column1,column2);
    > >
    > > Becker

    >
    > Thanks for your help, but the situation in A=1, B=2 or A=2, B=1 that
    > wont be accepted if using unique.
    >
    > My point is that when record(A=1, B=1) was existed in database, any new
    > record(A=1, B=1) can not be valided. They can be (A=1, B=2), (A=1, B=3)
    > or (A=5, B=1) etc.


    You probably have a primary key constraint on one of the columns
    (unless you are using kindergarten SQLite). Example without correct
    caps...

    create table stuff (
    colA int not null,
    colB int not null,
    primary key (colA, colB)
    )

    ...or if you succumb to the idea integer IDs for the primary key are a
    good idea...

    create table stuff (
    id int not null primary key,
    colA int not null,
    colB int not null,
    unique (colA, colB)
    )

    Not tested.

    Todd
     
    Todd Benson, Apr 12, 2008
    #10
  11. Frank Tsao

    Todd Benson Guest

    On Fri, Apr 11, 2008 at 6:43 PM, Todd Benson <> wrote:

    > You probably have a primary key constraint on one of the columns
    > (unless you are using kindergarten SQLite).


    That probably sounded bad. Sorry.

    > Example without correct
    > caps...
    >
    > create table stuff (
    > colA int not null,
    > colB int not null,
    > primary key (colA, colB)
    > )
    >
    > ...or if you succumb to the idea integer IDs for the primary key are a
    > good idea...
    >
    > create table stuff (
    > id int not null primary key,
    > colA int not null,
    > colB int not null,
    > unique (colA, colB)
    > )


    In rails, I should point out I _have_ to use the integer ID for my
    primary key, which irks me to no end because it goes against
    everything I know about set theory. That whining aside, I might try
    to fix it, but the underlying rails code is intimidating.

    Todd
     
    Todd Benson, Apr 12, 2008
    #11
  12. Frank Tsao

    Guest

    I like to use guids for ids.

    create_table:)name,{:id=>false}) do |t|
    t.column:)id,:string,:limit=>64)
    end
    execute(sql_statement_to_create_primary_key)

    Becker


    On Sat, Apr 12, 2008 at 9:34 AM, Todd Benson <> wrote:
    > On Fri, Apr 11, 2008 at 6:43 PM, Todd Benson <> wrote:
    >
    > > You probably have a primary key constraint on one of the columns
    > > (unless you are using kindergarten SQLite).

    >
    > That probably sounded bad. Sorry.
    >
    >
    > > Example without correct
    > > caps...
    > >
    > > create table stuff (
    > > colA int not null,
    > > colB int not null,
    > > primary key (colA, colB)
    > > )
    > >
    > > ...or if you succumb to the idea integer IDs for the primary key are a
    > > good idea...
    > >
    > > create table stuff (
    > > id int not null primary key,
    > > colA int not null,
    > > colB int not null,
    > > unique (colA, colB)
    > > )

    >
    > In rails, I should point out I _have_ to use the integer ID for my
    > primary key, which irks me to no end because it goes against
    > everything I know about set theory. That whining aside, I might try
    > to fix it, but the underlying rails code is intimidating.
    >
    > Todd
    >
    >
     
    , Apr 12, 2008
    #12
  13. Frank Tsao

    ara.t.howard Guest

    [OT] Re: How to check record duplication before saving?

    On Apr 12, 2008, at 10:34 AM, Todd Benson wrote:
    >
    > In rails, I should point out I _have_ to use the integer ID for my
    > primary key, which irks me to no end because it goes against
    > everything I know about set theory. That whining aside, I might try
    > to fix it, but the underlying rails code is intimidating.


    no db actually deals with sets - try to do a query and get the results
    back in different order each time. in practice they are *ordered*
    tuples of key value pairs and every db i've used is written with this
    implicit understanding. having an id is no different that having an
    'updated_at' field - it simply provides a total ordering which is
    arbitrary. fighting the id is just swimming upstream to nowhere - i
    know as i did it for a long time. ;-)

    a @ http://codeforpeople.com/
    --
    we can deny everything, except that we have the possibility of being
    better. simply reflect on that.
    h.h. the 14th dalai lama
     
    ara.t.howard, Apr 12, 2008
    #13
  14. Frank Tsao

    Rick Tessner Guest

    Hi,

    Excerpts from shuhao.tsao's message of Fri Apr 11 16:02:10 -0700 2008:
    > Dave Thomas wrote:
    > > On Apr 11, 2008, at 3:36 PM, Frank Tsao wrote:
    > >> So how to check and prevent a new record that has the same columns
    > >> with
    > >> same values tend to be saved in database?

    > >
    > > Try creating a unique database index that spans the two columns.
    > >
    > >
    > > Regards
    > >
    > >
    > > Dave

    >
    > Thanks for your help, but the situation in A=1, B=2 or A=2, B=1 that
    > wont be accepted if using unique.
    >
    > My point is that when record(A=1, B=1) was existed in database, any new
    > record(A=1, B=1) can not be valided. They can be (A=1, B=2), (A=1, B=3)
    > or (A=5, B=1) etc.


    You could try creating a third column. Let's suppose that you know that
    all A & B values are 6 digits or less, create a unique string column
    that will then contain the value of A & B sorted, zero padded and
    joined.

    u = [a, b].sort.map { |i| "%06d" %i }.join

    So, the database would contain 3 columns: a, b, u with u being defined
    as unique in the database.

    So, with a = 1; b = 2 we get u = "000001000002"

    or with a = 2; b = 1 we also get u = "000001000002"


    --
    Rick Tessner
     
    Rick Tessner, Apr 12, 2008
    #14
  15. Frank Tsao

    Todd Benson Guest

    Re: [OT] Re: How to check record duplication before saving?

    On Sat, Apr 12, 2008 at 3:55 PM, ara.t.howard <> wrote:
    >
    > On Apr 12, 2008, at 10:34 AM, Todd Benson wrote:
    >
    > >
    > > In rails, I should point out I _have_ to use the integer ID for my
    > > primary key, which irks me to no end because it goes against
    > > everything I know about set theory. That whining aside, I might try
    > > to fix it, but the underlying rails code is intimidating.
    > >

    >
    > no db actually deals with sets - try to do a query and get the results back
    > in different order each time. in practice they are *ordered* tuples of key
    > value pairs and every db i've used is written with this implicit
    > understanding. having an id is no different that having an 'updated_at'
    > field - it simply provides a total ordering which is arbitrary. fighting
    > the id is just swimming upstream to nowhere - i know as i did it for a long
    > time. ;-)
    >
    > a @ http://codeforpeople.com/


    I understand that, for example, postgresql and mysql have labeled all
    things with an integer. But, in the table itself, umm, doesn't make
    much sense...

    create table my_set (
    id int not null primary key,
    a int,
    b int,
    );

    insert into my_set values (1, 1, 1);

    insert into my_set values (2, 1, 1);

    It gets even worse with built in functions like auto_increment, where
    you've tossed all identity of the tuple to the wind (think of moving
    such a table to another db).

    I'm still a firm believer in using logical primary keys instead of
    arbitrary ones anyway, even if it takes a little more effort, but I'm
    willing to listen to other ways :)

    Todd
     
    Todd Benson, Apr 13, 2008
    #15
  16. Frank Tsao

    Todd Benson Guest

    Re: [OT] Re: How to check record duplication before saving?

    On Sat, Apr 12, 2008 at 7:14 PM, Todd Benson <> wrote:
    > On Sat, Apr 12, 2008 at 3:55 PM, ara.t.howard <> wrote:
    > >
    > > On Apr 12, 2008, at 10:34 AM, Todd Benson wrote:
    > >
    > > >
    > > > In rails, I should point out I _have_ to use the integer ID for my
    > > > primary key, which irks me to no end because it goes against
    > > > everything I know about set theory. That whining aside, I might try
    > > > to fix it, but the underlying rails code is intimidating.
    > > >

    > >
    > > no db actually deals with sets - try to do a query and get the results back
    > > in different order each time. in practice they are *ordered* tuples of key
    > > value pairs and every db i've used is written with this implicit
    > > understanding. having an id is no different that having an 'updated_at'
    > > field - it simply provides a total ordering which is arbitrary. fighting
    > > the id is just swimming upstream to nowhere - i know as i did it for a long
    > > time. ;-)
    > >
    > > a @ http://codeforpeople.com/

    >
    > I understand that, for example, postgresql and mysql have labeled all
    > things with an integer. But, in the table itself, umm, doesn't make
    > much sense...
    >
    > create table my_set (
    >
    > id int not null primary key,
    > a int,
    > b int,
    > );
    >
    > insert into my_set values (1, 1, 1);
    >
    > insert into my_set values (2, 1, 1);
    >
    > It gets even worse with built in functions like auto_increment, where
    > you've tossed all identity of the tuple to the wind (think of moving
    > such a table to another db).
    >
    > I'm still a firm believer in using logical primary keys instead of
    > arbitrary ones anyway, even if it takes a little more effort, but I'm
    > willing to listen to other ways :)


    I'll clarify a little (I may have told this little story before). I
    once worked for a company that had complete confidence in its ERP
    database (to be nameless). Suddenly, one day, there's about a half
    mil missing for no real reason except for what the reports said.
    Having been embezzled before, they wanted to find the culprit. The
    culprit turned out to be the database schema (and they were _so_ ready
    for a witch hunt).

    Todd
     
    Todd Benson, Apr 13, 2008
    #16
  17. Frank Tsao

    Todd Benson Guest

    Re: [OT] Re: How to check record duplication before saving?

    On Sat, Apr 12, 2008 at 8:37 PM, Todd Benson <> wrote:
    >
    > On Sat, Apr 12, 2008 at 7:14 PM, Todd Benson <> wrote:
    > > On Sat, Apr 12, 2008 at 3:55 PM, ara.t.howard <> wrote:
    > > >
    > > > On Apr 12, 2008, at 10:34 AM, Todd Benson wrote:
    > > >
    > > > >
    > > > > In rails, I should point out I _have_ to use the integer ID for my
    > > > > primary key, which irks me to no end because it goes against
    > > > > everything I know about set theory. That whining aside, I might try
    > > > > to fix it, but the underlying rails code is intimidating.
    > > > >
    > > >
    > > > no db actually deals with sets - try to do a query and get the results back
    > > > in different order each time. in practice they are *ordered* tuples of key
    > > > value pairs and every db i've used is written with this implicit
    > > > understanding. having an id is no different that having an 'updated_at'
    > > > field - it simply provides a total ordering which is arbitrary. fighting
    > > > the id is just swimming upstream to nowhere - i know as i did it for a long
    > > > time. ;-)
    > > >
    > > > a @ http://codeforpeople.com/

    > >
    > > I understand that, for example, postgresql and mysql have labeled all
    > > things with an integer. But, in the table itself, umm, doesn't make
    > > much sense...
    > >
    > > create table my_set (
    > >
    > > id int not null primary key,
    > > a int,
    > > b int,
    > > );
    > >
    > > insert into my_set values (1, 1, 1);
    > >
    > > insert into my_set values (2, 1, 1);
    > >
    > > It gets even worse with built in functions like auto_increment, where
    > > you've tossed all identity of the tuple to the wind (think of moving
    > > such a table to another db).
    > >
    > > I'm still a firm believer in using logical primary keys instead of
    > > arbitrary ones anyway, even if it takes a little more effort, but I'm
    > > willing to listen to other ways :)

    >
    > I'll clarify a little (I may have told this little story before). I
    > once worked for a company that had complete confidence in its ERP
    > database (to be nameless). Suddenly, one day, there's about a half
    > mil missing for no real reason except for what the reports said.
    > Having been embezzled before, they wanted to find the culprit. The
    > culprit turned out to be the database schema (and they were _so_ ready
    > for a witch hunt).
    >
    > Todd


    Wow. That sounded incriminating, didn't it! My apologies for the
    noise, but it's true. The company I worked for lost a bunch of money
    because of a culmination of practices. They were paying for new part
    revs and the db didn't reflect it. "Bad" shipments were dropped/lost
    in the shipping part of the database. Also, it was full of tables
    called: 12400010, 12400011, etc. and the relations were not even
    close to sane. The "help" was paid about $1000/hr to set it up.

    Todd
     
    Todd Benson, Apr 13, 2008
    #17
  18. Frank Tsao

    ara.t.howard Guest

    Re: [OT] Re: How to check record duplication before saving?

    On Apr 12, 2008, at 6:14 PM, Todd Benson wrote:
    >
    > It gets even worse with built in functions like auto_increment, where
    > you've tossed all identity of the tuple to the wind (think of moving
    > such a table to another db).


    yeah that's valid. i have at least one db where every id is a
    globally unique uuid - and by globally i mean according to rfc. still

    olddb.id

    is a valid concept...

    a @ http://codeforpeople.com/
    --
    we can deny everything, except that we have the possibility of being
    better. simply reflect on that.
    h.h. the 14th dalai lama
     
    ara.t.howard, Apr 13, 2008
    #18
  19. Frank Tsao

    ara.t.howard Guest

    Re: [OT] Re: How to check record duplication before saving?

    On Apr 12, 2008, at 7:48 PM, Todd Benson wrote:
    > The "help" was paid about $1000/hr to set it up.


    guess we have something to learn from them! ;-)

    a @ http://codeforpeople.com/
    --
    we can deny everything, except that we have the possibility of being
    better. simply reflect on that.
    h.h. the 14th dalai lama
     
    ara.t.howard, Apr 13, 2008
    #19
  20. Frank Tsao

    Todd Benson Guest

    Re: [OT] Re: How to check record duplication before saving?

    On Sat, Apr 12, 2008 at 9:02 PM, ara.t.howard <> wrote:
    >
    > On Apr 12, 2008, at 7:48 PM, Todd Benson wrote:
    >
    > > The "help" was paid about $1000/hr to set it up.
    > >

    >
    > guess we have something to learn from them! ;-)


    Yeah, it's funny. That may sound like an exaggeration, but after you
    add up the flight cost and motel stay, it probably was more like
    $1200/hr. The guy worked about 4 hours per day for a total of 3 or 4
    days.
     
    Todd Benson, Apr 13, 2008
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. ll

    duplication?

    ll, Nov 9, 2003, in forum: ASP .Net
    Replies:
    1
    Views:
    402
    Rocky Moore
    Nov 9, 2003
  2. msnews.microsoft.com

    Duplication of html generated from htmlwebresponse

    msnews.microsoft.com, Mar 28, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    387
    msnews.microsoft.com
    Apr 5, 2005
  3. =?Utf-8?B?QWVkZW4gSmFtZXNvbg==?=

    Duplication Submission on NLB cluster

    =?Utf-8?B?QWVkZW4gSmFtZXNvbg==?=, Oct 3, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    432
    Trevor Benedict R
    Oct 4, 2005
  4. Kavita

    Duplication of record in database

    Kavita, Nov 29, 2007, in forum: ASP .Net
    Replies:
    7
    Views:
    317
    Ian Semmel
    Nov 29, 2007
  5. Rob Dob

    BindingSource, Archiving Old record before saving new changes

    Rob Dob, Mar 14, 2006, in forum: ASP .Net Datagrid Control
    Replies:
    1
    Views:
    140
    Brian Smith
    Mar 16, 2006
Loading...

Share This Page