![]() ![]() Now, if I took what you were saying above, my UK1 would be correct one-way, but not the other way. Let's say you have a textbook order system modeled out, and you have our favorite ORDER -||-||-||- PRODUCTĪnd the way I prefer to do things, I would have this for my junction table. Sound doubling up on Indexes is okay, right?Īctually while I was waiting for your response, I had another question that relates to this last point. ![]() As a result the veh_type_code will need it's own index to support that foreign key definition. Because veh_id is the value that ties all the tables together, you want it to be the first value in your unique / primary key constraint. However if you define the order as (product_type_code, id) then you can search for a product_type_code using that index and get all the matching ID's as a result.Īpplying this to your stack exchange example, it also means that you in fact cannot double-dip on your indexes and will need to create extra ones to support your foreign key definitions. If you define the order as (id, product_type_code) then you can't use that index to search for a product_type_code unless you also specify an ID (and since ID is unique, that makes the product_type_code mostly useless). Why exactly do you have to do it in that order?īecause indexes work essentially left-to-right, so if you want to take advantage of a column being part of an index, it either has to be the left-most column of the index, or you also have to specify all the columns prior to it. questions/17405702/mysql-supertype-subtype-design Using this approach - and this is the cool part - ensures that there can only be ONE SubType that maps back up to the parent SuperType. However, I am creating a composite UK so that I can join my Supertype PRODUCT table with the SubType SUBSCRIPTION table, and those two tables are linked by using "id" and "product_type_code". So if the PRODUCT table was in isolation, then yes, i agree that is all I need to uniquely identify records. ![]() So, in the simplified example above, my PRODUCT table has an ID that is the PK. I only showed a snippet of the larger design, which includes me implementing a rally neat implementation of Super/SubTypes in MySQL. In a scenario where Id wasn't a primary key and a unique constraint was necessary then you could double-dip like that. If I had to guess, I would say that i should first create an IDX on "product_type_code", then create my FK on 'product_type_code' and then I could create the composite UK on the "id" + "product_type_code" columns.Īs mentioned though, in the scenario you laid out, the unique constraint is entirely unnecessary as your ID column is already unique by virtue of being the primary key so you'd just make a simple index on the product_type_code column. I have a question about the correct way to do a FK and Index(es).ġ.) In MySQL, you *must* have an index on a column in order to create a FK constraint.Ģ.) The "product_type_code" is a FK and points to a lookup table.ģ.) The "id" + "product_type_code" need to form a UK because I will be using that as a join to another table.Ī.) Do I need to create a regular Index on "product_type_code" so that I can in turn assign a FK to that single column?ī.) Or is it possible to double up and use my composite UK on the "id" + "product_type_code" both as a way to join those two fields to another table, PLUS as a way to serve as an index for my FK on "product_type_code"? ![]()
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |