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

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

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

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

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.

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



H2O