help creating composite primary keys

[jebin]

hey guys,
i need some advice on trying to create a composite primary key for a table using phpmyadmin.
i have already tried selecting multiple attributes as primary keys using the gui, only one gets selected.
i tried the Alter command to add another attribute as a primary key but i got an error.
/*       error
#1068 - Multiple primary key defined
*/

i even tried creating a new table with a composite primary key

//table
CREATE TABLE IF NOT EXISTS `StudentMarks1` (
  `m_s_id` varchar(20) NOT NULL,
  `m_attempt` int(1) NOT NULL,
  `m_sub_id` varchar(6) NOT NULL,
  `sub_th` int(3) DEFAULT NULL,
  `sub_th_rem` varchar(5) DEFAULT NULL,
  `sub_tw` int(3) DEFAULT NULL,
  `sub_tw_rem` varchar(5) DEFAULT NULL,
  `sub_pract` int(3) DEFAULT NULL,
  `sub_pract_rem` varchar(5) NOT NULL,
  `sub_oral` int(3) DEFAULT NULL,
  `sub_oral_rem` varchar(5) NOT NULL,
  `semester` int(1) NOT NULL,
  `m_revalflag` varchar(1) NOT NULL DEFAULT 'N' COMMENT 'enter n by default, enter y if student''s results are stored after reval'
Primary key('m_s_id', 'm_attempt','m_sub_id')
) ENGINE=MyISAM DEFAULT CHARSET=latin1

// error
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('m_s_id', 'm_attempt','m_sub_id') ) ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 15

[Gunashkhar Nandi]

Hi jebin,

Try this..

CREATE TABLE IF NOT EXISTS `StudentMarks1` (
  `m_s_id` varchar(20) NOT NULL,
  `m_attempt` int(1) NOT NULL,
  `m_sub_id` varchar(6) NOT NULL,
  `sub_th` int(3) DEFAULT NULL,
  `sub_th_rem` varchar(5) DEFAULT NULL,
  `sub_tw` int(3) DEFAULT NULL,
  `sub_tw_rem` varchar(5) DEFAULT NULL,
  `sub_pract` int(3) DEFAULT NULL,
  `sub_pract_rem` varchar(5) NOT NULL,
  `sub_oral` int(3) DEFAULT NULL,
  `sub_oral_rem` varchar(5) NOT NULL,
  `semester` int(1) NOT NULL,

  `m_revalflag` varchar(1) NOT NULL DEFAULT 'N' COMMENT 'enter n by default, enter y if student''s results are stored after reval',
Primary key(m_s_id,m_attempt,m_sub_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

If you notice there were two syntax errors 1. a missing comma at the end of third last line 2.columu names n second last line should be with out single quotes.

Hope this helps you

regards
Shekhar

--
Email to group : dbit-t...@googlegroups.com
To unsubscribe, send email to
dbit-techtal...@googlegroups.com
Visit this group at
http://groups.google.com/group/dbit-techtalk?hl=en