Notnull and empty string in My SQL

Question

Hy I have problem. I wanna to create table with some atributes, and some of them shoud be specified as NOT NULL.. And here comes the problem. When I insert some data into table, and when I insert '' (empty single string) it input data into table, but I dont want this... How to restrict inserting data from inputing single string or inputing nothing..

here are my table

CREATE TABLE tbl_Film (

ID INT UNSIGNED PRIMARY KEY,

Naziv VARCHAR(50) NOT NULL,

Zanr VARCHAR(50) NOT NULL,

Opis VARCHAR(150) NULL,

Kolicina INT UNSIGNED NOT NULL

);

INSERT INTO tbl_Film VALUES (1,'','Animirani','Mala ribica',2)

This input blank data into Naziv, and I don't want that.. I need to restrict that..

http://prntscr.com/21gfgd

score 0 · Answer 1 · answered Nov 02 '13 at 18:36

0

I dont know if this is possible in SQL, but why dont you exchange the '' in your application into the String NULL?

answered Nov 02 '13 at 18:36

user2948353

1
2

can you tel me how to do that, because I'm beginner :(. btw I'm using My SQL Workbench – Turbo Nov 02 '13 at 18:58

score 0 · Answer 2 · edited Apr 13 '17 at 12:42

In SQL, NULL is not the same as '' (with the exception of MS SQL via OleDB AFAIR, in which '' should be stored as NULL).

NULL values represent missing unknown data.

See http://www.w3schools.com/sql/sql_null_values.asp

In regular SQL, you should use a CHECK constraint, e.g.

CREATE TABLE tbl_Film (
ID INT UNSIGNED PRIMARY KEY,
Naziv VARCHAR(50) NOT NULL,
Zanr VARCHAR(50) NOT NULL,
Opis VARCHAR(150) NULL,
Kolicina INT UNSIGNED NOT NULL,
CHECK (Naziv <> '')
);

Sadly, this CHECK constraint is NOT implemented by MySQL:

The CHECK clause is parsed but ignored by all storage engines. See http://dev.mysql.com/doc/refman/5.7/en/alter-table.html

So the only solution I see, at DB level, is to write a P/SQL trigger...

Notnull and empty string in My SQL

2 Answers2