4

I have two tables for multilingual articles. In first table I have no text columns (id,authorid, date etc). In the second table I have text columns (titles,content etc).

For every row in first table I can have many rows in the second table. For example for article that is translated into three languages I have three rows in table 2.

In table 2 column(id) has foreign key to table 1, that's why it's already indexed. I can't set column id for table 2 unique as it's clear it's not unique. However, I can't edit data from table 2 in workbench as it says - no primary key or non-null unique key.

What I say in scheme:

Table1{
 id
 date
 authorId
}

Table2{
 id
 title
 content
 languageId
}

So the answer - if I create primary key for column (id) in table 2 - will mysql create one more index for this column?

  • You should probably have an `id` column as well as a `table1ID` column (or some such name), which would actually be your current `id` column. Then, make `id` the primary key, and make `table1ID` a foreign key. – Patrick Q Nov 25 '14 at 15:58
  • An index is associated with a primary key, so yes, adding a primary key will add an index (and in InnoDB affect the storage of the rows as well). – Gordon Linoff Nov 25 '14 at 16:01
  • why would you not have a primary key in the second table in the first place? just add a column for it. but yes it will add an index -- [**Another SO Question**](http://stackoverflow.com/questions/1071180/is-the-primary-key-automatically-indexed-in-mysql) – John Ruddell Nov 25 '14 at 16:01
  • 1
    I don't want to open a can of worms on Surrogate vs Natural keys here... The logical natural primary key on `Table2` is a compound key from `id` and whatever column stores your language. This will also create your clustering index so if `ID` is the first column in your primary key, all searches on this will perform pretty well. The alternative is to add an autoincrementing column to your second table, and make this the primary key, your nonclustered index will remain in place on `ID` but I think it will need to be rebuilt. – GarethD Nov 25 '14 at 16:06

1 Answers1

2

You can not create a primary key for id in Table2 as this id is not unique and by definition a primary key has to be UNIQUE and NOT NULL.

However, for you to be able to edit data from table 2 in workbench, I suggest you create a composite primary key on Table2 using both id and languageId fields.

ALTER TABLE table2 ADD PRIMARY KEY( id, languageId );

Here I am assuming that id, languageId columns together uniquely identify one record in Table2.

hashbrown
  • 3,438
  • 1
  • 19
  • 37