MySQL 5.1.36

I use a table "resource_names" to make sure resouce names are unique throughout the application.
| resourceNameId | resourceName | resourceNameAssigned | resourceNameDescription |
| primary key | unique | | |

Resouce names are assiged in "audio", video", "images" and "texts" tables.
Is there a way to restrict the use of each resourceNameId to one within the "audio", "video" . . . tables?

Thanks,
David

yes, add a column called "tabletype" (or similar name) and declare a UNIQUE index on the combination of (tabletype,resourceName)

or you could ditch the useless auto_increment, and make (tabletype,resourceName) the primary key instead

Thanks Rudy,
A resource name has to be unique for the whole app.
So I left the UNIQUE constraint on "resourceName" in the table "resource_names" and added a column "resourceType" ENUM('audio','video','image','text'), like you suggested, just without including it to the UNIQUE constraint. So a resource name is still unique in the whole application.
The 'audio', 'video', 'image', 'text' tables have a foreign key set to 'resource_names'.'resourceNameId' on their 'resourceNameId' column and a UNIQUE constraint as well.
Now that should take care of UNIQUE resource names.

Is there a way to "filter" a foreign key to only the rows in 'resource_names' that have the appropriate 'resourceType' set?
That would prevent inserting a 'resourceNameId' of the wrong type into the 'audio', 'video' etc. tables.

In other words: Can I restrict the entry of 'resourceNameId's into the 'audio' table to the 'resourceNameId's that have 'audio' as the 'resourceType' etc.?

David

hold on thar, baba louie

, i never suggested ENUM, in fact if you google "ENUM is evil" i'm sure you will run across previous threads where i say just that

hold on thar, baba louie

you have separate tables for each of these different types? may i ask why not just one table?

yes, if you really want separate tables, there is a way to do that

ENUM is evil . . . will read more about it.
I don't need to use ENUM.

The video type has titleTextId, thumbImageId, filePath, width, height, bytes and duration as defining columns.
Audio has filePath, bytes and duration columns.
Image has filePath, width, height and bytes.
Text has just a text column.
All types exist for a variable amount of locales.

I do not see how to put this in one table and still have a normalized database structure.

you could have a supertype/subtype structure

the supertype table would have common columns like filePath, and presumably name and/or description as well, along with date added, added by, nomber of hits, etc.

the subtype tables would have only those columns unique to that type

see Data Based : Distributed Keys and Disjoint Subtypes for a method to enforce type codes

This is good advice Rudy, thanks.
Just too bad that MySQL (even 5.5) does not validate CHECK CONSTRAINTS and data is inserted or updated.

I have to figure out a workaround.

I thought about creating a table with one row only for each resource type e.g. "image_resource_type" and set a foreign key in the "images" table.
That should work, butt seems way too elaborate a design.

David

Hi,

this could be performed through triggers. From MySQL 5.5 SIGNAL's are available which allows you to raise an exception:

These were not available prior to 5.5 as a result we could never raise an exception inside a trigger. The code above is to just show how the signal could work i.e. very the inserted or updated entries and either commit to the database or raise an exception.