In MySQL Workbench table editor there are 7 column flags available: PK, NN, UQ, BIN, UN, ZF, AI.
PK obviously stands for Primary Key. What about others?
In my MySQL InnoDB database, I have dirty zip code data that I want to clean up. The clean zip code data is when I have all 5 digits for a zip code (e.g. But for some reason, I noticed in my database that for zipcodes that start with a '0', the 0 has been dropped.
locked by Samuel Liew♦Apr 15 '18 at 13:50
This question's answers are a collaborative effort: if you see something that can be improved, just edit the answer to improve it! No additional answers can be added here
Read more about locked posts here.
3 Answers
PK - Primary Key
NN - Not Null
BIN - Binary (stores data as binary strings. There is no character set so sorting and comparison is based on the numeric values of the bytes in the values.)
UN - Unsigned (non-negative numbers only. so if the range is -500 to 500, instead its 0 - 1000, the range is the same but it starts at 0)
UQ - Create/remove Unique Key
ZF - Zero-Filled (if the length is 5 like INT(5) then every field is filled with 0’s to the 5th digit. 12 = 00012, 400 = 00400, etc. )
AI - Auto Increment
G - Generated column. i.e. value generated by a formula based on the other columns
Here is the source of these column flags
This exact question is answered on mySql workbench-faq:
Hover over an acronym to view a description, and see the Section 8.1.11.2, “The Columns Tab” and MySQL CREATE TABLE documentation for additional details.
That means hover over an acronym in the mySql Workbench table editor.
Not the answer you're looking for? Browse other questions tagged sqlmysqlmysql-workbench or ask your own question.
What does 'unsigned' mean in MySQL and when should I use it?
1 Answer
MySQL says:
All integer types can have an optional (nonstandard) attribute UNSIGNED. Unsigned type can be used to permit only nonnegative numbers in a column or when you need a larger upper numeric range for the column. For example, if an INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift from -2147483648 and 2147483647 up to 0 and 4294967295.
When do I use it ?
Ask yourself this question: Will this field ever contain a negative value?
If the answer is no, then you want an UNSIGNED
data type.
A common mistake is to use a primary key that is an auto-increment INT
starting at zero, yet the type is SIGNED
, in that case you’ll never touch any of the negative numbers and you are reducing the range of possible id's to half.