Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
KnowledgeWoods Consulting
5,607 PointsI want to insert two columns as primary key and autoincrement in mysql database.
Hi, Thanks in advance.
I want to insert two columns in the table as primary key and auto increment. I have table named as "module_details." Present columns in this table is id, module_name But know i want one more column after id now the structure would be, id, sort_id, module_details. I want when user enters module_details, the column "sort_id" should also fill automatically as id is filling.
id sort_id module_details
1 1 module 1
2 2 module 2
3 3 module 3
5 Answers
Codin - Codesmite
8,600 PointsYou can only have one primary key per table.
But you can have a Composite Primary Key that is made up of multple columns.
For example:
CREATE TABLE module_details (
id INT NOT NULL AUTO_INCREMENT,
sort_id INT NOT NULL,
primary key (id, sort_id)
)
More info here that might help: http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx
KnowledgeWoods Consulting
5,607 PointsHow will I create table in mysql database without writing the create command. I am adding columns manually. And when I insert primary key in to two columns, it shows error. Please suggest
Codin - Codesmite
8,600 PointsI think the only way would be to drop the contraint and to recreate it. (I am not 100% sure if this is the only way though).
KnowledgeWoods Consulting
5,607 PointsI try to create a new table with the code you provided to me above. But its showing error in line 4.
KnowledgeWoods Consulting
5,607 PointsERROR : Incorrect table definition; there can be only one auto column and it must be defined as a key
Codin - Codesmite
8,600 PointsSorry yes I edited my post was only supposed to be auto_increment on ID.
I do not understand why you need sort_id and id to both have increment? They would end up having the same value?
You could make the second field a calculated field based on the first.
Ted Sumner
Courses Plus Student 17,967 PointsI agree that there seems to be no need for a sort id, especially as described. You have two columns that have identical values. It is a waste of database storage to do that unless you really want them to have separate values for some reason. Can you elaborate on what the real goal of the two columns really are?
Ted Sumner
Courses Plus Student 17,967 PointsTed Sumner
Courses Plus Student 17,967 PointsEdited question category.