Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    24

    Unanswered: UNIQUE constrain

    I was interested in the unique clause with multiple fields:

    Browsing trought
    http://www.mysql.com/doc/en/CREATE_TABLE.html
    I didn't discovered any limitation on standard multifield unique constain, so I declared a table like

    CREATE TABLE Employee(
    Matricola CHAR(6) PRIMARY KEY,
    Name CHAR(20) NOT NULL,
    Surname CHAR(20) NOT NULL,
    Dipart CHAR(15),
    Salary NUMERIC(9) DEFAULT 0,
    /* FOREIGN KEY(Dipart) REFERENCES Dipartimento(NomeDip), */
    UNIQUE (Surname,Name)
    )

    I received no error nor warning in SQL Debug:
    [root@localhost:3306] CREATE TABLE Employee(
    Matricola CHAR(6) PRIMARY KEY,
    Name CHAR(20) NOT NULL,
    Surname CHAR(20) NOT NULL,
    Dipart CHAR(15),
    Salary NUMERIC(9) DEFAULT 0,

    UNIQUE (Surname,Name)
    )

    Then I inserted two time the same _surname_ with different _name_ getting the error:
    [root@localhost:3306] ERROR 1062: Valore duplicato '' per la chiave 1

    Seems the unique clause with different fields is not working ...

    H2O

  2. #2
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    It definately _does_ work, becuase I use it for many of my tables. Are you perhaps trying to insert a duplicate primary key value?

  3. #3
    Join Date
    Oct 2003
    Posts
    24
    Originally posted by Mincer
    It definately _does_ work, becuase I use it for many of my tables. Are you perhaps trying to insert a duplicate primary key value?
    No. As you can see, the primary key is the field "Matricola"
    CREATE TABLE Employee(
    Matricola CHAR(6) PRIMARY KEY,

    what is more strange is that opening the tables trough Controlcenter, it displays the green gemm (unique field) only over the Surname field, but the SQL statement was accepted... and was
    UNIQUE (Surname,Name)



    I may try opening a SQL table trough Access+ODBC......
    and look if it works....

    H2O

  4. #4
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    Can you show us the 2 insert statements? you have been trying?

  5. #5
    Join Date
    Oct 2003
    Posts
    24
    Originally posted by Mincer
    Can you show us the 2 insert statements? you have been trying?
    FOUND
    ---------------------------------------------
    CREATE TABLE Employee(
    Matricola CHAR(6) PRIMARY KEY,
    Name CHAR(20) NOT NULL,
    Surname CHAR(20) NOT NULL,
    Dipart CHAR(15),
    Salary NUMERIC(9) DEFAULT 0,

    UNIQUE (Surname,Name)
    )
    INSERT INTO `employee`
    (`Matricola`, `Name`, `Surname`, `Dipart`, `Salary`)
    VALUES ('', 'Jhon', 'White', NULL, 600)

    INSERT INTO `employee`
    (`Matricola`, `Name`, `Surname`, `Dipart`, `Salary`)
    VALUES ('', 'Luc', 'White', NULL, 5000)

    [root@localhost:3306] ERROR 1062: Valore duplicato '' per la chiave 1
    -----------------------

    As Primary key , Field Matricola is not given any value the first time, that is gets NULL (allowed ??).

    The second time it gets no value again, that is it gets again the NULL value.

    As primary key, I wonder if NULL is a valid value.... just the first time..
    Clearly, not been a int (the PK field is a char), it can't be autoincremented, but .....
    shouldn't be a impossible to let a primary key get the NULL value ??

    Giuliano

  6. #6
    Join Date
    Jul 2003
    Posts
    35
    Hi,

    You might want to read up on PRIMARY KEYs in MySQL...

    "A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, it will be done implicitly (and quietly). In MySQL the key is named PRIMARY. A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, which doesn't have any NULL columns, as the PRIMARY KEY."

    http://www.mysql.com/doc/en/CREATE_TABLE.html

    I tried entering data into your tables with unique PKs and everything worked ok.

    Chrs,
    Ash

  7. #7
    Join Date
    Sep 2003
    Location
    London
    Posts
    56
    Originally posted by H2O

    As Primary key , Field Matricola is not given any value the first time, that is gets NULL (allowed ??).

    The second time it gets no value again, that is it gets again the NULL value.
    So, you are trying to insert duplicate pk values.

    Also, so you are aware, you are not entering NULL into the field 'Matricola', you are entering an empty string. This is why your second query fails, because you are trying to have 2 records with the primary key as an empty string which is not unique, and hence does not conform to the requirements of a primary key.

    Hope this helps.

    Matt.

  8. #8
    Join Date
    Oct 2003
    Posts
    24

    Red face

    Hops ... it's true ...Sorry.... this time I messed up things a bit ...



    H2O

Posting Permissions

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