0

This seems like a desirable feature but I can't seem to figure out how I would do it while the foreign key is a part of the primary key (composite key).

The table is a simple junction table for a many to many relationship referencing User.id and Access.id referencing functions a user has access to:

Column |  References
user      user.id
access    access.id

Therefore there can be many entries for each user and each access level. To simplify things for "superusers" I wanted to have a NULL value for access which would mean they have access to every page (this is the only way I could figure how to enter a value that didn't reference a row in the access table). The problem is MySQL won't allow a NULL value as a part of the primary key.

Is there a way around the NULL issue with primary keys or is there another way to reference every row (or no rows) in a foreign key? The only other way around this I can think of would be to disable the foreign key or have to add a row for every access.id in the table. Either of which would be undesirable.

Devon Bessemer
  • 34,461
  • 9
  • 69
  • 95

2 Answers2

0

Presumably you have a superuser flag on your user table. You could UNION a Cartesian join of each superuser and the set of available access IDs into whatever query you need this for.

Depending on what you're doing, you could also just not store the access for a superuser in the database and treat them differently in code - i.e. ignore the access check once you've established them as SU. Depends on your application though.

LoztInSpace
  • 5,584
  • 1
  • 15
  • 27
  • Thanks for the contribution. This question is just as much theory as practice for me so I'm going to see if there is anything that can be done on the design of the junction table or relationship tables rather than altering code or adding a new table. – Devon Bessemer Nov 11 '14 at 00:31
  • You do not have to add a new table, just alter the query. Unless of course you do not have a superuser flag in which case you are going to be stuck adding all the access IDs. – LoztInSpace Nov 11 '14 at 00:38
0

I think NULL is allowed and you can use it as a unique combination along with user.id. But I am not sure if this is a good way to do this. I mean you can store the super user setting in a column and use it in the code than here.

Pradeep
  • 2,469
  • 1
  • 18
  • 27
  • A unique key wouldn't be effective. This type of relationship should have these fields be a composite key and that is where the issue lies. – Devon Bessemer Nov 11 '14 at 01:19
  • I am sorry to use the unique word. I meant that NULL is allowed as a value to a primary/composite key. – Pradeep Nov 11 '14 at 16:30