"you should define a primary key for this table" - why?

[CAH]

I once wrote on usenet and got a very good answer . This one

JN wrote:[color=blue][color=green]
> >| Should one place all the text in one big table, or is i better to place
> >| it in several tabels. Should one make 4 colums one for each language,
> >| or 4 rows one for each language?[/color]
>
> I use:
> CREATE TABLE `usr_languages` (
> `pageName` varchar(25) default '',
> `itemNo` int(11) default '0',
> `en` text,
> `fr` text,
> `it` text
> ) TYPE=MyISAM COMMENT='differ ent languages';
>[/color]

which I used, and it works just fine, except that I just discovered
that if I place just a litle more than a few line of text - in the TEXT
fields then i get an "error" in phpmyadmin. There is a small question
mark were thre is normally a pen and a cross - and when I move the
cursur above the question mark i get this. "you should define a primary
key for this table" .

Do you have any suggestions as to what the problem might be?

Thanks for any help you can offer.

Best regards
Cah

[Gerard]

Re: "you should define a primary key for this table" - why?

Have you tried...what the error says...as defining a primary
key...joint key with pageName and itemNo??

Rgds,
Gerard

[Bent Stigsen]

Re: "you should define a primary key for this table" - why?

CAH wrote:[color=blue]
> I once wrote on usenet and got a very good answer . This one
>
> JN wrote:
>[color=green][color=darkred]
>>>| Should one place all the text in one big table, or is i better to place
>>>| it in several tabels. Should one make 4 colums one for each language,
>>>| or 4 rows one for each language?[/color]
>>
>>I use:
>>CREATE TABLE `usr_languages` (
>> `pageName` varchar(25) default '',
>> `itemNo` int(11) default '0',
>> `en` text,
>> `fr` text,
>> `it` text
>>) TYPE=MyISAM COMMENT='differ ent languages';[/color]
>
>
> which I used, and it works just fine, except that I just discovered
> that if I place just a litle more than a few line of text - in the TEXT
> fields then i get an "error" in phpmyadmin. There is a small question
> mark were thre is normally a pen and a cross - and when I move the
> cursur above the question mark i get this. "you should define a primary
> key for this table" .
>
> Do you have any suggestions as to what the problem might be?[/color]

I don't use phpmyadmin, but I assume that it would like to use the
primary key to make sensible select/update-statements. Apart from
that, it is just an advice for your own good.

Primary keys are to ensure integrity, but you get performance gains as
an added bonus, because you get the table indexed. In other words,
without a primary key (when applicable), you risk making a mess of
your data and having a crappy performance. But the choice is still up
to you. :)


/Bent

[CAH]

Re: "you should define a primary key for this table" - why?

Gerard wrote:[color=blue]
> Have you tried...what the error says...as defining a primary
> key...joint key with pageName and itemNo??[/color]


Hi Gerard
Thanks a lot for the tip, - but how do I make a joint key ? I seams
strange to me that the problem only arises on a row, when there is a
lot of text in the TEXT field.

Cah

[CAH]

Re: "you should define a primary key for this table" - why?

> I don't use phpmyadmin, but I assume that it would like to use the[color=blue]
> primary key to make sensible select/update-statements. Apart from
> that, it is just an advice for your own good.
>
> Primary keys are to ensure integrity, but you get performance gains as
> an added bonus, because you get the table indexed. In other words,
> without a primary key (when applicable), you risk making a mess of
> your data and having a crappy performance. But the choice is still up
> to you. :)[/color]

Thanks for the tip, I will try to make one.

I see what you mean, I will try to make the kind of joint ket mensionen
above.

Cah

[Gerard]

Re: "you should define a primary key for this table" - why?

CREATE TABLE `usr_languages` (
`pageName` varchar(25) default '',
`itemNo` int(11) default '0',
`en` text,
`fr` text,
`it` text
PRIMARY KEY ( `pageName` , `itemNo` )
) TYPE=MyISAM COMMENT='differ ent languages';

I guess this should help in you with this table definition.

Regards,
Gerard

[CAH]

Re: "you should define a primary key for this table" - why?


Gerard wrote:[color=blue]
> CREATE TABLE `usr_languages` (
> `pageName` varchar(25) default '',
> `itemNo` int(11) default '0',
> `en` text,
> `fr` text,
> `it` text
> PRIMARY KEY ( `pageName` , `itemNo` )
> ) TYPE=MyISAM COMMENT='differ ent languages';
>
> I guess this should help in you with this table definition.[/color]

Hi Gerard

Thanks a lot - I did it with "alter table" since i alredy have the
table, and it did the trick. I do however think it my be a bug in
phpmyadmin that causes the error, but anyhow it worked.

Thanks for the help.

Cah

[Gordon Burditt]

Re: "you should define a primary key for this table" - why?

>JN wrote:[color=blue][color=green][color=darkred]
>> >| Should one place all the text in one big table, or is i better to place
>> >| it in several tabels. Should one make 4 colums one for each language,
>> >| or 4 rows one for each language?[/color]
>>
>> I use:
>> CREATE TABLE `usr_languages` (
>> `pageName` varchar(25) default '',
>> `itemNo` int(11) default '0',
>> `en` text,
>> `fr` text,
>> `it` text
>> ) TYPE=MyISAM COMMENT='differ ent languages';
>>[/color]
>
>which I used, and it works just fine, except that I just discovered
>that if I place just a litle more than a few line of text - in the TEXT
>fields then i get an "error" in phpmyadmin. There is a small question[/color]

I doubt it is an ERROR. It is a suggestion to improve your database,
much like a grade of A is a suggestion that you didn't do well enough
to deserve an A+.
[color=blue]
>mark were thre is normally a pen and a cross - and when I move the
>cursur above the question mark i get this. "you should define a primary
>key for this table" .[/color]

Conventional wisdom says you should have a primary key for every
table. Among other things, this gives you the ability to delete
one specific record without possibly getting others in the process.
An index also speeds lookup.

Sometimes your primary key is an "artificial " one, maintained
by the database, such as:
id int not null auto_increment
[color=blue]
>Do you have any suggestions as to what the problem might be?[/color]

Bad style.

Gordon L. Burditt