Based on my reply below, do you recommend I continue to have these indexes ?
-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Wed, Oct 13, 2010 at 8:22 PM
Subject: Re: Primary key not unique on InnoDB table
To: Travis Ard travis_...@hotmail.com
Cc
not unique on InnoDB table
Based on my reply below, do you recommend I continue to have these indexes ?
-- Forwarded message --
From: Tompkins Neil neil.tompk...@googlemail.com
Date: Wed, Oct 13, 2010 at 8:22 PM
Subject: Re: Primary key not unique on InnoDB table
To: Travis Ard
I've the following table. But why isn't the primary key unique, e.g.
preventing duplicates if entered ?
CREATE TABLE `players_master` (
`players_id` bigint(20) NOT NULL AUTO_INCREMENT,
`default_teams_id` bigint(20) NOT NULL,
`first_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
I´d never seen before a composed primary key that has an auto_increment
field on it.
May be I can be wrong but I think it wont work properly.
As far as I know, if you have an auto_increment field it must be your single
primary key. Am I wrong?
--
João Cândido de Souza Neto
Tompkins Neil
Sorry, the word is counpound instead of composed.
--
João Cândido de Souza Neto
João Cândido de Souza Neto j...@consultorweb.cnt.br escreveu na
mensagem news:20101013144314.9787.qm...@lists.mysql.com...
I´d never seen before a composed primary key that has an auto_increment
field on it.
Hi Neil,
Yes, primary key is always unique.
In your case, you are using composite key (players_id,default_teams_id).
_Krishna
On Wed, Oct 13, 2010 at 8:07 PM, Tompkins Neil neil.tompk...@googlemail.com
wrote:
I've the following table. But why isn't the primary key unique, e.g.
preventing
Sorry Joao, I thought that was pretty standard to have a primary key with
auto_increment ??
2010/10/13 João Cândido de Souza Neto j...@consultorweb.cnt.br
Sorry, the word is counpound instead of composed.
--
João Cândido de Souza Neto
João Cândido de Souza Neto j...@consultorweb.cnt.br
A primary key with an auto_increment is ok, but I cant think about a primary
key with two fiels where one of them is autoincrement. Am I completely
wrong?
--
João Cândido de Souza Neto
Tompkins Neil neil.tompk...@googlemail.com escreveu na mensagem
I see what you mean. Infact this is wrong and I will be dropping the second
field in the primary key.
2010/10/13 João Cândido de Souza Neto j...@consultorweb.cnt.br
A primary key with an auto_increment is ok, but I cant think about a
primary
key with two fiels where one of them is
Of course, sorry totally stupid should I recognised that.
Thanks
Neil
On Wed, Oct 13, 2010 at 3:46 PM, Krishna Chandra Prajapati
prajapat...@gmail.com wrote:
Hi Neil,
Yes, primary key is always unique.
In your case, you are using composite key (players_id,default_teams_id).
_Krishna
On 10/13/2010 10:37 AM, Tompkins Neil wrote:
I've the following table. But why isn't the primary key unique, e.g.
preventing duplicates if entered ?
CREATE TABLE `players_master` (
`players_id` bigint(20) NOT NULL AUTO_INCREMENT,
`default_teams_id` bigint(20) NOT NULL,
`first_name`
On 10/13/2010 11:37 AM, Tompkins Neil wrote:
Shawn, sorry my error, I didn't realise I had two fields as the primary key
That's misinformation. You can have multiple fields as a primary key.
Show us what you think is duplicate data and I may be able to help you
fix your definition
--
Shawn it is fine. I thought my primary key was just 1 field.
On Wed, Oct 13, 2010 at 4:44 PM, Shawn Green (MySQL)
shawn.l.gr...@oracle.com wrote:
On 10/13/2010 11:37 AM, Tompkins Neil wrote:
Shawn, sorry my error, I didn't realise I had two fields as the primary
key
That's
to
your storage requirements.
-Travis
-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Wednesday, October 13, 2010 8:37 AM
To: [MySQL]
Subject: Primary key not unique on InnoDB table
I've the following table. But why isn't the primary key unique, e.g
requirements.
-Travis
-Original Message-
From: Tompkins Neil [mailto:neil.tompk...@googlemail.com]
Sent: Wednesday, October 13, 2010 8:37 AM
To: [MySQL]
Subject: Primary key not unique on InnoDB table
I've the following table. But why isn't the primary key unique, e.g.
preventing
have found a problem with UNIQUE in InnoDB.
Example:
mysql CREATE TABLE b (
- idB int(11) NOT NULL auto increment,
- valor int(11) default NULL,
- PRIMARY KEY (idB),
- UNIQUE KEY valor (valor)
- ) TYPE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO b (idB
Jose,
thank you for the bug report. You are right: if there are several NULLs in a
unique secondary index, CHECK TABLE will report table corruption because of
a duplicate key in the index. I forgot to ease the check in CHECK TABLE.
I have now fixed this for 3.23.48.
Regards,
Heikki
Innobase
/InnoDB support at https://order.mysql.com/
I have found a problem with UNIQUE in InnoDB.
Example:
mysql CREATE TABLE b (
- idB int(11) NOT NULL auto increment,
- valor int(11) default NULL,
- PRIMARY KEY (idB),
- UNIQUE KEY valor (valor)
- ) TYPE=MyISAM;
Query OK, 0 rows
On Sun, Dec 16, 2001 at 09:33:06PM +0200, Heikki Tuuri wrote:
I will change InnoDB so that it allows several NULL values in secondary
indexes even if that index would have been declared UNIQUE. Oracle has a
similar convention.
In the case of foreign keys, I advise against using NULL values,
I have found a problem with UNIQUE in InnoDB.
Example:
mysql CREATE TABLE b (
- idB int(11) NOT NULL auto_increment,
- valor int(11) default NULL,
- PRIMARY KEY (idB),
- UNIQUE KEY valor (valor)
- ) TYPE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql INSERT
Bonjour,
Mysql-3.23.37 on Solaris 2.8.
I created this table :
CREATE TABLE livequote_last_PAR (
symbole char(20) NOT NULL default '',
id int(10) unsigned NOT NULL default '0',
date datetime NOT NULL default '-00-00 00:00:00',
last double(17,5) NOT NULL default '0.0',
Alex,
check by other queries whether it is the table which contains a
duplicate row, or if the SELECT gives a wrong answer.
Check what
SELECT * FROM ... WHERE symbole='1rPFTE';
returns.
Regards,
Heikki
At 12:04 PM 4/25/01 +0200, you wrote:
Bonjour,
Mysql-3.23.37 on Solaris 2.8.
I
Heikki,
I did the downgrade to Mysql-3.23.36. I don't have this problem with
3.23.36 !
Alex.
HT check by other queries whether it is the table which contains a
HT duplicate row, or if the SELECT gives a wrong answer.
HT Check what
HT SELECT * FROM ... WHERE symbole='1rPFTE';
HT
Heikki,
Another exemple :
mysql select * from livequote_last_PAR where symbole='1rPFTE' order by id desc;
+-+---+-+--++
| symbole | id| date| last | volume |
+-+---+-+--++
|
Heikki,
HT check by other queries whether it is the table which contains a
HT duplicate row, or if the SELECT gives a wrong answer.
HT Check what
HT SELECT * FROM ... WHERE symbole='1rPFTE';
HT returns.
Really strange :
mysql select * from livequote_last_PAR where symbole='1rPFTE' order
25 matches
Mail list logo
