166

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?

Ivan
  • 63,011
  • 101
  • 250
  • 382

3 Answers3

362

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

ctrl-alt-delor
  • 7,506
  • 5
  • 40
  • 52
Vasiliy Kulakov
  • 5,401
  • 1
  • 20
  • 15
  • 11
    @Ivan: A unique constraint ensures that a value can only ever exist in the column *once*. Any attempt to add a duplicate will result in an unique data constraint error. The unique constraint as supports covering more than one column - this is called a composite. – OMG Ponies Sep 08 '10 at 01:38
  • 4
    @Ivan -- it is a way to help index values while preventing duplicates. For example -- e-mail and SSN fields for a unique list of customers should be a unique index, since you frequently do lookups using those fields and they shouldn't repeat. For arbitrary values used only by your database internally for frequent lookups (such as customer ID), you should use a primary key with an auto-increment option instead. – Vasiliy Kulakov Sep 08 '10 at 01:46
  • UQ works by adding a unique index so as to maintain the uniqeness check. You do not need to set it if the columns is already the sole member of the primary key as the primary key is assumed to be unique. – ClearCrescendo Jun 12 '14 at 05:22
  • 7
    Some tooltips would be really nice here. – Kevin Cox Sep 26 '14 at 14:34
  • @VasiliyDeych, So for `UQ`, `U` means unique. What does `Q` mean? – Pacerier Dec 18 '14 at 07:11
  • 1
    @Pacerier it's not an acronym or initialism, it's just an abbreviation. `UQ` means unique. – wmassingham Feb 13 '15 at 16:10
  • 1
    @wmassingham, `UK` should be the right choice since we have `PK`, `ZF`, and `AI`. – Pacerier Feb 17 '15 at 03:25
  • 1
    @vasiliyDeych: what is the use of `BIN` flag when I've set a conflicting datatype (eg. `VARCHAR(10)`) or a corresponding binary string datatype (eg. `BINARY(10)`) ? When should I exactly use this flag? Is it permissible to use in case of columns with `BINARY` datatype only? – Nikunj Madhogaria Aug 18 '15 at 14:13
  • 7
    What about "G"? – Jin Kwon Jul 12 '16 at 10:00
  • If you have more than one unique column, it's better to set that up on the indexes tab, so you have more control about grouping them. – DanMan Jul 24 '16 at 09:28
  • 4
    G = Generated column. i.e. value generated by a formula based on the other columns. – MortimerCat Jul 31 '16 at 11:59
19

Here is the source of these column flags

http://dev.mysql.com/doc/workbench/en/wb-table-editor-columns-tab.html

Alex Lamson
  • 479
  • 5
  • 14
macio.Jun
  • 9,647
  • 1
  • 45
  • 41
  • It looks like MySQL team moved the page to this url: http://dev.mysql.com/doc/workbench/en/wb-table-editor-columns-tab.html – humkins Mar 04 '15 at 22:59
  • 2
    It would help if some of the information from the page was in the answer, rather than just having a link. That way the answer is safe even if the link goes down (again). – IQAndreas Feb 01 '16 at 08:46
-1

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.

Section 8.1.11.2, “The Columns Tab”

cellepo
  • 4,001
  • 2
  • 38
  • 57