RE: Primary key not unique on InnoDB table

[Travis Ard]

You obviously know best how your application will query your database, and
you may have already thought through your indexing strategy. If so, please
disregard my comments.

 

In my experience, it is not often you need separate indexes on most or all
the columns in a table (excepting very narrow tables, perhaps), so I would
think about how you anticipate the database might use each of these indexes.
Even though you may have multiple indexes available, most of the time a
database query optimizer will only choose one when deciding how to retrieve
data for a query.  So, if you have a column like first_name that is indexed,
your database engine may never use this index unless you have a query like
select * from players_master where first_name = 'xyz'.  If a column is
part of your select list, but is not used as your WHERE clause expression or
as part of a table join, indexing that column may not be a benefit. Running
EXPLAIN will tell you whether or not the index you anticipate is actually
being used for your query.

 

-Travis

 

 

From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] 
Sent: Friday, October 15, 2010 3:43 AM
To: [MySQL]; Travis Ard
Subject: Fwd: Primary key 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 travis_...@hotmail.com
Cc: [MySQL] mysql@lists.mysql.com


Hi Travis,

 

Thanks for your response.  The fields which have indexes on, can be used on
every other search, which is why I thought about creating them.  Would you
recommend against this ?

 

Cheers

Neil

On Wed, Oct 13, 2010 at 6:48 PM, Travis Ard travis_...@hotmail.com wrote:

I couldn't help but notice you have individual indexes on nearly all the
fields of your table.  If you won't be using these fields exclusively as a
join or filter condition in a query, you are unlikely to benefit from the
extra indexes and, in fact, they could slow down your inserts and add 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.
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,
 `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
 `dob` date NOT NULL,
 `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
 `retirement_date` date DEFAULT NULL,
 `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
 `estimated_value` double NOT NULL DEFAULT '0',
 `contract_wage` double NOT NULL DEFAULT '0',
 `rating` int(11) NOT NULL,
 PRIMARY KEY (`players_id`,`default_teams_id`),
 KEY `FK_players_master_countries_id` (`countries_id`),
 KEY `FK_players_master_positions_id` (`positions_id`),
 KEY `IDX_first_name` (`first_name`),
 KEY `IDX_known_as` (`known_as`),
 KEY `IDX_second_name` (`second_name`),
 KEY `IDX_dob` (`dob`),
 KEY `IDX_estimated_value` (`estimated_value`),
 KEY `IDX_contract_wage` (`contract_wage`),
 KEY `IDX_rating` (`rating`),
 KEY `FK_players_master_teams_id` (`default_teams_id`),
 CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`)
REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
 CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`)
REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
 CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci

I'm confused, I thought primary keys were always unique ?

Cheers
Neil

 

 

Re: Primary key not unique on InnoDB table

[Jo�o C�ndido de Souza Neto]

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 neil.tompk...@googlemail.com escreveu na mensagem 
news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@mail.gmail.com...
 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,
  `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dob` date NOT NULL,
  `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `retirement_date` date DEFAULT NULL,
  `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `estimated_value` double NOT NULL DEFAULT '0',
  `contract_wage` double NOT NULL DEFAULT '0',
  `rating` int(11) NOT NULL,
  PRIMARY KEY (`players_id`,`default_teams_id`),
  KEY `FK_players_master_countries_id` (`countries_id`),
  KEY `FK_players_master_positions_id` (`positions_id`),
  KEY `IDX_first_name` (`first_name`),
  KEY `IDX_known_as` (`known_as`),
  KEY `IDX_second_name` (`second_name`),
  KEY `IDX_dob` (`dob`),
  KEY `IDX_estimated_value` (`estimated_value`),
  KEY `IDX_contract_wage` (`contract_wage`),
  KEY `IDX_rating` (`rating`),
  KEY `FK_players_master_teams_id` (`default_teams_id`),
  CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`)
 REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION,
  CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`)
 REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION,
  CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
 REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
 COLLATE=utf8_unicode_ci

 I'm confused, I thought primary keys were always unique ?

 Cheers
 Neil
 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Primary key not unique on InnoDB table

[Jo�o C�ndido de Souza Neto]

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.

 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 neil.tompk...@googlemail.com escreveu na mensagem 
 news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@mail.gmail.com...
 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,
  `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dob` date NOT NULL,
  `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `retirement_date` date DEFAULT NULL,
  `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `estimated_value` double NOT NULL DEFAULT '0',
  `contract_wage` double NOT NULL DEFAULT '0',
  `rating` int(11) NOT NULL,
  PRIMARY KEY (`players_id`,`default_teams_id`),
  KEY `FK_players_master_countries_id` (`countries_id`),
  KEY `FK_players_master_positions_id` (`positions_id`),
  KEY `IDX_first_name` (`first_name`),
  KEY `IDX_known_as` (`known_as`),
  KEY `IDX_second_name` (`second_name`),
  KEY `IDX_dob` (`dob`),
  KEY `IDX_estimated_value` (`estimated_value`),
  KEY `IDX_contract_wage` (`contract_wage`),
  KEY `IDX_rating` (`rating`),
  KEY `FK_players_master_teams_id` (`default_teams_id`),
  CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`)
 REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION,
  CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`)
 REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION,
  CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
 REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
 COLLATE=utf8_unicode_ci

 I'm confused, I thought primary keys were always unique ?

 Cheers
 Neil


 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Primary key not unique on InnoDB table

[Krishna Chandra Prajapati]

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 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,
  `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dob` date NOT NULL,
  `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `retirement_date` date DEFAULT NULL,
  `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `estimated_value` double NOT NULL DEFAULT '0',
  `contract_wage` double NOT NULL DEFAULT '0',
  `rating` int(11) NOT NULL,
  PRIMARY KEY (`players_id`,`default_teams_id`),
  KEY `FK_players_master_countries_id` (`countries_id`),
  KEY `FK_players_master_positions_id` (`positions_id`),
  KEY `IDX_first_name` (`first_name`),
  KEY `IDX_known_as` (`known_as`),
  KEY `IDX_second_name` (`second_name`),
  KEY `IDX_dob` (`dob`),
  KEY `IDX_estimated_value` (`estimated_value`),
  KEY `IDX_contract_wage` (`contract_wage`),
  KEY `IDX_rating` (`rating`),
  KEY `FK_players_master_teams_id` (`default_teams_id`),
  CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`)
 REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION,
  CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`)
 REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION,
  CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
 REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
 COLLATE=utf8_unicode_ci

 I'm confused, I thought primary keys were always unique ?

 Cheers
 Neil

Re: Primary key not unique on InnoDB table

[Tompkins Neil]

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 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.
 
  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 neil.tompk...@googlemail.com escreveu na mensagem
  news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@mail.gmail.com...
  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,
   `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
   `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `dob` date NOT NULL,
   `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
   `retirement_date` date DEFAULT NULL,
   `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
   `estimated_value` double NOT NULL DEFAULT '0',
   `contract_wage` double NOT NULL DEFAULT '0',
   `rating` int(11) NOT NULL,
   PRIMARY KEY (`players_id`,`default_teams_id`),
   KEY `FK_players_master_countries_id` (`countries_id`),
   KEY `FK_players_master_positions_id` (`positions_id`),
   KEY `IDX_first_name` (`first_name`),
   KEY `IDX_known_as` (`known_as`),
   KEY `IDX_second_name` (`second_name`),
   KEY `IDX_dob` (`dob`),
   KEY `IDX_estimated_value` (`estimated_value`),
   KEY `IDX_contract_wage` (`contract_wage`),
   KEY `IDX_rating` (`rating`),
   KEY `FK_players_master_teams_id` (`default_teams_id`),
   CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY
 (`countries_id`)
  REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO
  ACTION,
   CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY
 (`positions_id`)
  REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
  ACTION,
   CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY
 (`default_teams_id`)
  REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
  ACTION
  ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
  COLLATE=utf8_unicode_ci
 
  I'm confused, I thought primary keys were always unique ?
 
  Cheers
  Neil
 
 
 



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com

Re: Primary key not unique on InnoDB table

[Jo�o C�ndido de Souza Neto]

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 
news:aanlkti=xnjcaiq7bmoxg-q+4nowdhv8uaj9dcqrol...@mail.gmail.com...
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 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.
 
  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 neil.tompk...@googlemail.com escreveu na mensagem
  news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@mail.gmail.com...
  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,
   `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
   `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `dob` date NOT NULL,
   `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
   `retirement_date` date DEFAULT NULL,
   `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
   `estimated_value` double NOT NULL DEFAULT '0',
   `contract_wage` double NOT NULL DEFAULT '0',
   `rating` int(11) NOT NULL,
   PRIMARY KEY (`players_id`,`default_teams_id`),
   KEY `FK_players_master_countries_id` (`countries_id`),
   KEY `FK_players_master_positions_id` (`positions_id`),
   KEY `IDX_first_name` (`first_name`),
   KEY `IDX_known_as` (`known_as`),
   KEY `IDX_second_name` (`second_name`),
   KEY `IDX_dob` (`dob`),
   KEY `IDX_estimated_value` (`estimated_value`),
   KEY `IDX_contract_wage` (`contract_wage`),
   KEY `IDX_rating` (`rating`),
   KEY `FK_players_master_teams_id` (`default_teams_id`),
   CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY
 (`countries_id`)
  REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE 
  NO
  ACTION,
   CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY
 (`positions_id`)
  REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE 
  NO
  ACTION,
   CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY
 (`default_teams_id`)
  REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
  ACTION
  ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
  COLLATE=utf8_unicode_ci
 
  I'm confused, I thought primary keys were always unique ?
 
  Cheers
  Neil
 
 
 



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Primary key not unique on InnoDB table

[Tompkins Neil]

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 autoincrement. Am I completely
 wrong?

 --
 João Cândido de Souza Neto

 Tompkins Neil neil.tompk...@googlemail.com escreveu na mensagem
 news:aanlkti=xnjcaiq7bmoxg-q+4nowdhv8uaj9dcqrol...@mail.gmail.com...
 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 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.
  
   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 neil.tompk...@googlemail.com escreveu na mensagem
   news:aanlkti=-1wvuxdfsq4km6rfz0wsrlpphug1bnt4x9...@mail.gmail.com...
   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,
`second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`dob` date NOT NULL,
`countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
`retirement_date` date DEFAULT NULL,
`positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
`estimated_value` double NOT NULL DEFAULT '0',
`contract_wage` double NOT NULL DEFAULT '0',
`rating` int(11) NOT NULL,
PRIMARY KEY (`players_id`,`default_teams_id`),
KEY `FK_players_master_countries_id` (`countries_id`),
KEY `FK_players_master_positions_id` (`positions_id`),
KEY `IDX_first_name` (`first_name`),
KEY `IDX_known_as` (`known_as`),
KEY `IDX_second_name` (`second_name`),
KEY `IDX_dob` (`dob`),
KEY `IDX_estimated_value` (`estimated_value`),
KEY `IDX_contract_wage` (`contract_wage`),
KEY `IDX_rating` (`rating`),
KEY `FK_players_master_teams_id` (`default_teams_id`),
CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY
  (`countries_id`)
   REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE
   NO
   ACTION,
CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY
  (`positions_id`)
   REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE
   NO
   ACTION,
CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY
  (`default_teams_id`)
   REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE
 NO
   ACTION
   ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
   COLLATE=utf8_unicode_ci
  
   I'm confused, I thought primary keys were always unique ?
  
   Cheers
   Neil
  
  
  
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
  http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
 
 



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com

Re: Primary key not unique on InnoDB table

[Tompkins Neil]

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 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 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,
  `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dob` date NOT NULL,
  `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `retirement_date` date DEFAULT NULL,
  `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `estimated_value` double NOT NULL DEFAULT '0',
  `contract_wage` double NOT NULL DEFAULT '0',
  `rating` int(11) NOT NULL,
  PRIMARY KEY (`players_id`,`default_teams_id`),
  KEY `FK_players_master_countries_id` (`countries_id`),
  KEY `FK_players_master_positions_id` (`positions_id`),
  KEY `IDX_first_name` (`first_name`),
  KEY `IDX_known_as` (`known_as`),
  KEY `IDX_second_name` (`second_name`),
  KEY `IDX_dob` (`dob`),
  KEY `IDX_estimated_value` (`estimated_value`),
  KEY `IDX_contract_wage` (`contract_wage`),
  KEY `IDX_rating` (`rating`),
  KEY `FK_players_master_teams_id` (`default_teams_id`),
  CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`)
 REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION,
  CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`)
 REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION,
  CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
 REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
 COLLATE=utf8_unicode_ci

 I'm confused, I thought primary keys were always unique ?

 Cheers
 Neil

Re: Primary key not unique on InnoDB table

[Shawn Green (MySQL)]

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` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
   `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
   `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `dob` date NOT NULL,
   `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
   `retirement_date` date DEFAULT NULL,
   `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
   `estimated_value` double NOT NULL DEFAULT '0',
   `contract_wage` double NOT NULL DEFAULT '0',
   `rating` int(11) NOT NULL,
   PRIMARY KEY (`players_id`,`default_teams_id`),
   KEY `FK_players_master_countries_id` (`countries_id`),
   KEY `FK_players_master_positions_id` (`positions_id`),
   KEY `IDX_first_name` (`first_name`),
   KEY `IDX_known_as` (`known_as`),
   KEY `IDX_second_name` (`second_name`),
   KEY `IDX_dob` (`dob`),
   KEY `IDX_estimated_value` (`estimated_value`),
   KEY `IDX_contract_wage` (`contract_wage`),
   KEY `IDX_rating` (`rating`),
   KEY `FK_players_master_teams_id` (`default_teams_id`),
   CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`)
REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
   CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`)
REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
   CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci

I'm confused, I thought primary keys were always unique ?

Cheers
Neil



I see no reason why this won't work. Show us some duplicate data and I 
may be able to explain how to fix your definition.


--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Primary key not unique on InnoDB table

[Shawn Green (MySQL)]

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 Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Primary key not unique on InnoDB table

[Tompkins Neil]

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 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 Green
 MySQL Principal Technical Support Engineer
 Oracle USA, Inc.
 Office: Blountville, TN

RE: Primary key not unique on InnoDB table

[Travis Ard]

I couldn't help but notice you have individual indexes on nearly all the
fields of your table.  If you won't be using these fields exclusively as a
join or filter condition in a query, you are unlikely to benefit from the
extra indexes and, in fact, they could slow down your inserts and add 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.
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,
  `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dob` date NOT NULL,
  `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `retirement_date` date DEFAULT NULL,
  `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `estimated_value` double NOT NULL DEFAULT '0',
  `contract_wage` double NOT NULL DEFAULT '0',
  `rating` int(11) NOT NULL,
  PRIMARY KEY (`players_id`,`default_teams_id`),
  KEY `FK_players_master_countries_id` (`countries_id`),
  KEY `FK_players_master_positions_id` (`positions_id`),
  KEY `IDX_first_name` (`first_name`),
  KEY `IDX_known_as` (`known_as`),
  KEY `IDX_second_name` (`second_name`),
  KEY `IDX_dob` (`dob`),
  KEY `IDX_estimated_value` (`estimated_value`),
  KEY `IDX_contract_wage` (`contract_wage`),
  KEY `IDX_rating` (`rating`),
  KEY `FK_players_master_teams_id` (`default_teams_id`),
  CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`)
REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
  CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`)
REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION,
  CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
COLLATE=utf8_unicode_ci

I'm confused, I thought primary keys were always unique ?

Cheers
Neil


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Primary key not unique on InnoDB table

[Tompkins Neil]

Hi Travis,

Thanks for your response.  The fields which have indexes on, can be used on
every other search, which is why I thought about creating them.  Would you
recommend against this ?

Cheers
Neil

On Wed, Oct 13, 2010 at 6:48 PM, Travis Ard travis_...@hotmail.com wrote:

 I couldn't help but notice you have individual indexes on nearly all the
 fields of your table.  If you won't be using these fields exclusively as a
 join or filter condition in a query, you are unlikely to benefit from the
 extra indexes and, in fact, they could slow down your inserts and add 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.
 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,
  `second_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `known_as` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dob` date NOT NULL,
  `countries_id` char(2) COLLATE utf8_unicode_ci NOT NULL,
  `retirement_date` date DEFAULT NULL,
  `positions_id` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
  `estimated_value` double NOT NULL DEFAULT '0',
  `contract_wage` double NOT NULL DEFAULT '0',
  `rating` int(11) NOT NULL,
  PRIMARY KEY (`players_id`,`default_teams_id`),
  KEY `FK_players_master_countries_id` (`countries_id`),
  KEY `FK_players_master_positions_id` (`positions_id`),
  KEY `IDX_first_name` (`first_name`),
  KEY `IDX_known_as` (`known_as`),
  KEY `IDX_second_name` (`second_name`),
  KEY `IDX_dob` (`dob`),
  KEY `IDX_estimated_value` (`estimated_value`),
  KEY `IDX_contract_wage` (`contract_wage`),
  KEY `IDX_rating` (`rating`),
  KEY `FK_players_master_teams_id` (`default_teams_id`),
  CONSTRAINT `FK_players_master_countries_id` FOREIGN KEY (`countries_id`)
 REFERENCES `countries` (`countries_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION,
  CONSTRAINT `FK_players_master_positions_id` FOREIGN KEY (`positions_id`)
 REFERENCES `positions` (`positions_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION,
  CONSTRAINT `FK_players_master_teams_id` FOREIGN KEY (`default_teams_id`)
 REFERENCES `teams_master` (`teams_id`) ON DELETE NO ACTION ON UPDATE NO
 ACTION
 ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
 COLLATE=utf8_unicode_ci

 I'm confused, I thought primary keys were always unique ?

 Cheers
 Neil

Re: Primary key / foreign key question

[Martijn Tonies]

Hello Steve,

 Ok, I'm a little new a this, so be gentle!! :)

 I was looking into the InnoDB engine for some tables I have, and would
like
 to use the PK/FK on some of the data.

 It appears that the PK/FK is mainly used for updating/deleting data,
 correct?  I can't use it to retreive data from multiple tables and have
them
 linked, other than a join?  Or am I wrong?

It's mainly used for generating exceptions when the data is about to
become invalid if you delete something that is used by another table.
You can set it to automatically delete child-data or prevent these
deletions so that the data in your database stays consistent.

When retrieving data, you need to JOIN tables yourself into a result set.

Alternatively, you can create a server side object called a VIEW that
is basically a result set that looks like a single table.

 Is there a good visual tool that I can use to build my tables, create the
 PK/FK definations, and test how inserts, deletes, updates affect all the
 tables involved??

What OS? For example, on Windows, we offer a free Lite version of
our developer tool Database Workbench.

Martijn Tonies
Database Workbench Lite for MySQL - FREE developer tool for MySQL!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: PRIMARY KEY and CreationTime columns

[Rob Wultsch]

On Sun, Mar 16, 2008 at 5:48 AM, Waynn Lue [EMAIL PROTECTED] wrote:
 Say I have this schema

  CREATE TABLE temp (
   EntityId BIGINT AUTO_INCREMENT PRIMARY KEY,
   CreationTime DEFAULT NOW()
  );

  Now let's say I want to find all rows created within the last 24 hours.  If 
 I do

  select * from temp where CreationTime  DATE_SUB(NOW(), INTERVAL 24 HOUR)

  that's going to do a full table scan to find out, even though there's
  already an implicit ordering in EntityId (this is of course assuming I
  don't manually set CreationTime to something else).  Is there any way
  to take advantage of the fact that there's a primary key index on
  entityId, or do I have to put a secondary index on CreationTime?

  Thanks,
  Waynn

First off you have not specified a data type for CreationTime . You
probably meant timestamp.

You need a second index on CreationTime.

-- 
Rob Wultsch

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary key

[Steffan A. Cline]

 In the last episode (Jan 11), Steffan A. Cline said:
 Is there anyway to do a SELECT or DESCRIBE or SHOW statement which can
 return the primary key field of a specified table?
 
 Pseudo code: select primary_key_field_name from mytable.
 
 SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE TABLE_SCHEMA='myschema' AND TABLE_NAME='mytable'
   AND CONSTRAINT_NAME='PRIMARY'
   ORDER BY ORDINAL_POSITION;
 
 For multi-column indexes, you will get multiple rows back.
 
 You should also use show create table mytable or show keys from
 mytable but you'll have to do extra parsing.

Dan,

Great! This is what I was after... I am building a class/ctype for Lasso for
a user db. When it is instantiated, it loads the structure into the type
from the db. Now, I have the primary key as the ID of the type I need to
separate it from the rest of the columns. SO, I guess now I'll work on
unless you have it handy where I can return all columns EXCEPT the primary
key.

Thanks

Steffan

---
T E L  6 0 2 . 5 7 9 . 4 2 3 0 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
[EMAIL PROTECTED] Phoenix, Az
http://www.ExecuChoice.net  USA
AIM : SteffanC  ICQ : 57234309
  Lasso Partner Alliance Member
---



 From: Dan Nelson [EMAIL PROTECTED]
 Date: Fri, 12 Jan 2007 01:00:03 -0600
 To: Steffan A. Cline [EMAIL PROTECTED]
 Cc: mysql@lists.mysql.com
 Subject: Re: Primary key
 
 In the last episode (Jan 11), Steffan A. Cline said:
 Is there anyway to do a SELECT or DESCRIBE or SHOW statement which can
 return the primary key field of a specified table?
 
 Pseudo code: select primary_key_field_name from mytable.
 
 SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   WHERE TABLE_SCHEMA='myschema' AND TABLE_NAME='mytable'
   AND CONSTRAINT_NAME='PRIMARY'
   ORDER BY ORDINAL_POSITION;
 
 For multi-column indexes, you will get multiple rows back.
 
 You should also use show create table mytable or show keys from
 mytable but you'll have to do extra parsing.
 
 -- 
 Dan Nelson
 [EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary key

[Dan Nelson]

In the last episode (Jan 11), Steffan A. Cline said:
 Is there anyway to do a SELECT or DESCRIBE or SHOW statement which can
 return the primary key field of a specified table?
 
 Pseudo code: select primary_key_field_name from mytable.

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
  WHERE TABLE_SCHEMA='myschema' AND TABLE_NAME='mytable' 
  AND CONSTRAINT_NAME='PRIMARY'
  ORDER BY ORDINAL_POSITION;

For multi-column indexes, you will get multiple rows back.

You should also use show create table mytable or show keys from
mytable but you'll have to do extra parsing.

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: primary key

[Alec . Cawley]

Ronan [EMAIL PROTECTED] wrote on 16/02/2006 11:56:18:

 Im trying to set up a primary key of server(text), date (date), hour 
 (small int) but when i try to include the server field in the key it 
 replies with
 
 ALTER TABLE `exim` DROP PRIMARY KEY ,
 ADD PRIMARY KEY ( `date` , `hour` , `server` )
 
 #1170 - BLOB/TEXT column 'server' used in key specification without a 
 key length
 
 i have googled, but not much is relevant to my example i dont think..
 
 
 CREATE TABLE `exim` (
`date` date NOT NULL default '-00-00',
`server` longtext NOT NULL,
`hour` tinyint(4) NOT NULL default '0',
`count` smallint(6) NOT NULL default '0',
PRIMARY KEY  (`date`,`hour`),
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='exim realtime stats';

A LONGTEXT field may be up to 4 Gigabytes long. MySQL cannot (and, I would 
think, should not) include thenwhole 4Gb in the index. It therefore needs 
you to indicate how many characters of the server field it should actually 
use in the index. To get it to use only the first 64 characters in the 
key, you should put in server(64) (the single quotes you are using are 
necesary only if you wish to give a colum the same name as a reserved 
wioord - a vary bad practice). 

However, if you are using it as a PRIMARY KEY or UNIQUE KEY, *you* must 
guarantee that those firat 64 characters are unique. within any given date 
and hour i.e., I would guess, that your servers are unique within the 
first 64 (or however many you choose) characters.

May I suggest that a more conventional way to do what I think you are 
doing woiuld be to have two tables. Allocate each server a number, and put 
the number in the exim table. Then have another table to convert the 
server name to a number. It is then trivially easy to use that table to 
convert from server number to name or vice versa. And the server name no 
l;onger has to be unique in the first N characters: as long as the names 
differ, the table will work.

Alec

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: primary key

[Martijn Tonies]

Hi there,


 Im trying to set up a primary key of server(text), date (date), hour 
 (small int) but when i try to include the server field in the key it 
 replies with
 
 ALTER TABLE `exim` DROP PRIMARY KEY ,
 ADD PRIMARY KEY ( `date` , `hour` , `server` )
 
 #1170 - BLOB/TEXT column 'server' used in key specification without a 
 key length
 
 i have googled, but not much is relevant to my example i dont think..

Well, you could start with the documentation.

server apparently, is a blob-type column. 

Lo and behold, the table definition:
 
 CREATE TABLE `exim` (
`date` date NOT NULL default '-00-00',
`server` longtext NOT NULL,

Yep. A BLOB column. Why, I wonder.

Either way - check the error message, read it. From what I can read
from it - and the MySQL documentation - you cannot just create
a constraint/index on a BLOB column.

`hour` tinyint(4) NOT NULL default '0',
`count` smallint(6) NOT NULL default '0',
PRIMARY KEY  (`date`,`hour`),
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='exim realtime stats';

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary Key question

[Alec . Cawley]

Haisam K. Ido [EMAIL PROTECTED] wrote on 01/07/2005 15:04:01:

 
 I've created the following table (server 4.1 in win2k)
 
 CREATE TABLE `os` (
`id` tinyint(10) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`description` varchar(255) default NULL,
PRIMARY KEY  (`id`,`name`)
 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
 and was very surprised that I can do the following twice.  Should'nt 
 this be rejected since name is a primary key ad has already been used?
 
 INSERT INTO os (name,description) VALUES ( 'winxp','winxp');

No. What you have requested is that the combination of id AND name be 
unique. Since id is auto-increment, every record will be unique unless you 
manually force the id to an old value. I guess you want the values to be 
separately unique, in which case you want
PRIMARY KEY (id), UNIQUE (name) 

Alec



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary Key question

[Haisam K. Ido]

so if I do want 'name' to be unique I must not make it primary, just 
simply unique, since my primary key is for id and name simultaneously.


[EMAIL PROTECTED] wrote:

your primary key is based on your (auto-increment) id and the name,

 PRIMARY KEY  (`id`,`name`)

so your two entries would be:

  1,winxp
  2,winxp

  
there's no key conflict/duplication there.


by the way, you do realize what the max range is on the (signed) tinyint
(for your id), correct?

 
 Original Message 



Date: Friday, July 01, 2005 10:04:01 AM -0400
From: Haisam K. Ido [EMAIL PROTECTED]
To: mysql@lists.mysql.com 
Subject: Primary Key question



I've created the following table (server 4.1 in win2k)

CREATE TABLE `os` (
  `id` tinyint(10) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `description` varchar(255) default NULL,
  PRIMARY KEY  (`id`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

and was very surprised that I can do the following twice.  Should'nt
this be rejected since name is a primary key ad has already been used?

INSERT INTO os (name,description) VALUES ( 'winxp','winxp');


--



-- End Original Message --


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary Key Question

[SGreen]

Hendro Suryawan [EMAIL PROTECTED] wrote on 06/16/2005 06:53:31 PM:

 Hi all,
 I have table with primary key on field PO,BrgId, NOSP but when i try
 insert several new reccord with field NOSP = '', mysql will accept the
 new reccord without complaint error.
 Is this normal behavior? 

As long as the combination of the values {PO, BrgId, NoSP} does not yet 
exist on the table, you should be able to add rows. Are you saying that, 
for example, that the combination {'somePOvalue', 4, ''} already exists on 
your table and it's allowing you to add a second row with the same 
combination of values?

 My perception if i have primary key on the
 three field the three field must be not empty.  I try to alter the field
 NOSP with syntax :
 Alter table BrgIn2 Change NOSP NOSP Varchar(20) NOT NULL

 but if i looked table definition mysql always add default '' in the
 definition. How to tell mysql not to add default '', i want to this
 field always not null or ''. I use mysql 4.1.11 on FC3 X86_64.
 Can anyone help? Thanks in advance.
 regards,

 Hendro

 Table   Create Table
 --  
 BrgIn2  CREATE TABLE `BrgIn2` (
 `PO` varchar(17) NOT NULL default '',
 `BrgId` int(4) NOT NULL default '0',
 `NoSP` varchar(20) NOT NULL default '',
snip
 PRIMARY KEY  (`PO`,`BrgId`,`NoSP`),
 KEY `BrgIn2SPBrg` (`NoSP`,`BrgId`,`Qty`),
 KEY `BrgId` (`BrgId`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1

The empty string ('') is not a NULL value. It represents a string that 
contains no characters. A NULL value indicates the lack of information, a 
state of non-existence. 

For instance: Imagine you have a table, Person, and the table has fields 
to hold a first name, a middle name, and a last name. If you know for a 
fact that some person does not have a middle name, you would use a '' 
(empty string) for the MiddleName value of that person. However, if you 
don't have a middle name on a data entry form for a particular person (a 
middle name may exist but you didn't get it as part of your data), you 
would use a NULL value to indicate the absence of information. 

I think what you would like to have is a CHECK constraint on the `NoSP` 
field that requires that all new or updated values have a certain minimum 
length (LENGTH(`NoSP`)  0). However, MySQL does not yet support CHECK 
constraints (see the TODO lists). Until it does, you will need to enforce 
that particular restriction using your application code (any version) or 
write that check into a TRIGGER (v5.0+).

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Primary Key Question

[Peter Brawley]

Hendro,

In SQL an empty string is not null.

PB

Hendro Suryawan wrote:


Hi all,
I have table with primary key on field PO,BrgId, NOSP but when i try
insert several new reccord with field NOSP = '', mysql will accept the
new reccord without complaint error.
Is this normal behavior? My perception if i have primary key on the
three field the three field must be not empty.  I try to alter the field
NOSP with syntax :
   Alter table BrgIn2 Change NOSP NOSP Varchar(20) NOT NULL

but if i looked table definition mysql always add default '' in the
definition. How to tell mysql not to add default '', i want to this
field always not null or ''. I use mysql 4.1.11 on FC3 X86_64.
Can anyone help? Thanks in advance.
regards,

Hendro

Table   Create Table
--  
BrgIn2  CREATE TABLE `BrgIn2` (
 `PO` varchar(17) NOT NULL default '',
 `BrgId` int(4) NOT NULL default '0',
 `NoSP` varchar(20) NOT NULL default '',
 `Spesifikasi` varchar(100) default NULL,
 `Qty` decimal(10,3) NOT NULL default '0.000',
 `Price` decimal(19,4) NOT NULL default '0.',
 `Disc` decimal(6,4) default NULL,
 `DPP` decimal(10,4) default NULL,
 `Tax` decimal(6,4) default '0.',
 `pph` decimal(6,4) default '0.',
 `Kurs` decimal(10,4) default NULL,
 `ShipDate` date default NULL,
 `Currcy` varchar(10) NOT NULL default '',
 `Keterangan` blob,
 `Terima` decimal(9,2) default NULL,
 `Periode` varchar(7) default NULL,
 `TglInput` date default NULL,
 `Operators` varchar(50) default NULL,
 `Workstation` varchar(30) default NULL,
 PRIMARY KEY  (`PO`,`BrgId`,`NoSP`),
 KEY `BrgIn2SPBrg` (`NoSP`,`BrgId`,`Qty`),
 KEY `BrgId` (`BrgId`)
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1






--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.7.6/19 - Release Date: 6/16/2005


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: primary key performance

[Philippe Poelvoorde]

Hi,
- 10 products in both cases. One time the column is a MediumInt, the
other time a BigInt. I know there is a difference in disk space usage, but
is there also one in performance at all ?
I'm not sure, this apply to your case. I had set a unique index on a 
char(50) and it was 2x slower than a varchar(50). My guest is that it 
use more disk seeks to retrieve all data needed in the index. So I would 
say that's the same about mediumint/bigint.

(in all case be sure to do optimize table your_table)
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: primary key performance

[Brent Baisley]

A varchar will take up less disk space than a char. A char is padded to 
fill it's length, so a index on char will be much larger than a 
varchar, depending on content.

Numbers work differently. An index on a number column should be faster 
than the same sized char or varchar column. First a foremost, make sure 
your column will accommodate your needs, both current and future. Then 
try to get performance from your structure. Remember, if you are only 
storing positive numbers, make it unsigned, you essentially double your 
capacity to store positive numbers without changing the column type.

On Jan 13, 2005, at 4:37 AM, Philippe Poelvoorde wrote:
Hi,
- 10 products in both cases. One time the column is a MediumInt, 
the
other time a BigInt. I know there is a difference in disk space 
usage, but
is there also one in performance at all ?
I'm not sure, this apply to your case. I had set a unique index on a 
char(50) and it was 2x slower than a varchar(50). My guest is that it 
use more disk seeks to retrieve all data needed in the index. So I 
would say that's the same about mediumint/bigint.

(in all case be sure to do optimize table your_table)
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search  Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary key error

[Rhino]

- Original Message - 
From: DBS [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, November 21, 2004 7:37 AM
Subject: Primary key error


 Hi list,

 MySQL newbie here and am using Navicat to learn how to manage a database
for
 a test OS shopping cart.  I got the below error message after importing a
 table into the database.  Can anyone tell me what I could do to correct
the
 problem?  Would I need to add an additional column to the table in
question
 for the primary keys, one for each row entry?

 Zen_products does not have a primary key.  Updates to this table will be
 done using the following pseudo statement:
 UPDATE zen_products SET ModifiedFieldsAndValues Where
  AllFieldsAndOldValues
 Updates to a record in this table may update more than one record.

You may not need to add any columns to the table, although I can't say for
sure since you don't provide a definition of the existing table or describe
the data in it.

MySQL or Navicat wants your table to have a primary key, which it apparently
doesn't have. The good news is that you can add a primary key to an existing
table, even after it has data in it. The primary key definition can identify
a single column or a combination of columns as the primary key. Here are
examples of both:

alter table mytable add primary key (id);

alter table mytable add primary key(area_code, phone_number);

You need to look at your table definition and choose a column or combination
of columns that uniquely identifies each row in the table. Then write and
execute the appropriate ALTER TABLE statement. If the ALTER TABLE statement
works, it should prove that your analysis was correct and you have chosen an
appropriate column or columns as the primary key; if the ALTER TABLE fails,
it will probably be because you have analyzed the data incorrectly and
chosen something that isn't unique for the data in the table.

If you can't find a unique column or combination of columns in your column,
you may have to add an additional column or columns to the table to ensure
that a primary key is possible on the table, then add the primary key via
the ALTER TABLE statement.

Choosing a good primary key is a non-trivial task; if you have no experience
with data modelling, particularly normalization, you should get some help
from someone who has this experience. It is not particularly hard to do but
if you have no experience, it is difficult to explain briefly how to do it
in a way that you are likely to understand.

If you have no one to help you, you can try posting a detailed description
of your data and perhaps someone on this mailing list can help you figure
out the best primary key for your data.

Rhino




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary Key

[Josh Trutwin]

On Mon, 10 May 2004 11:15:25 -0300
Ronan Lucio [EMAIL PROTECTED] wrote:

 Is the Primary Key Column mandatory?
 
 Supposing:
 If I have two tables: Clients and Cars, and a third table
 Clients_R_Cars, that is a relationship between Clients and Cars.
 
 I only need to know what cars the clients have.
 So, I just need to two columns CliCar_ClientsID and
 CliCar_CarsID, the will be my index keys.
 
 Even thus do I need to create a Primary Key Column CliCar_ID?

It is not mandatory to have a primary key, but you will have to handle duplicate 
rows in your front end program.  Having primary keys is generally a good idea.  In the 
situation that you are talking about, you can have a composite primary key (a primary 
key with more than one colunm) in your Clients_R_Cars table that is (Client_ID, 
Car_ID).  This says that in your Clients_R_Cars table no two rows can have the same 
Client_Id AND Car_ID.  Not sure if this is what you want or not.  

CREATE TABLE Clients_R_Cars (
   Client_ID INT NOT NULL,
   Car_ID INT NOT NULL,
   PRIMARY KEY (Client_ID, Car_ID)
);

If you really wanted to do this the relational way you would use InnoDB (or BDB) and 
use foreign keys as well.

CREATE TABLE Clients_R_Cars (
   Client_ID INT NOT NULL,
   Car_ID INT NOT NULL,
   INDEX client_id_ind (Client_ID),
   INDEX car_id_ind (Car_ID),
   FOREIGN KEY (Client_Id) REFERENCES Client(Client_Id) ON DELETE CASCADE ON UPDATE 
CASCADE,
   FOREIGN KEY (Car_Id) REFERENCES Car(Car_Id) ON DELETE CASCADE ON UPDATE CASCADE,
   PRIMARY KEY (Client_ID, Car_ID)
) TYPE=InnoDB;

Josh

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary Key

[Roger Baklund]

* Ronan Lucio
 Is the Primary Key Column mandatory?

 Supposing:
 If I have two tables: Clients and Cars, and a third table Clients_R_Cars,
 that is a relationship between Clients and Cars.

 I only need to know what cars the clients have.
 So, I just need to two columns CliCar_ClientsID and CliCar_CarsID,
 the will be my index keys.

 Even thus do I need to create a Primary Key Column CliCar_ID?

No, you don't need to do that. You can create a primary key based on the two
columns, (CliCar_ClientsID, CliCar_CarsID) or (CliCar_CarsID,
CliCar_ClientsID). Sometimes it is usefull to define both of these compound
indexes, then of course only one of them can be PRIMARY, the other is
defined as an UNIQUE index. You would get the same effect by defining both
as UNIQUE, but having a distinct primary key is 'cleaner'.

--
Roger


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: PRIMARY KEY in mysql 4.0.18

[Paul DuBois]

At 15:02 -0300 3/17/04, Geilson Coutinho Figueiredo wrote:
Hi,

   I would like to know what happen when I create an table without a 
Primary Key. Does MySQL create an hide primary key?
For InnoDB and BDB, yes.

Otherwise, no.

--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
MySQL Users Conference: April 14-16, 2004
http://www.mysql.com/uc2004/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: primary key - user entered/auto increment

[Victoria Reznichenko]

David Johnston [EMAIL PROTECTED] wrote:
 I am starting to design a database in sql, to replace a flat file db.
 
 The database holds records, currenlty there is a new database for each 
 record, so each record for each project starts with a id and increments.
 
 I want to create a sql database that will hold all records for all 
 projects, so I'd like to have a primary key -
 
 xxx/x
 
 The first three digits would be the project ID, the following 5 digits 
 would be the records unique number.
 
 Is it possilbe to create a field, that I can enter the first three 
 digits, while the following 5 auto increment?
 
 The result would be that all projects would still have records starting 
 from 1, and auto incrementing.

If I've got you right you should specify auto_increment on the secondary column of 
multiple-column index:
http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html

For example:

mysql create table t1(
- p_id int not null,
- id int not null auto_increment,
- primary key (p_id, id));
Query OK, 0 rows affected (0.07 sec)

mysql insert into t1 values
- (111,NULL),
- (222,NULL),
- (111,NULL);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql select * from t1;
+--++
| p_id | id |
+--++
|  111 |  1 |
|  111 |  2 |
|  222 |  1 |
+--++
3 rows in set (0.01 sec)



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary Key

[Martijn Tonies]

Hi,

 I saw an example of creating tables (see below).  I
 wonder what the primary key (user_name, role_name) in
 the table user_roles means?  Does it mean that both
 user_name and role_name are the primary key of the
 user_roles table?  How does a table have two primary
 keys?

 create table users (
   user_name varchar(15) not null primary key,
   user_pass varchar(15) not null
 );

 create table user_roles (
   user_name varchar(15) not null,
   role_name varchar(15) not null,
   primary key (user_name, role_name)
 );

A table cannot have two primary keys, only zero or one.

   primary key (user_name, role_name)

This primary key is a compound primary key - a constraint
for multiple columns. This means that every combination of
values needs to be unique.

These are valid (user, role):
martijn, admin
caroline, admin
caroline, poweruser

As you can see, you, as a user, can have multiple roles.
However, you cannot enter such a row twice:

(invalid):
caroline, poweruser
caroline, poweruser

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: primary key type is BLOB

[Martijn Tonies]

Hi,

May be the answer is in the documentation but I did not find it. So
how could I make my BLOB column a primary key?

My guess is you cannot.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: primary key type is BLOB

[Egor Egorov]

Lemasson Sylvain [EMAIL PROTECTED] wrote:

 I try to add a primary key on a BLOB column and I have got an error (ERROR 1170: 
 BLOB column 'value5' used in key specification without a key length).
 May be the answer is in the documentation but I did not find it. So how could I make 
 my BLOB column a primary key?

You can create index on the prefix of the column:
http://www.mysql.com/doc/en/Indexes.html



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: primary key type is BLOB

[Reverend Deuce]

Lemasson,

When you refer to a column as a primary key, I tend to think that this key
will be your main relationship point to the data in your table. Now, when
you combine the primary key concept with a BLOB (binary large object), it
seems to me that you are almost defeating the purpose of a primary key
(you'd be making the index basically useless). I have never heard of anybody
making a BLOB a primary key, so I could not comment on whether or not it is
actually do-able or possible with MySQL. If it is, it shouldn't be.

I would recommend you focus your design on creating inter-table
relationships based around smaller keys that index well, such as integers
and keep the BLOBs in a separate table, away from your frequently read data.

-- R.



- Original Message - 
From: Lemasson Sylvain [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, December 08, 2003 8:23 AM
Subject: primary key type is BLOB


I try to add a primary key on a BLOB column and I have got an error (ERROR
1170: BLOB column 'value5' used in key specification without a key length).
May be the answer is in the documentation but I did not find it. So how
could I make my BLOB column a primary key?

Lemasson Sylvain
Ingénieur développeur
[EMAIL PROTECTED]
tel: 01.48.63.27.27
MAK-SYSTEM SERVICES
Groupe MAK-SYSTEM
Paris Nord 2 - 13, rue de la Perdrix
BP 50035-95946 Roissy CDG Cédex
FRANCE


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary Key Constraint Problem

[Victoria Reznichenko]

Jacques Buitendag [EMAIL PROTECTED] wrote:
 
 I have recently discovered that when you place a primary key constraint on a column 
 of type CHAR(2) the constraint causes the following to happen when I add a new row 
 to the table:
 
 if a key (AA) is already in the table then I can not add Aa, aA or aa 
 
 this seams a bit wrong as 'A' != 'a' in the representation of CHAR 
 
 Is this the way that primary key constraints are specified in ANSI SQL ? and is 
 there a way of inserting AA and aa

CHAR columns are compared in case-insensitive fashion. Declare your column as BINARY:
http://www.mysql.com/doc/en/CHAR.html


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

re: Primary key

[Victoria Reznichenko]

On Friday 04 April 2003 15:37, Grégoire Dubois wrote:

 In the following table, I declare ID as a PRIMARY KEY.  Is it then
 necessary to add the parameters NOT NULL AUTO_INCREMENT?

 CREATE TABLE company (
 ID INT NOT NULL AUTO_INCREMENT,

 name VARCHAR(30),

 admin_ID INT,

 PRIMARY KEY ID,
 INDEX admin_ID,
 );

 Or can I just declare the table like that? Does creating a primary key
 on an int immediatly involve this one to be not null, and to
 auto-incrИment?

NOT NULL - yes, but if you want to have AUTO_INCREMENT column you should 
declare it as AUTO_INCREMENT.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary key

[Grgoire Dubois]

Victoria Reznichenko wrote:

On Friday 04 April 2003 15:37, Grgoire Dubois wrote:

 

In the following table, I declare ID as a PRIMARY KEY.  Is it then
necessary to add the parameters NOT NULL AUTO_INCREMENT?
CREATE TABLE company (
   ID INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(30),

   admin_ID INT,

   PRIMARY KEY ID,
   INDEX admin_ID,
);
Or can I just declare the table like that? Does creating a primary key
on an int immediatly involve this one to be not null, and to
auto-incrment?
   

NOT NULL - yes, but if you want to have AUTO_INCREMENT column you should 
declare it as AUTO_INCREMENT.

 

Thank you very much for your reply.
But I read that a PRIMARY KEY is a globally unique identifier for a 
table. As it is an identifier, it should never be null (ok, that's what 
you said), and it should be unique... Then, if I don't set 
AUTO_INCREMENT to my column ID, it still should be unique, and then 
increment itself... No?

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary key

[Bruce Feist]

Grgoire Dubois wrote:

Victoria Reznichenko wrote:

On Friday 04 April 2003 15:37, Grgoire Dubois wrote:

In the following table, I declare ID as a PRIMARY KEY.  Is it then
necessary to add the parameters NOT NULL AUTO_INCREMENT?
CREATE TABLE company (
   ID INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(30),

   admin_ID INT,

   PRIMARY KEY ID,
   INDEX admin_ID,
);
Or can I just declare the table like that? Does creating a primary key
on an int immediatly involve this one to be not null, and to
auto-incrment?   


NOT NULL - yes, but if you want to have AUTO_INCREMENT column you 
should declare it as AUTO_INCREMENT.
Thank you very much for your reply.
But I read that a PRIMARY KEY is a globally unique identifier for a 
table. As it is an identifier, it should never be null (ok, that's 
what you said), and it should be unique... Then, if I don't set 
AUTO_INCREMENT to my column ID, it still should be unique, and then 
increment itself... No? 
Not all unique identifiers are computer-generated.  If you leave off 
AUTO_INCREMENT but still make it a primary key, uniqueness will be 
enforced on whatever the application(s) enter(s) as the value, but 
nothing will be plugged in.

Bruce Feist



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary key

[Grgoire Dubois]

Bruce Feist wrote:

Grgoire Dubois wrote:

Victoria Reznichenko wrote:

On Friday 04 April 2003 15:37, Grgoire Dubois wrote:

In the following table, I declare ID as a PRIMARY KEY.  Is it then
necessary to add the parameters NOT NULL AUTO_INCREMENT?
CREATE TABLE company (
   ID INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(30),

   admin_ID INT,

   PRIMARY KEY ID,
   INDEX admin_ID,
);
Or can I just declare the table like that? Does creating a primary key
on an int immediatly involve this one to be not null, and to
auto-incrment?   


NOT NULL - yes, but if you want to have AUTO_INCREMENT column you 
should declare it as AUTO_INCREMENT.


Thank you very much for your reply.
But I read that a PRIMARY KEY is a globally unique identifier for a 
table. As it is an identifier, it should never be null (ok, that's 
what you said), and it should be unique... Then, if I don't set 
AUTO_INCREMENT to my column ID, it still should be unique, and then 
increment itself... No? 


Not all unique identifiers are computer-generated.  If you leave off 
AUTO_INCREMENT but still make it a primary key, uniqueness will be 
enforced on whatever the application(s) enter(s) as the value, but 
nothing will be plugged in.

Bruce Feist



Ok, I'm not sure Iunderstood the real (technical) reason, but I 
understood that I had to use the AUTO_INCREMENT to be sure of a nice 
functionning of my database.
Thank you very much all.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Primary key

[dpgirago]

(snip)


 Does creating a primary key
 on an int immediatly involve this one to be not null, and to
 auto-increment?



 NOT NULL - yes, but if you want to have AUTO_INCREMENT column you
 should declare it as AUTO_INCREMENT.


 Thank you very much for your reply.
 But I read that a PRIMARY KEY is a globally unique identifier for a
 table. As it is an identifier, it should never be null (ok, that's
 what you said), and it should be unique... Then, if I don't set
 AUTO_INCREMENT to my column ID, it still should be unique, and then
 increment itself... No?


 Not all unique identifiers are computer-generated.  If you leave off
 AUTO_INCREMENT but still make it a primary key, uniqueness will be
 enforced on whatever the application(s) enter(s) as the value, but
 nothing will be plugged in.

 Bruce Feist



Ok, I'm not sure Iunderstood the real (technical) reason, but I
understood that I had to use the AUTO_INCREMENT to be sure of a nice
functionning of my database.
Thank you very much all.

Let me give it a try. The primary key is *required* to be NOT NULL because the
value in the key *must* identify one and only one record in the table. If you
don't want to manually insert UNIQUE values in the ID column to accomplish this,
for example, in the mysql client, or by some other method such as an application
that writes data to the table, then using an AUTO_INCREMENT column attribute
will *enforce* the necessary uniqueness that a primary key requires. It is this
way be design and definition.




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: primary key/foreign key constraints with InnoDB

[Jeff Mathis]

Thanks,
but I think the lik you provided won't help. I know how to create pk/fk
contraints, and do in our schema, when the foreign key is completely
specified. for example, if my original table was instead:

create table Example (
   id int not null auto_increment primary key,
   fk_id int not null
) type = InnoDB;

then I create an index in fk_id, and issue the alter table statement:
alter table Example add constraint foreign key (fk_id) references
Fk(id);

for an InnoDB table called Fk.

What I need to do is somehow put an if statement in there. If
table_name = 'TabA', then verify that TabA.id exists. If table_name =
'TabB', then verify that TabB.id exists. TabA and TabB, for the present
purposes, could simply be

create table TabA {
id int not null auto_increment primary key
) type = InnoDB;

create table TabB {
id int not null auto_increment primary key
) type = InnoDB;


Its as though I could do the following:

create table Example (
id int not null auto_increment primary key,
table_name enum('TabA','TabB') not null,
table_id int not null
) type = InnoDB;
alter table Example add index (table_id);
alter table Example add constraint foreign key (table_id) references 
(if table_name = 'TabA' then TabA(id) else TabB(id);

but I don't think this works.

jeff


Stefan Hinz wrote:
 
 Jeff,
 
  I'm wondering if its somehow possible to create a pk/fk constraint for
  the table below
 
  create table Example (
  id int not null auto_increment primary key,
  table_name enum('TabA','TabB') not null,
  table_id int not null
  ) type = InnoDB;
 
  if table_name is 'TabA', then I want to make sure the row exists in
  TabA. Likewise if table_name is 'TabB'
 
 You can find the syntax for MySQL / InnoDB and a good example here:
 
 http://www.mysql.com/doc/en/SEC463.html
 
 To avoid trouble, consider this sentence from that page:
 
 Both tables have to be InnoDB type and there must be an index where
 the foreign key and the referenced key are listed as the FIRST
 columns. InnoDB does not auto-create indexes on foreign keys or
 referenced keys: you have to create them explicitly.
 
 The example on that page, however, shows exactly how you'd do that.
 
 Regards,
 --
   Stefan Hinz [EMAIL PROTECTED]
   iConnect GmbH http://iConnect.de
   Heesestr. 6, 12169 Berlin (Germany)
   Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3
 
 [filter fodder: sql, mysql, query]
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

-- 
Jeff Mathis, Ph.D.  505-955-1434
The Prediction Company  [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: primary key/foreign key constraints with InnoDB

[Stephen Giese]

Jeff,

We faced a similar challenge in an application: Each child record must have 
a parent in one of two tables, TabA or TabB, but not both.  We solved it 
by adding a foreign-key field for each possible parent in the child 
table.  Each column can have the FK constraint.  We were using Sybase, but 
I translate the DDL to MySQL below.

create table Example (
id int not null auto_increment primary key,
tableA_id  int not null,
tableB_id  int not null
) type = InnoDB;
alter table Example add index (tableA_id);
alter table Example add index (tableB_id);
alter table Example add constraint foreign key (tableA_id) references TabA(id);
alter table Example add constraint foreign key (tableB_id) references TabB(id);
However, you will notice that each child record now must have a parent 
record in BOTH parent tables.  We used our front end to enforce a rule that 
the one of the two foreign key fields is always -1 (or some other default 
value).  Then we insert a record into each parent with a key value that 
matches our default (-1).

This method is not as easily extensible as your model, but perhaps that's 
OK.  In SQL to join the parent and child you must decide which parent to 
join based on which FK column has the non-default value.

You might be able to come up with a DB rule to ensure that exactly one of 
the FK values is non-default.

Stephe

At 09:26 AM 4/2/2003 -0700, Jeff Mathis wrote:
Thanks,
but I think the lik you provided won't help. I know how to create pk/fk
contraints, and do in our schema, when the foreign key is completely
specified. for example, if my original table was instead:
create table Example (
   id int not null auto_increment primary key,
   fk_id int not null
) type = InnoDB;
then I create an index in fk_id, and issue the alter table statement:
alter table Example add constraint foreign key (fk_id) references
Fk(id);
for an InnoDB table called Fk.

What I need to do is somehow put an if statement in there. If
table_name = 'TabA', then verify that TabA.id exists. If table_name =
'TabB', then verify that TabB.id exists. TabA and TabB, for the present
purposes, could simply be
create table TabA {
id int not null auto_increment primary key
) type = InnoDB;
create table TabB {
id int not null auto_increment primary key
) type = InnoDB;
Its as though I could do the following:

create table Example (
id int not null auto_increment primary key,
table_name enum('TabA','TabB') not null,
table_id int not null
) type = InnoDB;
alter table Example add index (table_id);
alter table Example add constraint foreign key (table_id) references
(if table_name = 'TabA' then TabA(id) else TabB(id);
but I don't think this works.

jeff

Stefan Hinz wrote:

 Jeff,

  I'm wondering if its somehow possible to create a pk/fk constraint for
  the table below

  create table Example (
  id int not null auto_increment primary key,
  table_name enum('TabA','TabB') not null,
  table_id int not null
  ) type = InnoDB;

  if table_name is 'TabA', then I want to make sure the row exists in
  TabA. Likewise if table_name is 'TabB'

 You can find the syntax for MySQL / InnoDB and a good example here:

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

 To avoid trouble, consider this sentence from that page:

 Both tables have to be InnoDB type and there must be an index where
 the foreign key and the referenced key are listed as the FIRST
 columns. InnoDB does not auto-create indexes on foreign keys or
 referenced keys: you have to create them explicitly.

 The example on that page, however, shows exactly how you'd do that.

 Regards,
 --
   Stefan Hinz [EMAIL PROTECTED]
   iConnect GmbH http://iConnect.de
   Heesestr. 6, 12169 Berlin (Germany)
   Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3

 [filter fodder: sql, mysql, query]

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Jeff Mathis, Ph.D.  505-955-1434
The Prediction Company  [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: primary key/foreign key constraints with InnoDB

[Jeff Mathis]

thanks for the advice Stephen. I'll admit though I am somewhat loathe to
adding an artifical row in the other tables, but it may not be a bad way
to go. In the past, I've written triggers to do this kind of check, but
mysql doesn't yet support triggers.

what I ended up doing is carefully rethinking the schema. It turns out
we came up with a better design that does not require the table_name,
table_id linking mechanism. We just link into one table, which of course
presents no problems in creating a foreign key constraint. 

jeff

Stephen Giese wrote:
 
 Jeff,
 
 We faced a similar challenge in an application: Each child record must have
 a parent in one of two tables, TabA or TabB, but not both.  We solved it
 by adding a foreign-key field for each possible parent in the child
 table.  Each column can have the FK constraint.  We were using Sybase, but
 I translate the DDL to MySQL below.
 
 create table Example (
  id int not null auto_increment primary key,
  tableA_id  int not null,
  tableB_id  int not null
 ) type = InnoDB;
 alter table Example add index (tableA_id);
 alter table Example add index (tableB_id);
 alter table Example add constraint foreign key (tableA_id) references TabA(id);
 alter table Example add constraint foreign key (tableB_id) references TabB(id);
 
 However, you will notice that each child record now must have a parent
 record in BOTH parent tables.  We used our front end to enforce a rule that
 the one of the two foreign key fields is always -1 (or some other default
 value).  Then we insert a record into each parent with a key value that
 matches our default (-1).
 
 This method is not as easily extensible as your model, but perhaps that's
 OK.  In SQL to join the parent and child you must decide which parent to
 join based on which FK column has the non-default value.
 
 You might be able to come up with a DB rule to ensure that exactly one of
 the FK values is non-default.
 
 Stephe
 
 At 09:26 AM 4/2/2003 -0700, Jeff Mathis wrote:
 Thanks,
 but I think the lik you provided won't help. I know how to create pk/fk
 contraints, and do in our schema, when the foreign key is completely
 specified. for example, if my original table was instead:
 
 create table Example (
 id int not null auto_increment primary key,
 fk_id int not null
 ) type = InnoDB;
 
 then I create an index in fk_id, and issue the alter table statement:
 alter table Example add constraint foreign key (fk_id) references
 Fk(id);
 
 for an InnoDB table called Fk.
 
 What I need to do is somehow put an if statement in there. If
 table_name = 'TabA', then verify that TabA.id exists. If table_name =
 'TabB', then verify that TabB.id exists. TabA and TabB, for the present
 purposes, could simply be
 
 create table TabA {
  id int not null auto_increment primary key
 ) type = InnoDB;
 
 create table TabB {
  id int not null auto_increment primary key
 ) type = InnoDB;
 
 
 Its as though I could do the following:
 
 create table Example (
  id int not null auto_increment primary key,
  table_name enum('TabA','TabB') not null,
  table_id int not null
 ) type = InnoDB;
 alter table Example add index (table_id);
 alter table Example add constraint foreign key (table_id) references
  (if table_name = 'TabA' then TabA(id) else TabB(id);
 
 but I don't think this works.
 
 jeff
 

-- 
Jeff Mathis, Ph.D.  505-955-1434
The Prediction Company  [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: primary key/foreign key constraints with InnoDB

[Stefan Hinz]

Jeff,

 I'm wondering if its somehow possible to create a pk/fk constraint for
 the table below

 create table Example (
 id int not null auto_increment primary key,
 table_name enum('TabA','TabB') not null,
 table_id int not null
 ) type = InnoDB;

 if table_name is 'TabA', then I want to make sure the row exists in
 TabA. Likewise if table_name is 'TabB'

You can find the syntax for MySQL / InnoDB and a good example here:

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

To avoid trouble, consider this sentence from that page:

Both tables have to be InnoDB type and there must be an index where
the foreign key and the referenced key are listed as the FIRST
columns. InnoDB does not auto-create indexes on foreign keys or
referenced keys: you have to create them explicitly.

The example on that page, however, shows exactly how you'd do that.

Regards,
--
  Stefan Hinz [EMAIL PROTECTED]
  iConnect GmbH http://iConnect.de
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3

[filter fodder: sql, mysql, query]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: PRIMARY KEY

[Stefan Hinz, iConnect \(Berlin\)]

Rob,

 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,

 How does any application ask for the PRIMARY KEY?  Is this an
 ODBC call, or are their command line and built-in functions?  I
 couldn't find this in the documentation.

Primarily, this is a feature for ODBC applications like MS Access. I
don't know of any other app that really longs to have a primary key for
each table, but if there were such apps, MySQL would act as described in
the manual (with or without ODBC).

Regards,
--
  Stefan Hinz [EMAIL PROTECTED]
  Geschäftsführer / CEO iConnect GmbH http://iConnect.de
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: Rob Pecherer [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, January 18, 2003 6:38 PM
Subject: PRIMARY KEY


 According to the MySQL documentation (Section 6.5.3, CREATE TABLE
syntax):

 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.

 How does any application ask for the PRIMARY KEY?  Is this an
 ODBC call, or are their command line and built-in functions?  I
 couldn't find this in the documentation.

 Rob


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: PRIMARY KEY

[Stefan Hinz, iConnect \(Berlin\)]

Rob,

 I'm not questioning what MySQL does, only how.  MySQL does not act
 without a request, so my question is (now), How do you ask MySQL
 what the PRIMARY KEY of a table is?

SHOW KEYS FROM tbl

In an application, next thing you would do is check what the value of
'Key_name' is (which is the column name of the returned result set), and
if it is 'PRIMARY', then you have the PK.

I don't know how (ODBC) apps like MS Access would ask MySQL for a PK
column of a table, but MySQL must have a built-in mechanism to answer
such a question with something else but the value 'PRIMARY'.

Maybe some guru on the list has a more precise information?!

 P.S. I lived/worked in Berlin 1984-85 developing an RDBMS for
 Siemens on a joint project w. Intel.  It never reached the
 market.  Great City!!  I miss alt bier.

:-)

Regards,
--
  Stefan Hinz [EMAIL PROTECTED]
  Geschäftsführer / CEO iConnect GmbH http://iConnect.de
  Heesestr. 6, 12169 Berlin (Germany)
  Tel: +49 30 7970948-0  Fax: +49 30 7970948-3

- Original Message -
From: Rob Pecherer [EMAIL PROTECTED]
To: Stefan Hinz, iConnect (Berlin) [EMAIL PROTECTED]
Sent: Saturday, January 18, 2003 7:57 PM
Subject: RE: PRIMARY KEY


 Greetings Stefan, and thanks.

 I'm not questioning what MySQL does, only how.  MySQL does not act
 without a request, so my question is (now), How do you ask MySQL
 what the PRIMARY KEY of a table is?

 Thanks, Rob

 P.S. I lived/worked in Berlin 1984-85 developing an RDBMS for
 Siemens on a joint project w. Intel.  It never reached the
 market.  Great City!!  I miss alt bier.

 -Original Message-
 From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]]
 Sent: Saturday, January 18, 2003 11:35 AM
 To: Rob Pecherer; [EMAIL PROTECTED]
 Subject: Re: 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,

  How does any application ask for the PRIMARY KEY?  Is this an
  ODBC call, or are their command line and built-in functions?  I
  couldn't find this in the documentation.

 Primarily, this is a feature for ODBC applications like MS Access. I
 don't know of any other app that really longs to have a primary key
for
 each table, but if there were such apps, MySQL would act as described
in
 the manual (with or without ODBC).

 Regards,
 --
   Stefan Hinz




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Primary key question

[Steve Yates]

On Tue, 17 Dec 2002 19:15:08 +0100, Serrand Patrice wrote:
Does MySQL automatically create index on primary key ?

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

 - Steve Yates
 - Antonym:  The opposite of the word you're searching for.

~ Taglines by Taglinator - www.srtware.com ~


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

re: Primary Key Question

[Victoria Reznichenko]

On Monday 16 December 2002 18:12, tmb wrote:
 I understood that MySQL didn't internally keep up with
 the relationships between tables... like MS Access...

 And that it was up to the programmer to referential
 integrity...

 But I noticed in phpMyAdmin that the offer the option
 of defining a column in a table as 'Primary'

 Am I confused on this or is this something that
 phpMyAdmin takes does ??

Yes, you can define column as a primary key. It uniquely 
references to a particular record  in the table. So values
must be unique and non-null. What exactly  confused you?



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: primary key

[Alex Behrens]

Sorry I meant without deleting the table or affecting any present data. is
this possible?

Thanks!

-Alex Big Al Behrens
E-mail: [EMAIL PROTECTED]
Urgent E-mail: [EMAIL PROTECTED] (Please be brief!)
Phone: 651-482-8779
Cell: 651-329-4187
Fax: 651-482-1391
ICQ: 3969599
Owner of the 3D-Unlimited Network:
http://www.3d-unlimited.com
Send News:
[EMAIL PROTECTED]
- Original Message -
From: Paul DuBois [EMAIL PROTECTED]
To: Alex Behrens [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Sunday, March 31, 2002 1:33 AM
Subject: Re: primary key


 At 0:40 -0600 3/31/02, Alex Behrens wrote:
 Hey Guys,
 
 Is it possible to remove a primary key from a mysql table that has
already
 been created without whipping out the table?

 I suppose that depends on what without whipping out the table means.
 I confess I have no idea.

 ALTER TABLE might help you here.

 It's described in the manual.

 
 Thanks!
 
 -Alex Big Al Behrens
 E-mail: [EMAIL PROTECTED]
 Urgent E-mail: [EMAIL PROTECTED] (Please be brief!)
 Phone: 651-482-8779
 Cell: 651-329-4187
 Fax: 651-482-1391
 ICQ: 3969599
 Owner of the 3D-Unlimited Network:
 http://www.3d-unlimited.com
 Send News:
 [EMAIL PROTECTED]



 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: primary key

[Paul DuBois]

At 0:40 -0600 3/31/02, Alex Behrens wrote:
Hey Guys,

Is it possible to remove a primary key from a mysql table that has already
been created without whipping out the table?

I suppose that depends on what without whipping out the table means.
I confess I have no idea.

ALTER TABLE might help you here.

It's described in the manual.


Thanks!

-Alex Big Al Behrens
E-mail: [EMAIL PROTECTED]
Urgent E-mail: [EMAIL PROTECTED] (Please be brief!)
Phone: 651-482-8779
Cell: 651-329-4187
Fax: 651-482-1391
ICQ: 3969599
Owner of the 3D-Unlimited Network:
http://www.3d-unlimited.com
Send News:
[EMAIL PROTECTED]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: primary key that doesn't autoincrement

[Rick Emery]

yes, you can

-Original Message-
From: D Woods [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, January 09, 2002 4:02 PM
To: [EMAIL PROTECTED]
Subject: primary key that doesn't autoincrement


I'm a novice at setting up a mysql database and don't know the answer to
this. I usually set up my primary keys as autoincrementing ID fields. Now I
need to have a primary key that isn't an autoincrementing field as I want to
store the CFTOKEN and use it as the primary key. Can I not have a
non-autoincrementing primary key in mysql?

Thanks


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: primary key that doesn't autoincrement

[Dan Nelson]

In the last episode (Jan 09), D Woods said:
 I'm a novice at setting up a mysql database and don't know the answer to
 this. I usually set up my primary keys as autoincrementing ID fields. Now I
 need to have a primary key that isn't an autoincrementing field as I want to
 store the CFTOKEN and use it as the primary key. Can I not have a
 non-autoincrementing primary key in mysql?

Sure.  Just leave off the AUTO_INCREMENT when you create the field.

-- 
Dan Nelson
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: primary key based on unique value for two columns

[Rick Emery]

Yes, you can have multi-column keys.  see the manual, para. 6.5.3 CREATE
TABLE Syntax

-Original Message-
From: Brendin [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 12:37 PM
To: [EMAIL PROTECTED]
Subject: primary key based on unique value for two columns


I would like to have a table that has a primary key defined on a
combination of two columns in the table.  In other words a unique key
based upon the values in two columns.

I don't think I am able to do this in mysql.  I think you can only have
a primary key on one column and not on a combination of columns.  If I
am wrong please correct me.

I am looking for work arounds.  I have thought of one.  That would be to
concatenate the columns and use a field terminator such as a - to
separate the values or (columns) in the single column.  Then you could
use string functions to parse the columns.  Ex:

For column 1 in concatenated column
left( ColumnName, (instr( ColumnName, '-') - 1) )

For column 2 in concatenated column
Substr (ColumName, (instr ( ColumnName, '-') + 1) , length(ColumnName)-
(instr ( ColumnName, '-') + 1)  )

Is there any other way to do this?


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: primary key based on unique value for two columns

[Bill Adams]

Brendin wrote:

 I would like to have a table that has a primary key defined on a
 combination of two columns in the table.  In other words a unique key
 based upon the values in two columns.

 I don't think I am able to do this in mysql.  I think you can only have
 a primary key on one column and not on a combination of columns.  If I
 am wrong please correct me.

At least in 3.23.x (x?) and higher you CAN have a primary key on multiple
columns.


 I am looking for work arounds.  I have thought of one.  That would be to
 concatenate the columns and use a field terminator such as a - to
 separate the values or (columns) in the single column.  Then you could
 use string functions to parse the columns.  Ex:

[snip]
There is a maximum key lenth so if you have two char(255) columns you might
need to do something like:

ALTER TABLE table ADD PRIMARY KEY( char_col1(100), char_col2(100));

However, this also means that the combination of the first 100 chars from
each column must be unique.

b.




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: primary key based on unique value for two columns

[Daniel Las]

Hi

You are wrong.

Just try for example:

 create table test(
pk1 int not null,
pk2 int not null,
primary key(pk1,pk2)
);

Regards


Daniel £aœ
e-direct Polska sp. z o.o.
WWW: http://www.e-direct.pl
E-mail: [EMAIL PROTECTED]
45-072 Opole ul. Reymonta 45
tel. +48 77 44 26 073
fax. +48 77 44 26 074

 -Original Message-
 From: Brendin [mailto:[EMAIL PROTECTED]]
 Sent: Monday, November 12, 2001 7:37 PM
 To: [EMAIL PROTECTED]
 Subject: primary key based on unique value for two columns


 I would like to have a table that has a primary key defined on a
 combination of two columns in the table.  In other words a unique key
 based upon the values in two columns.

 I don't think I am able to do this in mysql.  I think you can only have
 a primary key on one column and not on a combination of columns.  If I
 am wrong please correct me.

 I am looking for work arounds.  I have thought of one.  That would be to
 concatenate the columns and use a field terminator such as a - to
 separate the values or (columns) in the single column.  Then you could
 use string functions to parse the columns.  Ex:

 For column 1 in concatenated column
 left( ColumnName, (instr( ColumnName, '-') - 1) )

 For column 2 in concatenated column
 Substr (ColumName, (instr ( ColumnName, '-') + 1) , length(ColumnName)-
 (instr ( ColumnName, '-') + 1)  )

 Is there any other way to do this?


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: primary key based on unique value for two columns

[Carsten H. Pedersen]

 I would like to have a table that has a primary key defined on a
 combination of two columns in the table.  In other words a unique key
 based upon the values in two columns.
 
 I don't think I am able to do this in mysql.  I think you can only have
 a primary key on one column and not on a combination of columns.  If I
 am wrong please correct me.

You're wrong :-)

mysql CREATE TABLE tablename (col_a int not null, 
- b int not null, PRIMARY KEY (a, b));

 I am looking for work arounds.

No need to...

/ Carsten
--
Carsten H. Pedersen
keeper and maintainer of the bitbybit.dk MySQL FAQ
http://www.bitbybit.dk/mysqlfaq




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: primary key based on unique value for two columns

[Jonathan Hilgeman]

You can't use a primary key for that, but you CAN make a unique two-column
key:

ALTER TABLE MyTable ADD UNIQUE MyNewIndex (Column1,Column2)

- Jonathan

-Original Message-
From: Brendin [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 10:37 AM
To: [EMAIL PROTECTED]
Subject: primary key based on unique value for two columns


I would like to have a table that has a primary key defined on a
combination of two columns in the table.  In other words a unique key
based upon the values in two columns.

I don't think I am able to do this in mysql.  I think you can only have
a primary key on one column and not on a combination of columns.  If I
am wrong please correct me.

I am looking for work arounds.  I have thought of one.  That would be to
concatenate the columns and use a field terminator such as a - to
separate the values or (columns) in the single column.  Then you could
use string functions to parse the columns.  Ex:

For column 1 in concatenated column
left( ColumnName, (instr( ColumnName, '-') - 1) )

For column 2 in concatenated column
Substr (ColumName, (instr ( ColumnName, '-') + 1) , length(ColumnName)-
(instr ( ColumnName, '-') + 1)  )

Is there any other way to do this?


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: primary key based on unique value for two columns

[Brendin]

Will this relate the primary keys to one another or just create primary
keys on the columns.

Ie:

Create table test (column1 int(11) not null, column2 int(11) not null,
primary key (column1, column2) )

Then could you...

Insert into test values (1,2)
Insert into test values (1,3)

Note that the combination of the two columns represents a distinct value
but column 1 is the same value in both inserts.  

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Bill Adams
Sent: Monday, November 12, 2001 12:05 PM
To: Brendin
Cc: [EMAIL PROTECTED]
Subject: Re: primary key based on unique value for two columns

Brendin wrote:

 I would like to have a table that has a primary key defined on a 
 combination of two columns in the table.  In other words a unique key 
 based upon the values in two columns.

 I don't think I am able to do this in mysql.  I think you can only 
 have a primary key on one column and not on a combination of columns.

 If I am wrong please correct me.

At least in 3.23.x (x?) and higher you CAN have a primary key on
multiple columns.


 I am looking for work arounds.  I have thought of one.  That would be 
 to concatenate the columns and use a field terminator such as a - to 
 separate the values or (columns) in the single column.  Then you could

 use string functions to parse the columns.  Ex:

[snip]
There is a maximum key lenth so if you have two char(255) columns you
might need to do something like:

ALTER TABLE table ADD PRIMARY KEY( char_col1(100), char_col2(100));

However, this also means that the combination of the first 100 chars
from each column must be unique.

b.




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: primary key based on unique value for two columns

[Brendin]

This will work thanks...  That's what I want a unique key based on
two columns.  

-Original Message-
From: Jonathan Hilgeman [mailto:[EMAIL PROTECTED]] 
Sent: Monday, November 12, 2001 12:09 PM
To: 'Brendin'; [EMAIL PROTECTED]
Subject: RE: primary key based on unique value for two columns

You can't use a primary key for that, but you CAN make a unique
two-column
key:

ALTER TABLE MyTable ADD UNIQUE MyNewIndex (Column1,Column2)

- Jonathan

-Original Message-
From: Brendin [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 10:37 AM
To: [EMAIL PROTECTED]
Subject: primary key based on unique value for two columns


I would like to have a table that has a primary key defined on a
combination of two columns in the table.  In other words a unique key
based upon the values in two columns.

I don't think I am able to do this in mysql.  I think you can only have
a primary key on one column and not on a combination of columns.  If I
am wrong please correct me.

I am looking for work arounds.  I have thought of one.  That would be to
concatenate the columns and use a field terminator such as a - to
separate the values or (columns) in the single column.  Then you could
use string functions to parse the columns.  Ex:

For column 1 in concatenated column
left( ColumnName, (instr( ColumnName, '-') - 1) )

For column 2 in concatenated column
Substr (ColumName, (instr ( ColumnName, '-') + 1) , length(ColumnName)-
(instr ( ColumnName, '-') + 1)  )

Is there any other way to do this?


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: primary key based on unique value for two columns

[primej]

sure you can

CREATE TABLE xx (field1 INT NOT NULL,field2 INT NOT NULL,PRIMARY KEY
(field1,field2))

and you have table xx with unique key in two fields

hand
primoz

- Original Message -
From: Brendin [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, November 12, 2001 7:37 PM
Subject: primary key based on unique value for two columns


 I would like to have a table that has a primary key defined on a
 combination of two columns in the table.  In other words a unique key
 based upon the values in two columns.

 I don't think I am able to do this in mysql.  I think you can only have
 a primary key on one column and not on a combination of columns.  If I
 am wrong please correct me.

 I am looking for work arounds.  I have thought of one.  That would be to
 concatenate the columns and use a field terminator such as a - to
 separate the values or (columns) in the single column.  Then you could
 use string functions to parse the columns.  Ex:

 For column 1 in concatenated column
 left( ColumnName, (instr( ColumnName, '-') - 1) )

 For column 2 in concatenated column
 Substr (ColumName, (instr ( ColumnName, '-') + 1) , length(ColumnName)-
 (instr ( ColumnName, '-') + 1)  )

 Is there any other way to do this?


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: primary key based on unique value for two columns

[Jonathan Hilgeman]

I have not tested this with the primary key - my previous suggestion went
off the logic in my head at the time (most likely not a good thing, since
I'm tired right now), but I have a feeling Bill here is probably correct if
he says it's possible with the primary key as well. I stand corrected.

- Jonathan

-Original Message-
From: Bill Adams [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 12, 2001 11:05 AM
To: Brendin
Cc: [EMAIL PROTECTED]
Subject: Re: primary key based on unique value for two columns


Brendin wrote:

 I would like to have a table that has a primary key defined on a
 combination of two columns in the table.  In other words a unique key
 based upon the values in two columns.

 I don't think I am able to do this in mysql.  I think you can only have
 a primary key on one column and not on a combination of columns.  If I
 am wrong please correct me.

At least in 3.23.x (x?) and higher you CAN have a primary key on multiple
columns.


 I am looking for work arounds.  I have thought of one.  That would be to
 concatenate the columns and use a field terminator such as a - to
 separate the values or (columns) in the single column.  Then you could
 use string functions to parse the columns.  Ex:

[snip]
There is a maximum key lenth so if you have two char(255) columns you might
need to do something like:

ALTER TABLE table ADD PRIMARY KEY( char_col1(100), char_col2(100));

However, this also means that the combination of the first 100 chars from
each column must be unique.

b.




-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Primary Key pairs and auto-incrementing

[Carl Troein]

On 29-Aug-01, Paul DuBois wrote:

 Create a single PRIMARY KEY that consists of the two columns chapter and
 section, and make section an AUTO_INCREMENT column.  When you insert
 rows, set chapter to the proper chapter number and section to NULL.
 This will cause MySQL to generate independent sequences for each chapter.
 (In essence, you have multiple sequences within the same table.)
 
 This requires that you have MySQL 3.22.25 or later.  That's when this
 feature was introduced.

That's odd. There's nothing about it in the changelog for 3.22.25 in the
documentation, and the only reference to this behavior I've been able to
find in the manual is this (from the chapter on MyIsam): Note that when
an AUTO_INCREMENT is defined on the end of a multi-part-key the old
behavior is still present.

I hate having to ask for this, but could you please point me to the
chapter in the manual where this is discussed? I just can't seem to
find it. :-P

//C

-- 
 Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280
 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/
 Amiga user since '89, and damned proud of it too.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Primary Key pairs and auto-incrementing

[Chris Bolt]

 I want to create a table with two Primary Keys.  The first key is
 a category
 field, and the second is auto-incremented.  Example: Key
 1=Chapter name, Key
 2 =section number.  For example, (Chapter1, 1) (Chapter1, 2) (Chapter1, 3)
 then with a new Cheaper, I want to restart the auto-incremented field back
 to 1 (Chapter2, 1) (Chapter2, 2).  How do I get the auto-incrementer to
 restart with each new chapter?

Try this:

mysql CREATE TABLE test (chapter INT NOT NULL, section INT NOT NULL
AUTO_INCREMENT, PRIMARY KEY (chapter, section));
Query OK, 0 rows affected (0.00 sec)

mysql INSERT INTO test (chapter) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO test (chapter) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO test (chapter) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql SELECT * FROM test;
+-+-+
| chapter | section |
+-+-+
|   1 |   1 |
|   2 |   1 |
|   2 |   2 |
+-+-+
3 rows in set (0.00 sec)

You can't have two primary keys, but you can have a single primary key on
two columns.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Primary Key pairs and auto-incrementing

[Paul DuBois]

At 10:37 AM -0400 8/29/01, Ferrara, Joseph C wrote:
I want to create a table with two Primary Keys.  The first key is a category
field, and the second is auto-incremented.  Example: Key 1=Chapter name, Key
2 =section number.  For example, (Chapter1, 1) (Chapter1, 2) (Chapter1, 3)
then with a new Cheaper, I want to restart the auto-incremented field back
to 1 (Chapter2, 1) (Chapter2, 2).  How do I get the auto-incrementer to
restart with each new chapter?

Create a single PRIMARY KEY that consists of the two columns chapter and
section, and make section an AUTO_INCREMENT column.  When you insert
rows, set chapter to the proper chapter number and section to NULL.
This will cause MySQL to generate independent sequences for each chapter.
(In essence, you have multiple sequences within the same table.)

This requires that you have MySQL 3.22.25 or later.  That's when this
feature was introduced.

-- 
Paul DuBois, [EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Primary key not unique on Innodb tables.

[Heikki Tuuri]

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 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',
  volume int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (symbole,id),
  KEY idx_id (id)
) TYPE=Innobase;

  As you can see, (symbole,id) is the primary key.

  But :

mysql select * from livequote_last_PAR where symbole='1rPFTE' order by id
desc;
+-+---+-+--++
| symbole | id| date| last | volume |
+-+---+-+--++
| 1rPFTE  | 33121 | 2001-04-25 11:59:39 | 78.7 |492 |
| 1rPFTE  | 33121 | 2001-04-25 11:59:39 | 78.7 |492 |
| 1rPFTE  | 33120 | 2001-04-25 11:59:39 | 78.7 |   1290 |
| 1rPFTE  | 33120 | 2001-04-25 11:59:39 | 78.7 |   1290 |
| 1rPFTE  | 33119 | 2001-04-25 11:59:29 | 78.7 | 10 |
| 1rPFTE  | 33118 | 2001-04-25 11:59:04 | 78.7 |106 |
| 1rPFTE  | 33117 | 2001-04-25 11:58:49 | 78.7 | 50 |
| 1rPFTE  | 33116 | 2001-04-25 11:57:57 | 78.7 | 76 |
| 1rPFTE  | 33115 | 2001-04-25 11:57:33 | 78.6 | 99 |
| 1rPFTE  | 33114 | 2001-04-25 11:56:44 | 78.7 |   1768 |
| 1rPFTE  | 33113 | 2001-04-25 11:56:44 | 78.7 |118 |
| 1rPFTE  | 33112 | 2001-04-25 11:56:04 | 78.7 |   1000 |
+-+---+-+--++
12 rows in set (0.00 sec)

  I've  not  yet  deeply  explored what the problem is, but there is a
  problem.

  I  will  have to ckech exactly what queries my programs are doing in
  order to have this result, but there is a problem in mysql or innodb
  anyway I think.

  Regards,
  Alex.





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Primary key ID gets messed up after deleting/adding

[Jens Vonderheide]

 When I first added about 10 records and then deleted a few records and
 then added more records it didn't replace the missing records.

That's MySQL's usual behaviour. auto_increment only garantees to create
unique keys. "Holes" left by deleting data are not filled.

Jens


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Primary key ID gets messed up after deleting/adding

[Lindsay Adams]

I can understand not wanting to have holes, but..
If it is a primary key, how are you going to handle updating tables that
rely on that key id?


If your id numbers were to shift, like you want, and you had a related sub
table (one to many) and this was say, an shopping cart, then all of a sudden
customer A's line items are showing up in Customer B's and not customer A's

It is more common to to have reliable unique no shifting keys.

You can program in some logic that deletes all related records in related
tables, and then re-uses numbers if you want to 'fill' the holes, butis it
really worth it?

Holes are merely an aesthetic item in the vast majority of cases.

Auto_increment behavior is clearly documented in the manual.




On 4/8/01 9:35 AM, "David" [EMAIL PROTECTED] wrote:

 I have a primary key listed as ID
 
 When I first added about 10 records and then deleted a few records and
 then added more records it didn't replace the missing records.
 
 When I select all records to view I now get:
 
 1
 2  why didn't it go to 3, 4 and 5 after 2?
 6
 7
 8
 
 
 
 When I add a record it becomes record 11.  Am I doing something wrong
 when I delete a record?  I thought it would move the other records down
 6 and 7 would be 3 and 4 etc...
 
 Using RedHat Linux with latest MySQL rpms: mysql-server-3.23.32-1.7
 php-mysql-4.0.4pl1-3
 mysql-devel-3.23.32-1.7
 mysqlclient9-3.23.22-3
 mysql-3.23.32-1.7
 
 
 -
 Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Primary key ID gets messed up after deleting/adding

[Kobus Myburgh]

How would you actually overcome that? Wouldn't it be good if MySQL would be adapted to 
actually do this for you?

-- Kobus


 "Jens Vonderheide" [EMAIL PROTECTED] 2001-04-08 19:25:48 
 When I first added about 10 records and then deleted a few records and
 then added more records it didn't replace the missing records.

That's MySQL's usual behaviour. auto_increment only garantees to create
unique keys. "Holes" left by deleting data are not filled.

Jens


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Primary key ID gets messed up after deleting/adding

[Kobus Myburgh]

No, what is meant here is that:

He has record 1 2 3 and 4. Now deletes 2 and 3. Now create 2 new records. MySQL 
creates them as 5 and 6, instead of 2 and 3 again...

--Kobus

 Lindsay Adams [EMAIL PROTECTED] 2001-04-08 19:42:12 
I can understand not wanting to have holes, but..
If it is a primary key, how are you going to handle updating tables that
rely on that key id?


If your id numbers were to shift, like you want, and you had a related sub
table (one to many) and this was say, an shopping cart, then all of a sudden
customer A's line items are showing up in Customer B's and not customer A's

It is more common to to have reliable unique no shifting keys.

You can program in some logic that deletes all related records in related
tables, and then re-uses numbers if you want to 'fill' the holes, butis it
really worth it?

Holes are merely an aesthetic item in the vast majority of cases.

Auto_increment behavior is clearly documented in the manual.




On 4/8/01 9:35 AM, "David" [EMAIL PROTECTED] wrote:

 I have a primary key listed as ID
 
 When I first added about 10 records and then deleted a few records and
 then added more records it didn't replace the missing records.
 
 When I select all records to view I now get:
 
 1
 2  why didn't it go to 3, 4 and 5 after 2?
 6
 7
 8
 
 
 
 When I add a record it becomes record 11.  Am I doing something wrong
 when I delete a record?  I thought it would move the other records down
 6 and 7 would be 3 and 4 etc...
 
 Using RedHat Linux with latest MySQL rpms: mysql-server-3.23.32-1.7
 php-mysql-4.0.4pl1-3
 mysql-devel-3.23.32-1.7
 mysqlclient9-3.23.22-3
 mysql-3.23.32-1.7
 
 
 -
 Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php 
 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Primary key ID gets messed up after deleting/adding

[Jens Vonderheide]

 How would you actually overcome that? Wouldn't it be good if
 MySQL would be adapted to actually do this for you?

I think that not reusing deleted numbers is easier (i.e. more efficient).
IIRC, earlier versions of MySQL in fact reused the numbers.

There are 2 ways to overcome this:

1) Check if you really need to rely on numbers without any gaps. If you tell
us what you want to do, someone on the list may come up with a different
approach.
2) If you really need that behaviour, you shouldn't use auto_increment, but
write your own functions to get a unique key. I did this once (because I
needed to support some RDBMSs without auto_increment). It's not that
difficult.

Jens


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Primary key ID gets messed up after deleting/adding

[John Jensen]

How I learned to love number gaps:

I have a database of colleges and universities. Every degree listing 
as a numbered id. This used to be auto-incremented. After several 
deletes and additions, I found it advantageous to have gaps between 
schools to add new degree listings, so that I didn't get a numbering 
scenario like School A has degree #s 4, 5, 89, 326 and School B has 
#s 6, 7 8, 88, 91, 214, etc. If I have gaps, I have room to keep a 
school's degrees together in a sequence. It makes it so much easier 
to keep track. 

Gaps are only a subjective problem. Objectively, they make no 
difference to the database operation, and administratively, they can 
be quite useful. When we look past our expectations, we often find 
new posibilities.

On 8 Apr 2001, at 20:37, Jens Vonderheide wrote:

  How would you actually overcome that? Wouldn't it be good if
  MySQL would be adapted to actually do this for you?
 
 I think that not reusing deleted numbers is easier (i.e. more
 efficient). IIRC, earlier versions of MySQL in fact reused the
 numbers.
 
 There are 2 ways to overcome this:
 
 1) Check if you really need to rely on numbers without any gaps. If
 you tell us what you want to do, someone on the list may come up with
 a different approach. 2) If you really need that behaviour, you
 shouldn't use auto_increment, but write your own functions to get a
 unique key. I did this once (because I needed to support some RDBMSs
 without auto_increment). It's not that difficult.
 
 Jens
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED] To
 unsubscribe, e-mail
 [EMAIL PROTECTED] Trouble
 unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 


John Jensen
520 Goshawk Court
Bakersfield, CA 93309
661-833-2858

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: PRIMARY KEY (userid), UNIQUE (userid)

[The Tilghman]

No.  A primary key is a unique key, by definition.

-- 
"There cannot be a crisis today.  My schedule is already full."
 --Henry Kissinger

 -Original Message-
 From: Jacob Friis Larsen [mailto:[EMAIL PROTECTED]]
 Sent: Wednesday, February 07, 2001 09:58
 To: [EMAIL PROTECTED]
 Subject: PRIMARY KEY (userid), UNIQUE (userid)
 
 
 Is there an idea in having a PRIMARY KEY and UNIQUE key for 
 the same table
 column ?
 
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail 
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: PRIMARY KEY (userid), UNIQUE (userid)

[Cal Evans]

Yes, you can but you probably need a real good reason to. As Tilghman
pointed out a PK is a unique key.  If you have another unique key (a
candidate key) then you need revisit your datamodel and make sure you have a
good reason. Candidate keys to exist in the real world but they are rare.

Cal
http://www.calevans.com


-Original Message-
From: Jacob Friis Larsen [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 07, 2001 9:58 AM
To: [EMAIL PROTECTED]
Subject: PRIMARY KEY (userid), UNIQUE (userid)


Is there an idea in having a PRIMARY KEY and UNIQUE key for the same table
column ?


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php