New with databases, having problems wiith primary key

Question

I have a table where events organized by a sports center are stored. I created it like this:

create table events(
  name varchar(20) primary key,
  description varchar(150),
  type varchar(20) not null,
  event_date not null,
  event_start time not null,
  event_end time not null,
  room_name varchar(20) not null,
  foreign key(room_name) references rooms(name) on update cascade on delete cascade);

now I have realized that events with the same name can exist as long as they are not on the same day. For example, a swimming competition can be held several times in a month and still have the same name. How can I modify the table so that the primary key is made up of name and date without having to delete and re-create the table?

Why not have an auto-increment integer primary key and leave the data for the data columns? — obe, Jun 01 '21 at 08:51
add a unique attribute to the competition name. And imho you should have an autoincrement id as primary key, not the competition name — Lelio Faieta, Jun 01 '21 at 08:58
@KenzoBetter Is there a reason why you want to go for composite primary keys consisting of name and dates? Why not have two separate tables event_name(id, name, description, type) and events(event_name_id, event_date, event_start, event_end, room_name) ? — Manpreet Krishan, Jun 01 '21 at 08:58

score 1 · Answer 1 · answered Jun 01 '21 at 08:51

1

You could use an alter table statement to drop the primary key and add a new one:

ALTER TABLE events DROP PRIMARY KEY;
ALTER TABLE events ADD PRIMARY KEY (name, event_date);

answered Jun 01 '21 at 08:51

Mureinik

297,002
52
306
350

score 0 · Answer 2 · answered Jun 01 '21 at 10:19

It's worth reading up about primary keys.

As @mureinik says, you can drop the original key and create a new key - but your design has a few problems.

Firstly, primary keys should never change. Columns like "Name" suggest they can change - even if the user corrects a typo, or if the event changes its name (e.g. to add a sponsor). This goes for "room" too.

Secondly, your design combines two logical entities into a single table. I think you have an entity called event_type (tennis tournament), and an event instance (1 July 2021). I think there will be more attributes of event type, which you won't want to copy for each instance (e.g. "description").

So, I think your schema is more like

event_type
----------
event_id int pk
event_name varchar
event_type varchar
event_description varchar

event_instance
--------------
event_type_id int fk pk
event_date date pk
event_start time not null,
event_end time not null,
room_id int fk

New with databases, having problems wiith primary key

2 Answers2