UNIQUE constraint and RAND() function?

2 Message(s) by 2 Author(s) originally posted in mysql discussion

From: lawpoop

Date: Wednesday, October 24, 2007

Hello all -

I have a table

<database> A collection of {records} in a {relational database}.

	(1997-06-04)

with a column that has a UNIQUE

<language> A {portable} {job control language}.

	["The UNIQUE Command Language - Portable Job Control",
	I.A. Newman, Proc DATAFAIR 73, 1973, pp. 353-357].

	(1994-11-22)

constraint. This column
is a code

<software> Instructions for a computer in some programming
	language, often {machine language}.  The word "code" is often
	used to distinguish instructions from {data} (e.g. "The code
	is marked 'read-only'") whereas "{software}" is used in
	contrast with "{hardware}" and may consist of more than just
	code.

	(2000-04-08)

number that a user uses to log into a website.

The value is only good for 2 days, so I figure 5 characters is long
enough to keep someone from guessing a code. Also, since we do not want
someone guessing codes from a sequence, I'm using the RAND() function
( along with a few others to create a zero-padded five digit

An employee of Digital Equipment Corporation.  See also
	{VAX}, {VMS}, {PDP-10}, {TOPS-10}, {DEChead}, {double
	DECkers}, {field circus}.

	[{Jargon File}]

number )
to get a random

1. Unpredictable (closest to mathematical definition); weird.
	"The system's been behaving pretty randomly."

	2. Assorted; undistinguished.  "Who was at the conference?"
	"Just a bunch of random business types."

	3. (pejorative) Frivolous; unproductive; undirected.  "He's
	just a random loser."

	4. Incoherent or inelegant; poorly chosen; not well organised.
	"The program has a random set of misfeatures."  "That's a
	random name for that function."  "Well, all the names were
	chosen pretty randomly."

	5. In no particular order, though {deterministic}.  "The I/O
	channels are in a pool, and when a file is opened one is
	chosen randomly."

	6. Arbitrary.  "It generates a random name for the scratch
	file."

	7. Gratuitously wrong, i.e. poorly done and for no good
	apparent reason.  For example, a program that handles file
	name defaulting in a particularly useless way, or an assembler
	routine that could easily have been coded using only three
	registers, but redundantly uses seven for values with
	non-overlapping lifetimes, so that no one else can invoke it
	without first saving four extra registers.  What {randomness}!

	8.  A random hacker; used particularly of high-school students
	who soak up computer time and generally get in the way.

	9.  Anyone who is not a hacker (or, sometimes, anyone not
	known to the hacker speaking).  "I went to the talk, but the
	audience was full of randoms asking bogus questions".

	10.  (occasional MIT usage) One who lives at Random Hall.  See
	also {J. Random}, {some random X}.

	[{Jargon File}]

	(1995-12-05)

number:

INSERT INTO Cards ( code_number ) VALUES ( LPAD( FLOOR( RAND() *
100000 ), 5, '0' ) )

My concern is that I could get a failed insert if the RAND() guesses a
number that happens to exist already in the table. Is this a concern?
How could I fix it without using a pattern, or referencing existing
codes, so that the user could not guess the pattern?

Bonus question: Is there a control

<character> (Or "ctrl", "^") One (or a pair) of {modifier
	keys} found on all modern {keyboards}.  If the control key is
	held down while pressing and releasing certain other keys then
	a "{control character}" is generated, e.g. holding control and
	hitting "A" generates control-A ({ASCII} code 1).  The ASCII
	code for the control character is generally 64 less than that
	for the unmodified character.

	The control key does not generate any character on its own but
	most modern keyboards and {operating systems} allow a program
	to tell whether each of the individual keys on the keyboard
	(including modifier keys) is pressed at any time.

	Control characters mostly have some kind of "non-printing"
	effect on the output such as ringing the bell (Control-G) or
	advancing to the next line (Control-J).  Most have alternative
	names suggesting these functions (Bell, Line Feed, etc.).

	See {ASCII character table}.

	(1997-07-10)

statement that I could use in PHP
to continuously re-try my existing statement on failure, until RAND
comes up with an unused code number?

From: gordonb.vpu4p

Date: Wednesday, October 24, 2007

I have a table with a column that has a UNIQUE constraint. This column
is a code number that a user uses to log into a website.
The value is only good for 2 days, so I figure 5 characters is long
enough to keep someone from guessing a code.

I presume, then, that you periodically (e.g. hourly) remove old
records, because if you leave them around, you will violate the UNIQUE
constraint.

Also, since we do not want
someone guessing codes from a sequence, I'm using the RAND() function
( along with a few others to create a zero-padded five digit number )
to get a random number:

So how many users do you expect

<language, tool> A {Unix} tool written in {Tcl} and a {script
	language} for automating the operation of {interactive}
	applications such as {telnet}, {FTP}, {passwd}, {fsck},
	{rlogin}, {tip}, etc..  Expect can feed input to other
	programs and perform {pattern matching} on their output.  It
	is also useful for testing these applications.  By adding
	{Tk}, you can also wrap interactive applications in {X11}
	{GUIs}.

	{Home (http://expect.nist.gov/)}.

	["expect: Scripts for Controlling Interactive Tasks", Don
	Libes, Comp Sys 4(2), U Cal Press Journals, Nov 1991].

	(1997-06-09)

to sign up in 2 days? 5? You're
very safe

A safe program analysis is one which will not reach invalid
	conclusions about the behaviour of the program.  This may
	involve making safe approximations to properties of parts of
	the program.  A safe approximation is one which gives less
	information.

	For example, strictness analysis aims to answer the question
	"will this function evaluate its argument"?.  The two possible
	results are "definitely" and "don't know".  A safe
	approximation for "definitely" is "don't know".  The two
	possible results correspond to the two sets: "the set of all
	functions which evaluate their argument" and "all functions".
	A set can be safely approximated by another which contains it.

(but a screw

<jargon> (MIT) A {lose}, usually in software.  Especially used
	for user-visible misbehaviour caused by a bug or {misfeature}.
	This use has become quite widespread outside {MIT}.

	[{Jargon File}]

	(1994-12-01)

up is still not impossible). 20000? You're
virtually guaranteed to screw up at least every 2 days. 200000?
You're absolutely guaranteed to screw up at least half of them.This is similar to the "birthday problem". The chances of at least
two people having the same birthday (month and day) in a group

A group G is a non-empty {set} upon which a {binary} operator
	* is defined with the following properties for all a,b,c in G:

	  Closure:     G is closed under *,  a*b in G
	  Associative: * is associative on G, (a*b)*c = a*(b*c)
	  Identity:    There is an identity element  e  such that
		       a*e = e*a = a.
	  Inverse:     Every element has a unique inverse a' such that
		       a * a' = a' * a = e.  The inverse is usually
		       written with a superscript -1.

	(1998-10-03)

of
25 is about 50%, as I recall, even though you're using less than
7% of the days of the year.

INSERT INTO Cards ( code_number ) VALUES ( LPAD( FLOOR( RAND() *
100000 ), 5, '0' ) )
My concern is that I could get a failed insert if the RAND() guesses a
number that happens to exist already in the table. Is this a concern?

It depends. Is occasionally blowing up acceptable? For a web site
intended to look professional, the answer is certainly no. What
are the consequences of a breakin? Someone posts some SPAM in a
message? Not that big a deal (and real

1. Not simulated.  Often used as a specific antonym to
	{virtual} in any of its jargon senses.

	2. <mathematics> {real number}.

	[{Jargon File}]

	(1997-03-12)

users might do that anyway).
Does this site offer gambling games

<games> "The time you enjoy wasting is not time wasted." --
	{Bertrand Russell}.

	Here are some games-related pages on the {Web}: {Imperial
	Nomic (http://www.mit.edu:8001/people/achmed/fascist/)},
	{Thoth's games and recreations page
	(http://www.cis.ufl.edu/~thoth/library/recreation.html)},
	{Games Domain (http://wcl-rs.bham.ac.uk/GamesDomain)},
	{Zarf's	List of Games on the Web
	(http://www.leftfoot.com/games.html)},
	{Dave's list of pointers to games resources
	(http://wcl-rs.bham.ac.uk/~djh/index.html)},
	{Collaborative Fiction
	(http://asylum.cid.com/fiction/fiction.html)}.

	See also {3DO}, {ADL}, {ADVENT}, {ADVSYS}, {alpha/beta
	pruning}, {Amiga}, {CHIP-8}, {Core Wars}, {DROOL}, {empire},
	{I see no X here.}, {Infocom}, {Inglish}, {initgame}, {life},
	{minimax}, {moria}, {mudhead}, {multi-user Dimension},
	{nethack}, {ogg}, {plugh}, {rogue}, {SPACEWAR}, {virtual
	reality}, {wizard mode}, {wumpus}, {xyzzy}, {ZIL}, {zorkmid}.

	See also {game theory}.

	(1996-03-03)

with real money? You will go
broke very quickly.

How could I fix it without using a pattern, or referencing existing
codes, so that the user could not guess the pattern?

RAND() isn't cryptographically secure. This means it may not be that
hard to guess a generated number given a previous one (Joe Evil signs up
himself to get a number, then uses it to guess others).

Bonus question: Is there a control statement that I could use in PHP
to continuously re-try my existing statement on failure, until RAND
comes up with an unused code number?

Look at something like mysql_affected_rows() after your query. If
it's 0, it failed, try again. Either that or you have got other
problems, like a spelling error

1. A discrepancy between a computed, observed, or measured
	value or condition and the true, specified, or theoretically
	correct value or condition.

	2. <programming> A mental mistake made by a programmer that
	may result in a program {fault}.

	3. (verb) What a program does when it stops as result of a
	programming error.

	(2000-03-28)

in the query, or you're out of disk
space, or something else is broke. Perhaps if you fail more than a
dozen times, you should declare the page broken.

Next Message: Too fast web page for mysql database

Blogs related to UNIQUE constraint and RAND() function?

click here python untrusted interpreter delicate sound of thunder ...
bbs mere biwi ka jawab nahin-sridevi chickasha oklahoma newpaper archives-obituaries 1901 python nested function lexical scoping the nightingale's song at midnight and the morning rain. 1940 dia de la bandera peru colorear beast tgp ...

sql pivottable
sql quick reference pdf sql quick reference sheet sql quotation marks sql raiserror sql rand sql rand function sql random sql random function sql random number sql random number generation sql random number generator sql random numbers ...

How do I website delete all but the top 10 posts in a table I have ...
so alter table searchdb unique(wordid, recid); ? if you want to add a unique key on wordid, recid yes. or what? syntax erro. you have to say “add” alter table… add… ok that worked. WOW now you should have unique constraint between the ...

sql query in visual basic
... reference sql quick reference card sql quick reference guide sql quick reference oracle sql quick reference pdf sql quick reference sheet sql quotation marks sql raiserror sql rand sql rand function sql random sql random function ...

im in process of moving my clients hosting accounts to another ...
is it possible to ask mysql to set a random value of a given range for some field? why? robboplus, yes. gnari hm !man random. Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/random !man rand ...

MySQL Reference
The CREATE FUNCTION statement allows MySQL statements to access precompiled executable functions known as user-defined functions (UDFs). These functions can perform practically any operation, since they are designed and implemented by ...