MySQL Bugs: #30522: inserts on composed key will default to 0 if one of the key members is missing

Bug #30522	inserts on composed key will default to 0 if one of the key members is missing
Submitted:	20 Aug 2007 21:48	Modified:	21 Aug 2007 7:23
Reporter:	chris vlad	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.20	OS:	Linux (intel compiler)
Assigned to:		CPU Architecture:	Any

Description:
On a table with a composed key if i insert a row where i am missing one of the key values, mysql will defualt the missing value to 0 producing invalid data.

I am not specifying any value for column suggested_id
mysql will insert it with an warning "Filed 'suggested_id' doesn't have a default value" error 1364. 

select * from tb_table will result in 5 | 0 making an invalid entry in my table, I may not have a sugested_id=0

I tested in Ms SQL 2000 and is giving a hard error and will abort the insert, i was expecting a similar hard error and an abort instead of defaulting to 0.

Chris Vlad
chrisvlad@commandaccess.net

How to repeat:
CREATE TABLE tb_table (
  `prod_id` INTEGER UNSIGNED NOT NULL,
  `suggested_id` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (`prod_id`, `suggested_id`)
)
ENGINE = InnoDB;

insert into tb_table (prod_id) values (5);

select * from tb_table;
5 | 0

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read about default values at http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html