MySQL Insert Data Question

Question

Hi, assume I already created a table in MySQL as below

CREATE TABLE IF NOT EXISTS `sales` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `client_id` smallint(5) unsigned NOT NULL,
  `order_time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `sub_total` decimal(8,2) NOT NULL,
  `shipping_cost` decimal(8,2) NOT NULL,
  `total_cost` decimal(8,2) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `sales`
--

If I added a new field must_fill for the current table.

 `must_fill` tinyint(1) unsigned NOT NULL,

User can insert less than the number of fiels items to the table defaultly, just as the script of below.

INSERT INTO `sales` (`id`, `client_id`, `order_time`, `sub_total`, `shipping_cost`, `total_cost`) VALUES
(8, 12312, '2007-12-19 01:30:45', 10.75, 3.00, 13.75);

It's fine.

But How can I configure the field (must_fill) to a MUST INCLUDE Data field when user plan to insert into new data.

BTW, The code will be integrated in PHP script.

[update] Or, can I only write special PHP script to judge must_fill is null or not when user try to run a Insert Into script binded some data values?

Answer 1

It's difficult to enforce this as MySQL was never designed in quite that way. A NOT NULL designation in a MySQL table merely means the value can never be NULL, not that a value must be supplied: MySQL will find a default if it is omitted. However, you can provide a default value that would be annoying if a real one was omitted; exactly what to use depends on your application.

I would also look at MySQL Server Modes. You might want to enable STRICT_ALL_TABLES, but that's a fairly draconian setting and your application will probably break if it has been programmed against MySQL's lax defaults regime.

Answer 2

Not even a check constraint prevents MySQL from inserting the implicit default for an integer:

create table YourTable (
  id int primary key
, must_fill tinyint not null
, constraint must_fill_constraint check (must_fill <> 0)
);
insert into YourTable (id) values (1);
select must_fill from YourTable t1;

This prints 0, even though there is a constraint forbidding 0. You can change this behavior my enabling strict mode.

Answer 3

MySQL will default an integer column like your must_fill to zero if you do not specify "strict mode". Try adding a

SET SESSION sql_mode=STRICT_ALL_TABLES

before the INSERT, and your example INSERT should now produce an error because no value was supplied for the must_fill column.

You can set strict mode as a command line option when you launch the MySQL daemon. Details are in the MySQL mode documentation page I mention below.

MySQL modes http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html