How to make the combination of two column as primary key?

[shihab2555]

Hi,
I made a table named staff_info containing 4 column name,day,duty_start,duty_finish. This table still have no parimary key. Now assume that there are 4 staff and they do their work in each day of the week.That means in each day there work 4 staff and 1 staff work in 7 days. Ultimately i can't either make name or day column as my primary key.But combination of two column value is unique.Is there any way that i can make the combination of these two column as any kind of primary key? I think i made myself clear.

[cereal]

I'm not sure to understand what you need but you could add a primary key with both fields:

alter table staff_info add primary key('fieldA','fieldB');

just make sure these are less than 1000 bytes and not nullable; if the same value combination is submitted the query will fail for duplicate entry. Or take a look to unique kyes. In any case paste the table structure here, it will help, bye!

[shihab2555]

i think i didn't make myself clear
letsrdaw a table

name day duty_start duty_finish

mr. A saturday 8:00 12:00

mr. A sunday 12:00 16:00

mr. A monday 16:00 20:00

mr. b saturday 12:00 16:00

mr. b sunday 12:00 16:00

mr. b monday 8:00 12:00

here you see in name column there are duplicate value and in day column there are duplicate value.so i can't make them primary key separately.But combination of them is unique.Is their any way to make their combination as primary key

[mwatson81]

Based on your example you should be about to make a compound primary key of name and day.

[shihab2555]

How to make compound primary key?

[urtrivedi]

cereal already told you the solution
alter table staff_info add primary key('name','day');

or you can open table structure in phpmyadmin, and check on both column, then click on primary key at the boottom of table structure.

So after doing this, table will not expect same day for any person.

But still such table will have maximum only 7 records for any person, better you do make it name and date (not day) as primary key.

[vgaldikas]

Is there any particular reason you need to use there particular fields for PK?

In my opinion it is bad to use chars/strings as primary keys. Did you try to think about alternative table design?

I could suggest something, but dont know what you are trying to achieve.

~Vytas