0

I'm playing with some ideas. Basically I want a foreign key but I want the table that the key should be used to also be in the row. So that the foreign keys can refer to different tables. Generally you'd only be returning one result from any given query that needed to dereference this 'pointer'.

I know I could just store the table name and use two select statements to do it, inserting the result of the first select to get the table and key into the next call to index the right table, but can it be done with a single select on the database? What would the sql look like?

EDIT: Let me see if I can come up with an example. MediaTable ID | Name | Owner| Type | FKey

Mp3 ID | Artist | Album | Title | BitRate | Codec | Data

Movie ID | Title | Resolution | imdbUrl | AudioCodec | VideoCodec | Data

OK contrived but ... leta say I want to search the MediaTable for all Titles (in Mp3 or Movie tables) where MediaTable.Owner = "ekl" assuming FKey is the foreign key of the ID field. Can "MediaTable.type" select which table that FKey should apply to?

Yes, I know its not the best way to do this particular example, but its easier than explaining two larger projects where I would want this.

Evan Langlois
  • 4,050
  • 2
  • 20
  • 18

2 Answers2

0

Basically, what you want to do is to parameterise a foreign key and add which table to refer.

Theoretically, it can be done by string concatenating your query and executing it in one go. But, fetching the component strings might need multiple calls or a procedure call.

Even though it is theoretically possible, I'd like to warn that, what you're trying to do is extremely unconventional and leads to code being slow (due to string concatenation) and non-maintainable.

Sarath Chandra
  • 1,850
  • 19
  • 40
  • Procedure call sounds like an interesting way to go. Otherwise, you basically end up with two queries, right? One to figure out which table and the other to build the final query. Procedure call wraps the nastiness. I didnt think of that! Yes I know its a hack. – Evan Langlois Jul 17 '15 at 22:34
  • You might find this helpful: http://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables – Sarath Chandra Jul 17 '15 at 22:41
0

Foreign keys, as such, don't do much more than validate the integrity of your data on insertion. Any column that you get as result of a SELECT, you get because you explicitly asked for it when you did the SELECT, not because the database automatically follows the foreign key references. If you have a good reason, you could design your whole schema without using any FK constraints at all, and it would work just fine.

It follows that, if you want to search several tables at once, you need to explicitly select from those tables. In your example, this could be done with a UNION:

SELECT mt.fkey, mt.owner, mt.type, media.title
  FROM mediatable mt INNER JOIN 
       (SELECT id, 'MP3' AS type, title FROM mp3
        UNION
        SELECT id, 'MOVIE', title FROM movie) media
       ON mt.fkey = media.id AND mt.type = media.type
 WHERE mt.owner = 'ekl';
Mihai
  • 2,835
  • 2
  • 28
  • 36
  • that query would require that the id fields be unique across both mp3 and movie tables since there is no specific selection of the table – Evan Langlois Jul 17 '15 at 23:14
  • You might also be interested in the answer to http://stackoverflow.com/questions/3579079/ – Mihai Jul 17 '15 at 23:24