List:General Discussion« Previous MessageNext Message »
From:SGreen Date:June 16 2005 4:47pm
Subject:Re: Primary Key Question
View as plain text  
Hendro Suryawan <elcom@stripped> 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 '',
> PRIMARY KEY  (`PO`,`BrgId`,`NoSP`),
> KEY `BrgIn2SPBrg` (`NoSP`,`BrgId`,`Qty`),
> KEY `BrgId` (`BrgId`)

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

Primary Key QuestionHendro Suryawan16 Jun
  • Re: Primary Key QuestionSGreen16 Jun
  • Re: Primary Key QuestionPeter Brawley16 Jun