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]
