Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Modify column in phpMyAdmin to have SET Length/Values match a column in another table or pull from a table

Posted on 2016-07-27
7
Medium Priority
?
68 Views
Last Modified: 2016-07-27
TABLE: tbl_a
COLUMN: category
TYPE: SET
LENGTH/VALUES: '1', '2', '3'

TABLE: tbl_b
COLUMN: category
TYPE: SET
LENGTH/VALUES: I want to match the Length/Values of tbl_a, can this by done dynamically so I don't have to re-enter them?

Or better yet, is the following an option?

TABLE: tbl_category
COLUMN: category
RECORDS:
1
2
3

TABLE: tbl_a
COLUMN: category
TYPE: SET
LENGTH/VALUES: Something like: "select category from tbl_category order by category"? So the Length/Values are stored in a separate table which can be reused in multiple places.

Thanks.
0
Comment
Question by:bbdesign
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41731794
Is data type SET something you've picked up from another data base application? I can't find any information on a this data type to use as a reference.

But what you're describing sounds a little like a relational table. The values for a particular column are constrained to the values available in another table. This can also reduce the size of the table when the second table (foreign table) has a key column. The main table with thousands of records contains the much shorter key instead of the word(s) associated with that key.

MySQL does support foreign key constrains when the storage engine is InnoDB. The caveat to using relational tables is that you must join the related table in your queries when using keys.
0
 

Author Comment

by:bbdesign
ID: 41731838
When I click on a table in phpMyAdmin and click Structure, then click Change on any column, one of the Type options is SET. Then, in the Length/Values field you can put in a comma-separated list, such as:

'1', '2', '3'

...then when entering data into that table, you get a drop-down list in phpMyAdmin with only those values available to be selected.

Yes, I believe I want to do what you are describing. I was hoping there was an easy way to do this in phpMyAdmin, when defining the table structure, but I don't see how to do it.
0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41731870
So it is. You've taught me something.

As long as the storage engine is InnoDB, when you look at the table structure in phpMyAdmin, you'll see a link for Relation View under the horizontal line below the field definitions. It's just to the right of the Print View button. There you can reference fields in other InnoDB tables.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 

Author Comment

by:bbdesign
ID: 41731947
Yes, I do see Relation View. I clicked it, but I have no idea what I'm doing at that point. It should allow me to do what I described earlier, insert the values from one table's column into the "list of possible values" for another table's column? I.e., the second thing I described in my original question.
0
 
LVL 22

Accepted Solution

by:
Kim Walker earned 2000 total points
ID: 41732053
No. It doesn't insert it. It links that column to another column in the same table or another table in the same data base. For example, if you have a table for contact information that includes a prefix column and you wish to limit the values for that column to Mr., Mrs., Ms., you would create a second table called prefixes which has one or two columns and three rows, one for each possible prefix. One column could be labeled name which would contain the prefix name (Mr., Mrs., or Ms.)  The second column would be for a key if you wish to use keys instead. Then in the relation view for the first table, you would select the drop-down beside the row for prefix and select the table and column to link to -- in this case prefixes.name. If you are using keys, you'd select the key column instead.

Now, if you attempt to insert a record in phpMyAdmin, you'll see a drop-down to select your prefix instead of an open text box. If you attempt to manipulate a record via SQL with any other value for a prefix, you'll get an error based on your selection for ON DELETE and ON UPDATE. These are called Referential Actions. You can consult the documentation for a description of each referential action.
0
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41732059
Correction, if you attempt to manipulate the data in the main table with a prefix value other than those three, MySQL will reject the operation. The referential action refers to any attempt to update or delete a record from the prefixes table which the documentation refers to as the parent table.
0
 

Author Comment

by:bbdesign
ID: 41732082
Awesome, I got it to work. Thank you so much!
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question