Relationship with a table without unique/primary key I have a database with 3 tables.

Table Work, with a field named workName.

Table SubWorks, which have a field subWorkName and work with a relationship with Work.workName.

And table Activity with field superworkname which should have a relationship with SubWorks.subWorkName. 

I can't create this relationship because SubWorks.subWorkName is not an unique key (or primary key, it just don't appear in phpMyAdmin) and it can't be because it's allowed to have multiple SubWorks with the same name until Work.workName is different between them.

How can i resolve this situation? I'm thinking about using a primary key int, but what if one day i go in overflow? Suppose i got so much works to store that i go out the limit?



I added an ID column to Activity so i can differentiate them

That's what i want to do with relationships:

I have a Work named R1.

3 SubWorks, named: R1-1, R1-2, R1-3 with a relationship to R1.

And 2 Activity (ID: 1, 2), with refer with subWorkName to R1-1.
And another activity with ID 3, which refer with subWorkName to R1-2

I want, if i delete R1: R1-1, R1-2, R1-3 will be deleted too and with them all their activity, so activity 1 and 2 will be deleted to.

Now, suppose i delete R1-1, activity ID 1 and 2 should be deleted too.

If i delete R1-2 activity ID 3 should be deleted.

I already did the first part (if i delete a Work, SubWork will gone with it) but i had problem with the second.
Primary keys (and foreign keys) should be unique, otherwise how does the database know which row the foreign key relationship is pointing to?


  I'm thinking about using a primary key int, but what if one day i go in overflow? Suppose i got so much works to store that i go out the limit?


I wouldn't worry about hitting the limit for ints. The mysql documentation lists the limits of various int types. If you're concerned about having more than 2 billion rows anytime soon, you can always use bigint and have over 9 quintillion rows before overflowing. 18 quintillion if you used unsigned.