Relationship with a table without unique/primary key

Question

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.

Answer 1

Using surrogate IDs is one way but you don't really have to. If you want to keep your current design - which I suppose has Work (workName) as primary key and SubWork (workName, subWorkName) as a potential primary or unique key, you can define the Activity table like this (I also changed slightly the column names):

Work
--------
workName      PK


SubWork
--------
workName      PK ,  FK -> Work (workName)
subWorkName   PK


Activity 
-------- 
activityID    PK       -- or what other column you have as primary key
workName        FK1
subWorkName     FK1  
  FK1 (workName, subWorkName) -> SubWork (workName, subWorkName)

As @raumkrieger points out, to define a foreign key constraint you have to reference a unique or primary key column (or columns.) Since if we assume correctly that (workName, subWorkName) unique identifies a row in the SubWork table, you can use this combination to be referenced by the foreign key.

---

Note: I do not suggest that using long varchar columns is always better than using narrow surrogate integer columns. Especially if the tables are big (or plan to be) and/or the varchar columns are very wide, this will not be the most efficient design in most DBMS. But for narrow varchar columns and/or smaller tables, the difference in performance will be negligible.

Answer 2

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.