0

I am learning mysql many to many relationship. While I was researching index, I found the below question.

How to properly index a linking table for many-to-many connection in MySQL?

Quassnoi answered a detail answer. Within his answer, I found the following syntax.

"ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_1, table_2)"

I changed "table_table" to my joining table called "postcategory" and changed "table1" to "post", "table2" to "category"

I got a syntax error when I execute it..

What am I doing wrong? I think I didn't understand Quassnoi's intention perfectly.

Community
  • 1
  • 1
Moon
  • 22,195
  • 68
  • 188
  • 269
  • 1
    Can you post your exact alter table statement please? – philwinkle Feb 24 '11 at 05:43
  • @philwinkle // So I have post, category, and postcategory tables. My alter statement is "ALTER table postcategory add constraint pk_post_category(post,category);" I think pk_post_category is not a valid syntax. – Moon Feb 24 '11 at 05:45

2 Answers2

1

Your response from above lists your ALTER TABLE statement as:

ALTER table postcategory add constraint pk_post_category(post,category);

You're defining a constraint here, not an index. If you're trying to add a primary key, it probably shouldn't be multicolumn (composite) and if so, you're missing the PRIMARY keyword. If you're trying to add a foreign key, you're missing the REFERENCES declaration.

So if it's a primary, as such, I would rewrite as:

ALTER TABLE `postcategory` ADD CONSTRAINT PRIMARY KEY `pk_post_category` (`post`,`category`);

If it's a foreign key:

ALTER TABLE `postcategory` ADD CONSTRAINT `fk_post_category` (`post`) REFERENCES `[tablename].[column]`;
philwinkle
  • 7,036
  • 3
  • 27
  • 46
0

You need to tell mysql what kind of constraint you are adding: primary key, unique, or foreign key. What's the full statement that is getting a syntax error?

Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
  • // Like I wrote, I am trying to add an index to my joining table. So..I read http://stackoverflow.com/questions/571309/how-to-properly-index-a-linking-table-for-many-to-many-connection-in-mysql article and found "ALTER TABLE table_table ADD CONSTRAINT pk_table1_table2 (table_1, table_2)", which does not work at my end – Moon Feb 24 '11 at 05:52
  • Take a look at the [syntax for the `ALTER TABLE` statement](http://dev.mysql.com/doc/refman/5.5/en/alter-table.html) in MySQL. – Ted Hopp Feb 24 '11 at 08:17