CODEWITHSUNDEEP
mysqlsqlforeign-keyscomposite-primary-keyidentifying-relationship
grogTheFrog
grogTheFrog

Reputation: 19

Expressing identifying and non-identifying relationships in MySQL

Context

I'm learning about identifying and non-identifying relationships, and I'm wondering how I'd express them in MySQL. For practice, I've been working on a database for Pokemon. For context, every few years a new version of the game comes out and updates a lot of things, e.g. the a certain move that a Pokemon can use may get stronger. This update is called generation. Moreover, each move has an elemental type, like fire or water.

So my three entities are move, generation, and type. Since I want to keep track of how a Pokemon move chances over time, a move is in an identifying relationship with generation. The name of the move is not enough to identify it, since, e.g. the move "Karate Chop" is different in generation 1 than in generation 2. So the corresponding primary key in generation, genID, should be part of my primary key for move.

On the other hand, I want to store type as a foreign key in move, but I believe this is a non-identifying relationship. Every move has a type, so I believe it's what's called a mandatory non-identifying relationship.

My attempt

So how would I write this in MySQL? I think it would be something like

CREATE TABLE move (
  moveID int NOT NULL,
  genID int NOT NULL,
  typeID int NOT NULL,
  PRIMARY KEY (MoveID, GenID),
  CONSTRAINT FK_GenMove FOREIGN KEY (genID) REFERENCES generation(genID),
  CONSTRAINT FK_TypeMove FOREIGN KEY (typeID) REFERENCES type(typeID)
);

However, I couldn't find an example where a foreign key was part of the primary key in the MySQL book I'm using (they discuss identifying relationships, but I couldn't find an example with syntax). Specifically, I'm unsure whether the order I list the constraints matters (should I declare my primary keys first, then my foreign keys?)

Indices

Also, I believe that my composite primary key will automatically become a clustered index for my table. A common query one would do is filtering move by generation/genID. So this should automatically be efficient since I have an index on genID, even though its part of a composite key, right? Or do I need to make a separate index for genID alone?

Upvotes: 0

Views: 234

Answers (1)

grogTheFrog
grogTheFrog

Reputation: 19

One thing that I realized the next day is that the order in which I declare my primary key matters. (moveID, genID) will sort by moveID first, then genID, whereas (genID, moveID) would sort the other way. Since I mentioned that I wanted the behavior of the latter case in my original post (picking out all move's in a given generation), as opposed to the former case, I felt that I should point out.

Upvotes: 0

Related Questions