Bug #48896 Changing a primary key column to nullable should report an error

• Submitted: 19 Nov 2009 9:53
• Modified: 19 Nov 2009 13:59
• Reporter: zohar aharoni
• Status: Not a Bug
• Category: MySQL Server: Errors
• Severity: S3 (Non-critical)
• Version: 5.1.37
• OS: Any
• CPU Architecture: Any

[19 Nov 2009 9:53] zohar aharoni

Description:
If a table has a column which is part of a multi-column primary key then that column cannot be nullable.

If an ALTER TABLE command is executed to change that column to be nullable, then MySQL completes the command successfully, reports no errors or warnings and lists all the rows as affected, but does not change anything.

If no changes are done, at the minimum I'd expect no rows to be affected. If a command is executed to change something which is not legal (such as making a primary key column nullable) then at least a warning should be emitted.

How to repeat:
CREATE TABLE test (id INT NOT NULL, id2 INT NOT NULL, PRIMARY KEY (id,id2));
ALTER TABLE test MODIFY COLUMN id2 INT NULL;

Suggested fix:
Show a warning when such a command fails to change anything, possibly explaining why.

[19 Nov 2009 13:59] MySQL Verification Team

Thank you for the bug report. This is documented behaviour:

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

"A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table ......"