Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51

    Unanswered: UNIQUE or PRIMARY

    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?

    CREATE TABLE `clients` (
    `first_name` varchar(30) collate utf8_unicode_ci NOT NULL,
    `last_name` varchar(30) collate utf8_unicode_ci NOT NULL,
    `nickname` varchar(30) collate utf8_unicode_ci default NULL,
    `country_origin` varchar(30) collate utf8_unicode_ci NOT NULL,
    `ph_country` mediumint(3) NOT NULL,
    `ph_area` mediumint(5) NOT NULL,
    `ph_home` mediumint(12) NOT NULL,
    `mobile` mediumint(12) NOT NULL,
    `dob` date NOT NULL,
    `primary_email` varchar(40) collate utf8_unicode_ci NOT NULL default '@.com',
    `secondary_email` varchar(40) collate utf8_unicode_ci default NULL,
    `company` varchar(50) collate utf8_unicode_ci default NULL,
    `visa` varchar(10) collate utf8_unicode_ci NOT NULL,
    `visa_expire` date NOT NULL,
    `company_rego` varchar(20) collate utf8_unicode_ci NOT NULL,
    `other` varchar(30) collate utf8_unicode_ci NOT NULL,
    `notes` varchar(50) collate utf8_unicode_ci NOT NULL,
    `client` varchar(3) collate utf8_unicode_ci NOT NULL default 'n',
    PRIMARY KEY (`primary_email`),
    KEY `first_name` (`first_name`),
    KEY `country_origin` (`country_origin`),
    KEY `client` (`client`),
    KEY `dob` (`dob`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a unique key may consist of multiple columns

    so if you declare this --
    Code:
    UNIQUE (first_name,last_name, dob)
    how likely are you to get two people with the same name and same birthdate?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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?
    "Defying critics is a disagreement to growth through improvement"

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by zeroge View Post
    ...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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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
    "Defying critics is a disagreement to growth through improvement"

  6. #6
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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`
    )
    "Defying critics is a disagreement to growth through improvement"

  7. #7
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    running an SQL
    ALTER TABLE clients ADD UNIQUE (mobile)
    Will give me basically the same error
    "Defying critics is a disagreement to growth through improvement"

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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

    the table already contains duplicates in the column(s)
    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 ... ;-)
    "Defying critics is a disagreement to growth through improvement"

  10. #10
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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
    "Defying critics is a disagreement to growth through improvement"

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by zeroge View Post
    I am only experimenting and now I have 3 dummy clients with 2xy and 2xn...
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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?
    "Defying critics is a disagreement to growth through improvement"

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by zeroge View Post
    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...
    Code:
    CREATE TABLE client_types
    ( client_type CHAR(1) NOT NULL PRIMARY KEY
    , type_descr VARCHAR(19)
    );
    INSERT INTO client_types VALUES
     ('y','existing client')
    ,('r','renewal client')
    ,('d','dropped off client')
    ,('n','not yet client')
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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.
    "Defying critics is a disagreement to growth through improvement"

  15. #15
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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
    "Defying critics is a disagreement to growth through improvement"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •