Page 1 of 1
avoid multible similar datasets
Posted: Tue Jan 10, 2006 9:18 pm
by joecrack
hi
i have a big problem, i have a table where a datadset is clearly identified by three fields
(project no, customer no and contract date).
Now how do i tell my sql database that it is not allowed to put a exact second one with these same three fields in it. Should i make all three fields unique. Because now everyone can put in the same dataset multible times.
And if i make a select everything is <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> up.
Posted: Tue Jan 10, 2006 9:23 pm
by feyd
make the three fields a single unique key?
Posted: Tue Jan 10, 2006 9:33 pm
by joecrack
no that doesnt work, because it has to be allowed to double the customer no for example.
there can be many projects from the same customer but not i combination witjh the same date and project no/.
Posted: Tue Jan 10, 2006 9:45 pm
by hawleyjr
Aw, another normalization question
There are some very detailed posts regarding normalization here.
Posted: Tue Jan 10, 2006 9:56 pm
by joecrack
i know sorry .. i tried to solve my prob by reading post in this forum .. but nothing realy helped me - you have any ideas or a good url where i can read a bit more specific about it ?>??? would be nice
thx joe
Posted: Tue Jan 10, 2006 10:04 pm
by sheila
joecrack wrote:no that doesnt work, because it has to be allowed to double the customer no for example.
there can be many projects from the same customer but not i combination witjh the same date and project no/.
You misunderstood what feyd said The combination of all three columns is defined to be unique. Have a look at this page
http://dev.mysql.com/doc/refman/4.1/en/ ... index.html
where it talks about multiple column indexes
A column list of the form (col1,col2,...) creates a multiple-column index. Index values are formed by concatenating the values of the given columns.
Posted: Tue Jan 10, 2006 11:26 pm
by josh
You'd still need your application to be aware of unique rows as well, unless you just want standard mysql error messages
easy solution, just select count(*) where `row` = '' and `row2`= '' and `row3` = ''
if there exists a row with your conditions tell the user they already have a project or whatever
Posted: Wed Jan 11, 2006 12:38 am
by joecrack
thanks boys .. solved it.
@jshpro2 i just did a select with the postet data and if theres a similar one allready existing ERROR =)