Go Premium for a chance to win a PS4. Enter to Win

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
?
70 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
  • 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month5 days, 19 hours left to enroll

773 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