I have a column that is my primary key...
it has PK and NN checked off in MySQL Workbench table editor...
However software is able to write a record where the columns value is
''...
Which isn't NULL I guess.. but what do I do in the table construction
SQL so that blank isn't valid either?

SpreadTooThin wrote:
pain of ..something or other.:-)

On Mar 15, 3:36*pm, The Natural Philosopher <t... (AT) invalid (DOT) invalid>
wrote:
But its a VARCHAR....

SpreadTooThin wrote:
Well spank it and tell it to change itself into an INT. Or add an auto
inc int as the real primary key.
..

'' is a perfectly valid varchar value.

If you want a *unique* tag for a table record, make it an auto inc not
null int primay key index field and be done with it.

You can always add an index on another field if needs be.

On Tue, 15 Mar 2011 14:34:20 -0700 (PDT), SpreadTooThin wrote:
Insert a dummy record that has the value you don't want to be valid.
Then the database will complain about non-unique key if something tries
to do it again.

--
The true sysadmin does not adjust his behaviour to fit the machine. He
adjusts the machine until it behaves properly. With a hammer, if necessary.
- Brian in the Monastery

On 2011-03-15 22:34, SpreadTooThin wrote:
You normally handle this kind of restriction with a check constraint,
something like:

check ( mycol <> '' )

but since this is not supported by mysql you will have to emulate it
with triggers. I know there is an article somewhere, but I can't find
now so I'll do a sketch of the content.

create table T (
c varchar(25) not null primary key
) engine = innodb;

create table error_msg (
msg varchar(255) not null primary key
) engine = memory;

delimiter @
create trigger chk_str_gt_zero
before insert on T
for each row begin
if (new.c = '') then
-- force primary key violation
insert into error_msg (msg) values ('String can''t be empty');
insert into error_msg (msg) values ('String can''t be empty');
end if;
end @

delimiter ;
insert into T (c) values ('');

ERROR 1062 (23000): Duplicate entry 'String can't be empty' for key
'PRIMARY'

You will need a similar trigger for update


/Lennart

On 3/15/2011 5:34 PM, SpreadTooThin wrote:
You have a database question - try a newsgroup for your database (in
this case comp.databases.mysql). You'll get much better answers.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Tue, 15 Mar 2011 19:54:05 -0400, Jerry Stuckle wrote:
*psst* Hey, Jerry? Am I missing something here?

--
"This place is evil! We need weapons! Crossbows! Rocket Launchers!
Rent-a-zilla!"
-- L33t Master Largo www.megatokyo.com

In article <9ecd5226-94cf-477d-b21c-9aa89fbd7c11 (AT) w7g2000pre (DOT) googlegroups.com>, SpreadTooThin <bjobrien62 (AT) gmail (DOT) com> wrote:
That is correct. It's not.

doesn't support. There are workarounds, though; this post describes
accomplishing pretty much the same thing with a TRIGGER:
http://forums.mysql.com/read.php?136,152474,240479

On 3/15/2011 7:54 PM, Jerry Stuckle wrote:
Sorry - I was just in the PHP newsgroup before this and got my
newsgroups screwed up.

An empty string is a valid value - it is not the same as NULL.

MySQL doesn't have a check constraint; as mentioned earlier, the easiest
way is to just insert a dummy row with an empty string in the field.

If this won't work (and it won't in many instances), you could put in a
trigger (you would need both INSERT and UPDATE triggers) to test the field.

Unfortunately, MySQL also doesn't have a way to cancel an insert/update
from a trigger, but in your case this could easily be handled by
changing the value to NULL. Then the NOT NULL constraint would take
effect and reject the INSERT or UPDATE.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================