avoid multible similar datasets

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
joecrack
Forum Commoner
Posts: 99
Joined: Mon Oct 31, 2005 9:17 pm

avoid multible similar datasets

Post 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&#39;m naughty, are you naughty?'>smurf</span> up.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

make the three fields a single unique key?
joecrack
Forum Commoner
Posts: 99
Joined: Mon Oct 31, 2005 9:17 pm

Post 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/.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Aw, another normalization question :roll:

There are some very detailed posts regarding normalization here.
joecrack
Forum Commoner
Posts: 99
Joined: Mon Oct 31, 2005 9:17 pm

Post 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
sheila
Forum Commoner
Posts: 98
Joined: Mon Sep 05, 2005 9:52 pm
Location: Texas

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
joecrack
Forum Commoner
Posts: 99
Joined: Mon Oct 31, 2005 9:17 pm

Post 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 =)
Post Reply