Hendro Suryawan <[EMAIL PROTECTED]> wrote on 06/16/2005 06:53:31 PM:
> Hi all,
> I have table with primary key on field PO,BrgId, NOSP but when i try
> insert several new reccord with field NOSP = '', mysql will accept the
> new reccord without complaint error.
> Is this normal behavior?
As long as the combination of the values {PO, BrgId, NoSP} does not yet
exist on the table, you should be able to add rows. Are you saying that,
for example, that the combination {'somePOvalue', 4, ''} already exists on
your table and it's allowing you to add a second row with the same
combination of values?
> My perception if i have primary key on the
> three field the three field must be not empty. I try to alter the field
> NOSP with syntax :
> Alter table BrgIn2 Change NOSP NOSP Varchar(20) NOT NULL
> but if i looked table definition mysql always add default '' in the
> definition. How to tell mysql not to add default '', i want to this
> field always not null or ''. I use mysql 4.1.11 on FC3 X86_64.
> Can anyone help? Thanks in advance.
> regards,
> Hendro
> Table Create Table
> ------ ----------------------------------------------------
> BrgIn2 CREATE TABLE `BrgIn2` (
> `PO` varchar(17) NOT NULL default '',
> `BrgId` int(4) NOT NULL default '0',
> `NoSP` varchar(20) NOT NULL default '',
<snip>
> PRIMARY KEY (`PO`,`BrgId`,`NoSP`),
> KEY `BrgIn2SPBrg` (`NoSP`,`BrgId`,`Qty`),
> KEY `BrgId` (`BrgId`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
The empty string ('') is not a NULL value. It represents a string that
contains no characters. A NULL value indicates the lack of information, a
state of non-existence.
For instance: Imagine you have a table, Person, and the table has fields
to hold a first name, a middle name, and a last name. If you know for a
fact that some person does not have a middle name, you would use a ''
(empty string) for the MiddleName value of that person. However, if you
don't have a middle name on a data entry form for a particular person (a
middle name may exist but you didn't get it as part of your data), you
would use a NULL value to indicate the absence of information.
I think what you would like to have is a CHECK constraint on the `NoSP`
field that requires that all new or updated values have a certain minimum
length (LENGTH(`NoSP`) > 0). However, MySQL does not yet support CHECK
constraints (see the TODO lists). Until it does, you will need to enforce
that particular restriction using your application code (any version) or
write that check into a TRIGGER (v5.0+).
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
