SQL experts: can two columns be set up as a single foreign key?

Factual Questions
1

So let’s say that I have table A that contains (among other columns, of course):

userId: int not null,
projectId: int not null

Now, I want to set up table B such that for each unique userId/projectId pair, there can be any number of rows. (Say, for example, table B contains a row for each day worked on a particular project by a particular person. So the userId/projectId pair identifies the person working and the project he’s working on, and then that pair is used to find all the days in table B.)

The question is: How can I set up table B with the userId and projectId columns as foreign keys to table A, but treated as a pair rather than individually?

I tried just declaring both columns in table B separately as foreign keys, but I quickly ran into this problem: Say I have two rows in table A:

userId projectId
1 1
2 1

And I have some rows in table B where:

userId projectId
1 1

Now I want to delete that second row from table A. If the foreign keys in table B are declared separately, I can’t delete that row because it violates a foreign key constraint on the projectId even though, from my “conceptual” view the foreign key is the pair (1, 1) or (2, 1), and so the (2, 1) row is deletable.

I’ve ended up “solving” this by just not using foreign keys at all, but it did get me to wondering if there’s a way to do it that I just couldn’t find.

2

The correct syntax to achieve this may depend on what database you’re using. When you set up tables A and B, you need to make it clear that the two columns are a single primary and foreign key. I have a feeling you may have made the columns in table B two separate foreign keys.

In MySQL, for example, you would use a schema like this:

create table TableA (userId int(11), projectId int(11) … primary key (userId, projectId) );

create table TableB (userId int(11), projectId int(11) … foreign key (userId, projectId) references (TableA.userId, TableA.projectId) );