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 > >
