[Fwd: Workbench - handling NULL values in the table designer]

Hi,

Never mind, I've sorted this - you have to type the word NULL into the 
field.

Thanks,

Andy

-------- Original Message --------
Subject: 	Workbench - handling NULL values in the table designer
Date: 	Tue, 04 Nov 2008 18:57:04 +0000
From: 	Andy Shellam <[email protected]>
To: 	MySQL GUI Tools <[email protected]>



Hi,

Is the table designer's "insert" tab (and insert editor) capable of 
inserting NULL values into a field?

I've got 2 tables, customers and users.  The users table contains all 
the logins for the system, but the login could be for a user or a staff 
member.  The record has either an "is_staff" flag set and their staff 
e-mail address filled in, or a customer ID assigned which is a foreign 
key onto the customers table (and a NULL staff e-mail address.)  If the 
user is a staff member, the customer_id field will be NULL.

In the Insert editor of the users table, if I leave the customer_id 
field blank, workbench still creates it in the INSERT query but leaves 
the value blank (not even '') at which MySQL throws a syntax error.  
Here are 2 queries generated by workbench for a customer (ID 1) and 
staff (ID 2) record:

INSERT INTO `users` (`id`, `user_id`, `password`, `customer_id`, 
`enabled`, `is_staff`, `staff_email`) VALUES (1, '[email protected]', 
'encrypted_password', 1, 'Yes', 'No', );
INSERT INTO `users` (`id`, `user_id`, `password`, `customer_id`, 
`enabled`, `is_staff`, `staff_email`) VALUES (2, 'user.name', 
'encrypted_password',  , 'Yes', 'Yes', '[email protected]');

Notice how in the first query, the "staff_email" (which is a NULLable 
field) is included in the query but has no value specified, and in the 
second, the customer_id field has no value (it should be NULL.)

Also in the editor, if I delete the contents of the customer_id field, 
it pre-fills in "0" - which will then also fail in MySQL because there 
is no customer record with ID 0.  This should be NULL.

Thanks,

Andy

-- 
MySQL GUI Tools Mailing List
For list archives: http://lists.mysql.com/gui-tools
To unsubscribe:    http://lists.mysql.com/[email protected]




-- 
MySQL GUI Tools Mailing List
For list archives: http://lists.mysql.com/gui-tools
To unsubscribe:    http://lists.mysql.com/[email protected]