:(, oh how I wanted it to be true in MySQL 4.1, maybe it's a bug?.. I
don't know realy...
Here is Copy From Console:
mysql> create table `temp` (
-> col1 int(9) NOT NULL,
-> col2 date NOT NULL,
-> col3 Varchar(30) NOT NULL,
-> col4 Varchar(30));
Query OK, 0 rows affected (0.02 sec)
mysql> desc `temp`;
+-------+-------------+-------------------+------+-----+------------+---
----+
| Field | Type | Collation | Null | Key | Default |
Extra |
+-------+-------------+-------------------+------+-----+------------+---
----+
| col1 | int(9) | binary | | | 0 |
|
| col2 | date | latin1_swedish_ci | | | 0000-00-00 |
|
| col3 | varchar(30) | latin1_swedish_ci | | | |
|
| col4 | varchar(30) | latin1_swedish_ci | YES | | NULL |
|
+-------+-------------+-------------------+------+-----+------------+---
----+
4 rows in set (0.01 sec)
As you see, default NULL is only for col3 - Nullable column :(
P.S. there is no matter what type of table you create (MyISAM, InnoDB,
etc.).
So, Cal Evans, maybe you can try above create statement and email MySQL
output?
-----Original Message-----
From: Cal Evans [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 07, 2003 9:04 PM
To: Arūnas Milašauskas
Cc: Fortuno, Adam; [EMAIL PROTECTED]
Subject: Re: FW: MySQL not null vs MSAccess required
I hate to disagree with you but in the 30+ databases I've created in
MySQL, not defining a default value leaves the default value as NULL.
Setting that field to NOT NULL means that I have to enter something or
the record won't commit.
=C=
p.s. I use the 3.xx series, maybe this behavior has changed in 4 but I
hope not.
* Cal Evans
* http://www.eicc.com
* We take care of your IT,
* So you can take care of your business.
*
* I think inside the sphere.
Arūnas Milašauskas wrote:
> No, no, no ... Dear Friends, defining column as NOT NULL and do not
> specifying default value does not means that there is no default value
> at all. MySQL defines default value for that column of his on decision
> (for Date it's '0000-00-00', for text column's it is '', for number
> column's it is '0' and so on...)
>
> P.S. I use MySQL 4.1alpha, but I am true it is on other versions
> too.
>
> Regards,
> Arunas
>
> -----Original Message-----
> From: Cal Evans [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 07, 2003 8:51 PM
> To: Arūnas Milašauskas
> Cc: Fortuno, Adam; [EMAIL PROTECTED]
> Subject: Re: FW: MySQL not null vs MSAccess required
>
>
> Of course the OT NULL is not firing, you put DEFAULT ''. This defeats
> the purpose of NOT NULL since every new record written has a default
> value that IS NOT NULL. (Whether the user entered the field or not.
>
> remove the DEFAULT form the table definition and leave the NOT NULL.
> This will prevent ANY program (even Access) from writing to the table
> without specifying a value.
>
> Now if you want more than that you'll have to write it into your FE.
> (i.e.not null and not '')
>
> HTH,
> =C=
> * Cal Evans
> * http://www.eicc.com
> * We take care of your IT,
> * So you can take care of your business.
> *
> * I think inside the sphere.
>
>
> Arūnas Milašauskas wrote:
>
>>Yes it's true, when someone enter's NULL value, but when someone does
>>not even touch that field on the form? :(
>>
>> In other DBMS it's like a rule to make all possible chechking on
>>DB for any unforeseen accident... So I'm hopefully waiting for MySQL
>
> 5xx
>
>>relise to realize this...
>> As for now I see that there is no other way as to make check
>>constraint's validation (even for Required columns :( ) on forms on
>>client side :(
>>
>>-----Original Message-----
>>From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
>>Sent: Tuesday, October 07, 2003 8:33 PM
>>To: Arūnas Milašauskas
>>Subject: RE: MySQL not null vs MSAccess required
>>
>>
>>Arunas,
>>
>>Then take change the table's definition to make the CustName field NOT
>>NULL
>>and don't include a default value. When someone attempts to change the
>>CustName field to NULL, you'll get a runtime error.
>>
>>Regards,
>>Adam
>>
>>-----Original Message-----
>>From: Arūnas Milašauskas
>>Sent: Tuesday, October 07, 2003 8:33 PM
>>To: '[EMAIL PROTECTED]'
>>Subject: FW: MySQL not null vs MSAccess required
>>
>>
>>
>>MSAccess as frontend :) sorry, you're right.
>>
>> So, I defined column without default value, but nothing
>>changed... I still can enter empty values into field :(
>>
>> Now, actualy I want MySQL to rise run-time error, because there
>>are many forms in access and DB structure is changing ... I mean it is
>>better (I think) to capture MySQL run-time errors (I have library
>
> writen
>
>>for this) than edit every form trigger's after changing NOT NULL
>
> columns
>
>>in DB especially if there is more than one form allowing to enter data
>>into such tables...
>>
>> Thanks, for thought about form self validation ;)
>>
>>Regards,
>> Arunas
>>
>>
>>
>>
>>-----Original Message-----
>>From: Fortuno, Adam [mailto:[EMAIL PROTECTED]
>>Sent: Tuesday, October 07, 2003 8:09 PM
>>To: Arūnas Milašauskas
>>Cc: [EMAIL PROTECTED]
>>Subject: RE: MySQL not null vs MSAccess required
>>
>>
>>Arunas,
>>
>>Actually, I think you mean MySQL is the backend for MS Access.
>>
>>Don't assume columns that are NOT NULL must have a default value. You
>>are
>>welcome to define columns as NOT NULL without a default value. Doing
>>this
>>will force the client (the Access form's) to require a value before
>>inserting the record.
>>
>>CREATE TABLE customers (
>> ID INT(9) NOT NULL,
>> CustName VARCHAR(30) NOT NULL,
>> ...
>>) Type = MyISAM;
>>
>>Since you're using an MS Access front-end to allow users to enter
>
> data.
>
>>Put
>>logic in your form to require the users to populate a value for the
>>customer
>>name. Something like this:
>>
>>** Assuming you have a form with customer name field named
>
> "tbCustName".
>
>>Private Sub Form_BeforeUpdate()
>> Dim strMsg as String
>>
>>On Error Resume Next
>>
>> If (Not ValidateSelf()) Then
>> '** Notify the user that the form failed validation.
>> Let strMsg = "Please ensure all required fields are populated."
>> Call MsgBox(strMsg, vbOkOnly)
>>
>> '** Cancel's the save event.
>> Call DoCmd.CancelEvent
>> EndIf
>>End Sub
>>
>>Private Function ValidateSelf()
>> Dim blnIsValid As Boolean
>>
>> '** Determine if the field has a value.
>> If (Not Nz(Me.tbCustName.Value, "") = "") Then Let blnIsValid = True
>>
>> '** Return the results of the validation process.
>> Let ValidateSelf = blnIsValid
>>End Function
>>
>>Its been a while since I've used Access for soemthing like this. My
>
> VBA
>
>>might be a little rusty. The point is you want to enforce user
>>population of
>>the customer name field in the client. If you do it from MySQL, the
>
> odbc
>
>>driver will generate a runtime error, which Access will then need to
>>capture
>>- you should capture errors anyway.
>>
>>Regards,
>>Adam
>>
>>-----Original Message-----
>>From: Arunas Mila?auskas [mailto:[EMAIL PROTECTED]
>>Sent: Tuesday, October 07, 2003 12:23 PM
>>To: [EMAIL PROTECTED]
>>Subject: MySQL not null vs MSAccess required
>>
>>
>>Hello,
>> I'm Using MSAccess as backend for my MySQL database. Tables are
>>linked with MyODBC 3.51, so all data user enters through MSAccess
>
> forms.
>
>> I need that some fields (CustName) in MySQL table (CUSTOMERS)
>>would be required, it means, that user must fill a value into that
>
> field
>
>>(CustName) displayed on MSAccess form (Customers).
>>
>> MySQL table create statement is:
>> create table CUSTOMERS (ID `ID` int(9) NOT NULL,
>>`CustName` Varchar(30) NOT NULL DEFAULT '');
>> Default value in table declaration is requred as you know for
>>NOT NULL columns.
>>
>> The problem is:
>> When user enters a new record in MSAccess form and does
>>not even touch field "CustName", new record is created with empty
>
> string
>
>>as default value!!!!
>> If user enters for example "1" and then deletes the
>>value in MSAccess form, Access reports, that value must not be NULL.
>
> But
>
>>what can I do if user does not even go into that field and leave empty
>>field? User Must Enter A Value!!!! How to do this without writing many
>>code in every form with required (NOT NULL) fields?
>>
>>
>> Thanks for any help, It is needed very very much because for now
>>any user can not fill required fields!
>> It would be very very appreciated
>>
>>Regards, Arunas
>>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
