Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Ruby > How to check record duplication before saving?

Reply
Thread Tools

How to check record duplication before saving?

 
 
Frank Tsao
Guest
Posts: n/a
 
      04-11-2008
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/.

 
Reply With Quote
 
 
 
 
Dave Thomas
Guest
Posts: n/a
 
      04-11-2008

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

 
Reply With Quote
 
 
 
 
Phillip Gawlowski
Guest
Posts: n/a
 
      04-11-2008
-----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-----

 
Reply With Quote
 
Dave Thomas
Guest
Posts: n/a
 
      04-11-2008

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

 
Reply With Quote
 
RubyTalk@gmail.com
Guest
Posts: n/a
 
      04-11-2008
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 <(E-Mail Removed)> 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
>
>


 
Reply With Quote
 
Frank Tsao
Guest
Posts: n/a
 
      04-11-2008
http://www.velocityreviews.com/forums/(E-Mail Removed) 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/.

 
Reply With Quote
 
Frank Tsao
Guest
Posts: n/a
 
      04-11-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.
--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
Frank Tsao
Guest
Posts: n/a
 
      04-11-2008
Thanks for your help. I just used Instant Rails 2.0 for developing.
Mongrel and Sqlite.
--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
Phillip Gawlowski
Guest
Posts: n/a
 
      04-11-2008
-----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-----

 
Reply With Quote
 
Todd Benson
Guest
Posts: n/a
 
      04-11-2008
On Fri, Apr 11, 2008 at 6:01 PM, Frank Tsao <(E-Mail Removed)> wrote:
> (E-Mail Removed) 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

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How include a large array? Edward A. Falk C Programming 1 04-04-2013 08:07 PM
Duplication of record in database Kavita ASP .Net 7 11-29-2007 05:07 PM
HSRP mac duplication erros causing vlans configured to flap gurumurthy Cisco 6 02-14-2006 08:59 PM
Duplication of html generated from htmlwebresponse msnews.microsoft.com ASP .Net 2 04-05-2005 07:25 AM
duplication? ll ASP .Net 1 11-09-2003 09:18 AM



Advertisments