Relationship with a table without unique/primary key

primary-keyunique-constraint

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.

Best Answer

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.

Related Solutions

Surrogate key / Primary key: Better to use an existing unique data field or create a key field

You should never assume that a data point which is outside of the control of your system will never change. This means you shouldn't assume student names won't change. There are lots of reasons in the real world why names might change. Anything that is at reasonable risk of changing is a bad candidate for a primary key. Also, names are very unlikely to be unique over a student population of any reasonable size.

Some exceptions to this might be things which are controlled by an external standards body which can be reasonably well trusted to maintain consistency. This could include something the the IATA code for an airport or the symbol for an atomic element.

Regarding the efficiency of textual (natural) keys vs. integer (surrogate) keys, there is no easy answer to this as it depends on many factors. On balance, it is fair to say that surrogate integer keys are more efficient than natural textual keys - especially if your textual values are much larger than a few characters.

Nevertheless, there are advantages to natural keys beyond raw file I/O and CPU cycles, as long as you can trust the natural keys to be stable. A school's student ID is probably a pretty good candidate because you can institute an internal policy that says student IDs are granted for life and never change.

Postgresql – Email address unique or primary key

Let's first distinguish between keys and indexes, key is part of the logical model and is often implemented with an unique index. You can however create a unique index without creating a key, but that can not be referenced by a foreign key.

A candidate key is something that uniquely identifies a row in a table, in SQL one of the candidate keys are normally used as a primary key (I never really understood why one of the c.k. is concidered "better" than the others, but that's another story), and the remaining c.k becomes unique constraints.

A unique constraint can be used the same way as a primary key can. Consider:

create table A ( x ... not null
               , y ... not null
               , z ... not null
               ,     unique (x)
               ,     primary key (y,z) );

create table B ( x ...
               ,   ...
               ,     foreign key (x) references A (x) );

create table C ( y ...
               , z ...
               ,   ...
               ,     foreign key (y, z) references A (y, z) );

B references the unique constraint and C references the primary key constraint.

NOT NULL is yet another kind of constraint. In your case you can enforce this for email without declaring it unique.

The next aspect of your post concerns the stability of a key, a key should be stable (but that doesn't mean it can never change, it does not have to be immutable). Some DBMS implements ON UPDATE CASCADE that can be of help for such operation, still if the key is distributed around your model it will be a pain updating it.

In your case I would probably choose another candidate key as the primary key, and declare email to be NOT NULL and UNIQUE.

Best Answer

Related Solutions

Surrogate key / Primary key: Better to use an existing unique data field or create a key field

Postgresql – Email address unique or primary key

Related Question