Member Avatar

sir i am creating a database with name ams and 3 table with name article (fileds id(auto increment),title,image,contents and category) and 2 table login with user passwd as fileds 3rd table with category.

now i want in 1st table id,title,category as primary key but it gives error multiple primary key defined when i try to alter the table it gives error

1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

so plz help me out how to define multiple primary key in table.

You can't have multiple primary keys in a table, you can have a composite primary key in a table.

The use of multiple auto increment columns eludes me, they would all hold the same value as the others.

The intention of a Primary Key is to provide a unique identifier. You have that in the form of id. Why add more columns? You won't make it more unique that way. Furthermore, it is common practice that a Primary Key holds no meaning or information. Title and category hold information I assume. What if the title changes at some point? Your primary key would change. That is unwanted behaviour as the unique reference you once had is now invalid.

Member Avatar

I think it's saying you can't have an autoincrement field included if you want multiple fields as your PK. If you think about it, it makes little sense. Are you trying to make title-category unique in the table? So I'm assuming that title is a varchar and category is an integer of some type? If so, best change category fieldname to category_id, just for sanity's sake.

ALTER TABLE `article` ADD UNIQUE `unique_index` (`title`, `category_id`);

Should do it. Leave off the primary key.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.