Member Avatar for 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.

  • 5 Contributors
  • 6 Replies
  • 190 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by vgaldikas

Dani AI

Generated

Short answer: yes — MySQL accepts a compound primary key so the pair (name, day) can enforce one row per person per day. , and are correct on that point. That said, using text columns as the table's primary key has practical downsides (collation surprises, index size, and update cost), which is what was warning about.

Recommended, practical design: normalize the data so staff have numeric IDs and the schedule references that ID. Use appropriate types for the fields (TIME for start/finish; DATE or a small integer for day-of-week). Example schema pattern:

CREATE TABLE staff (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (name)
);

CREATE TABLE schedule (
  staff_id INT UNSIGNED NOT NULL,
  weekday TINYINT UNSIGNED NOT NULL,  -- 0=Sunday .. 6=Saturday
  duty_start TIME NOT NULL,
  duty_finish TIME NOT NULL,
  PRIMARY KEY (staff_id, weekday),
  FOREIGN KEY (staff_id) REFERENCES staff(id)
);

Migration checklist (safe, in-order):

  • Find duplicates before adding any key: run a grouped COUNT to detect pairs that already repeat and fix them.
  • Populate a staff table from distinct names.
  • Add a staff_id column to your existing table and UPDATE rows by joining on name.
  • Move rows into the new schedule table (map weekday names to numbers with a CASE or manual mapping).
  • Once data is moved and verified, drop old columns and add the constraints you need.

Notes and cautions:

  • Primary-key columns must be NOT NULL.
  • If schedules are for specific dates (not weekly patterns), store DATE instead of weekday.
  • Back up the table before schema changes.
  • If you prefer to keep a single table, a UNIQUE constraint on (name, day) is an alternative to a PK — but using numeric IDs is generally more robust and efficient.

Recommended Answers

All 6 Replies

Member Avatar for 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!

Member Avatar for 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

Member Avatar for mwatson81

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

Member Avatar for shihab2555

How to make compound primary key?

Member Avatar for 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.

Member Avatar for 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

Be a part of the DaniWeb community

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