Bug #45397 Edit table GUI shows the wrong column flags
Submitted: 9 Jun 2009 11:04 Modified: 13 Dec 2012 17:13
Reporter: Karsten Wutzke Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.2.4a OS:Any
Assigned to: CPU Architecture:Any
Tags: ai, column flags, Edit Table, nn

[9 Jun 2009 11:04] Karsten Wutzke
Description:
When editing a table, to the right of each column two exactly two check boxes appear (NN and AI) plus a column type specific list of checkboxes to te right.

The policy of the flags shown is wrong.

How to repeat:
Look at the "Edit Table..." tab for columns... NN and AI don't really fit together. NN is for every column, AI only for integers.

Suggested fix:
1. The AI flag shown for all columns is wrong. It is applicable for integral types only. Thus, it should be placed to the right into the column's type-specific list of flags.

2. The NN (not null) flag is the only correct flag here.

3. The primary key constraint is applicable for *every* column basically, so it does belong to the main area, actually it is the most often changed constraint and deserves a place to the left of the NN constraint. Disable check boxes for type that can't be used as primary keys (are there any???). Even BLOBs can be indexed, so the PRIMARY KEY constraint definitely belongs to the main area.

4. It would be nice if one could see check boxes for the foreign keys there as well. Grey them all out, they can't be specified here, but should show a check when the foreign key is in effect.

5. As a fourth flag on the main panel the UNIQUE constraint is missing entirely. Again, it qualifies for (almost?) all types.

The column data type specific flags all belong to the checkbox list to the right:

1. For INT's these are AUTO_INCREMENT (AI), UNSIGNED (UN), ZEROFILL
2. For Strings these are BINARY, ASCII, UNICODE

With this solution you would map the SQL CREATE TABLE constraints to the "Edit table..." main window:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(] LIKE old_tbl_name [)];

create_definition:
    column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
  | KEY [index_name] [index_type] (index_col_name,...)
  | INDEX [index_name] [index_type] (index_col_name,...)
  | [CONSTRAINT [symbol]] UNIQUE [INDEX]
        [index_name] [index_type] (index_col_name,...)
  | FULLTEXT [INDEX] [index_name] (index_col_name,...)
      [WITH PARSER parser_name]
  | SPATIAL [INDEX] [index_name] (index_col_name,...)
  | [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name,...) [reference_definition]
  | CHECK (expr)

...

If MySQL supported them, even CHECK constraints would fit here.

Implementation:

1. The order of the four flags is probably PK - FK - UQ - NN.
2. The PK flag sets NN to checked and disables them.
3. All FK flags are disabled.
4. Maybe the PK flag unsets the UQ flag, not sure though. You decide ;-)
[9 Jun 2009 12:32] Valeriy Kravchuk
Thank you for the UI changes suggested.
[12 Jun 2009 1:57] Alfredo Kojima
PK can be toggled by double clicking the 1st column. However I agree that it's non-intuitive and probably very few people know about that. But it does sound like a good idea to redesign the columns list.
[17 Sep 2009 18:59] Karsten Wutzke
I looked at the 5.2.3a changes.

The flags shown should always be in this order:

PK | UK | FK | NN | Default

1. UK = unique key is missing. We need this.

2. FK should be shown, all disabled. They're just for information purposes. Like this, you can immediately recognize identifying relationships (where PK and FK are checked).

3. NN should immediately be followed by Default, because you can immediately see if the defaults are correct, which are NULL for nullable columns most of the time.

All other flags (BIN, UN, ZF, AI) *should not necessarily be here*!

That's because they are *no general* flags. They are *type-dependent* and should go where they used to be: into another area to the right, above the comment. I liked this a lot more.

A general request:

Please colorize the checkboxes that are functionally disabled! This holds true for all type-dependent and FK checkboxes. These are GUI basics.
[17 Sep 2009 19:25] Karsten Wutzke
I apologize, but UK should be UQ, because UNIQUE is a constraint, not a key... because of this, you should probably use the order:

PK | FK | UQ | NN | Default

This looks like the best you can get: PK-FK for identifying relationships, all constraints thereafter (UQ + NN), followed by Default, which is not a constraint, but because of the NULL issues should be placed next to NN.
[15 Oct 2009 6:43] Susanne Ebrecht
Karsten is right here. Unique is a constraint.
[15 Oct 2009 21:17] Karsten Wutzke
Two things:

1. I'd very much appreciate if this RFE would make it to 5.2, not 6.0. It is my personal #1 bug report. :-(

2. WB 5.2.4a is already a small improvement, however, it would be nice to have FK next to PK, too, even if these are not togglable. It is a very intuitive element for recognizing identifying and non-identifying relationships. Playing with the PK check marks when seeing FK next to them makes immediately clear what identifying and non-identifying relationships are, without have to read the manual or entering these terms into a search engine.

PK | FK | UQ | NN | Default

should always show

AI | UN | ZF | BIN

belong to the "old" *type-specific* box to the right.

Note, that AI and a Default value exclude themselves as well (mixing AUTO_INCREMENT and DEFAULT raises an error in MySQL). It doesn't make sense anway.
[2 Nov 2009 12:46] Karsten Wutzke
I noticed another issue, see here:

http://bugs.mysql.com/bug.php?id=48468
[13 Dec 2012 17:13] Philip Olson
This is an obsolete request, so it is being closed.