It says a unique key can be applied several times to a table where a primary key is only allowed once. Makes all sense but - if I try to add a unique key mysql prompts me of a double value?

The table shows 'primary_email' column as the one with a primary key attached. the other four 'key' columns are indexed

I wanted to add a unique key to 'mobile'! WHY?

Some people who have no email have a mobile number and thus both primary_email and/or mobile could be used to avoid double entries?

I AM NEW to MySQL, read a lot but the key-topic is still a bit grey to me...

THANK YOU

a unique key may consist of multiple columns

so if you declare this --how likely are you to get two people with the same name and same birthdate?

Hello r937

That would depend on the DB size but it could reach a hundred and more?

But that is not what I'd do. I'd choose, i.e. mobile number, company name, and other pretty much unique data instead, of course. The thing is, since I assigned a primary key to the 1st email address, MySQL wouldn't allow me to assign now a unique key to any other column and I wonder why?

that's not true, of course it will

if you disagree, then we need to see the entire CREATE TABLE statement (along with the CREATE INDEX statements, if any, if you choose to declare the indexes separately from the table), plus of course the full error message

I'd LOVE to agree and I trust you are right. MAY that be one of these phpMyAdmin flaws?

I assigned the primary key and the indexes all in phpMyAdmin after the table was created, simply clicking on the thumbnails that would assign the keys. BUT - clicking on an asign "UNIQUE" thumnail I get the warning of double entry.

Give me a sec, I will send you the statements from phpMyAdmin

I can literally choose any column I like - clicking a "assign a unique key" thumnail in phpMyAdmin this is what I get:

#1062 - Duplicate entry 'y' for key 2
and the SQL phpMyadmin used for it was;
ALTER TABLE `clients` ADD UNIQUE (
`client`
)

running an SQL
ALTER TABLE clients ADD UNIQUE (mobile)
Will give me basically the same error

ah, okay, now i understand the error message

it cannot assign the unique index because the table already contains duplicates in the column(s) that you're trying to declare as unique

how many different clients are there? your client column is only VARCHAR(3)

I THINK SLOWLY I GET TO UNDERSTAND MYSQL...

this column has only var3 because it's a very simple identifier
y= existing client
r= renewal client
d= dropped off client and
n= not yet client

I am only experimenting and now I have 3 dummy clients with 2xy and 2xn... Is the error message caused because I have already 2 different determinable values (y/n) in that column and would I need to empty this column, assign a "UNIQUE" and then it would work?

Sorry for my ignorance but I am trying to learn fast ... ;-)

Hi r937

I know it's got nothing to do with this issue here ... but I got together a nice script that would allow someone to quickly search their DB for ANY entry and get data printed on their result page.

I have set up a page with 4 different search terms, all individually searchable (only the first is activated now, but) I think it's a very helpful little tool for companies with less experienced staff and I would like to share this with the community here. If interested, let me know how I can post it in this forum and I send you also a link for your own testing.

Cheers

is '2xn' an actual value in that column?

note that if you make it unique, then you can only ever have a single '2xn' row, in fact you can only have one row for each unique value

as for your script, sorry, if it's written in php i'm not interested -- i don't write php, and i don't even run php

Sorry, no I meant I have one time y and and 2 times n

Every contact in our DB would eventually have either a y, n, d, or an r assigned. This way we should be able to easy distinguish/pull all;
existing, non-existing, dropped off or returning contacts/clients!

That was my idea! Sure, every contact only has ONE of the four values at a time, but this can change, of course, since a non-client can turn an existing client.

In other words, we could have, i.e. 10,000 contacts;
5000 as y
3000 as r
1500 as n and
500 as d

Would my idea with the UNIQUE key for this column work?

absolutely not

unique means only one row can have any particular value

i think what you're looking for is something that will guarantee that only one of the four allowed values can be specified -- in other words, you can add as many rows for y or n or d or r, but you can't enter any other value, e.g. x

this is best done with a foreign key constraint to a "client_types" table, which would ahve only 4 rows, one for each of the types...
you should also change your client column in your main table to be called client_type, with CHAR(1)

and i'll leave it to you to suss out the actual FOREIGN KEY syntax to declare for it

r937

I must have read a 100 and more posts all around the net, all about unique, primary, index key, but nowhere could I actually understand what I understand from your explanation. It certainly pushes me towards better understanding ... (only one week young in the MySQL world)

Thanks for all your help, indeed.

Hi r937

Just took a nap for a couple of hrs. To make sure my brain is on your level, what you say is;

In my main table, let's call it "MAIN" I have many rows; DOB, EMAIL, NAME, PHONE, ... and "CLIENT_TYPE". in this client_type row I can have anything, y,n,r, or d.

Then I set up a CLIENT_TYPE TABLE with 4 rows named y,n,r, and d! Each of these rows have assigned a UNIQUE KEY!

Then in my main table, I have to apply "FOREIGN KEYS" to the CLIENT_TYPE row, correct?

I am pretty new to MySQL but this is to do with joining tables, right?

Thanks