My bad, =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:
:(, 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]
