October 7, 2009 at 10:09 am
Hey Guys,
I am able to insert data which is like null into a not null column. I create a table with not null column
CREATE TABLE TEST (TEST2 VARCHAR(MAX) NOT NULL);
Then when I try to insert nothing into test like the query below
insert INto TEST values ('')
It takes it, but when you go back and open the table you see nothing, length of the data is zero and you see blank space.
I dont understand the whole point of not null column and also if you have to avoid this, we have to create a check contraint on every column in a table which would be taking a lot of resources.
Any ideas on how to overcome this?? it will be of great help
Thanks,
Nikhil
October 7, 2009 at 11:26 am
Okay, sorry but an empty string ('') is not the same as NULL.
October 7, 2009 at 12:09 pm
Just to add to Lynns reply :
Start with reading these eluminating articles:
- http://www.sqlservercentral.com/articles/Basic+Querying/understandingthedifferencebetweenisnull/871/
- http://www.sqlservercentral.com/articles/Advanced+Querying/2829/
- http://www.sqlservercentral.com/articles/Advanced+Querying/gotchasqlaggregatefunctionsandnull/1947/
- http://www.sqlservercentral.com/articles/Advanced+Querying/fourrulesfornulls/1915/
You have to understand NULL means "unkonwn" !
You are inserting an empty string so we know your columns length will be zeroe, so containing no data. Hence the empty column you get to see.
You cannot enter NULL in a non nullable column !
Declare @wrk table (idnr int identity(1,1) not null primary key ,
mycol varchar(128) NULL,
anothercol varchar(128) not null) ;
Insert into @wrk (mycol, anothercol) values ( NULL, 'just a value') ;
Insert into @wrk (mycol, anothercol) values ( 'known value', 'just a value2') ;
select *
from @wrk ;
--this will give an error !!
Insert into @wrk (mycol, anothercol) values ( 'known value', NULL) ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 7, 2009 at 12:31 pm
thanks for the replies,
I did not mean was inserting null,,, but if you see nothing in the column , is it not similar to inserting unknown value. I dont know why they would allow that entry as for unknown values you use Null. So, I thought you can never insert a zero length value as data into any table.
Also, my question would be , is there any way of not allowing this in the development or production environment without using check contraints on the table as they consume a lot of resources.
Thanks,
Nick
October 7, 2009 at 12:36 pm
An empty string is a known value, it is a zero length string. If you don't want zero length strings inserted, then you either need to filter out those records on the front end or use contraints on the table.
October 7, 2009 at 12:40 pm
toparsi (10/7/2009)
thanks for the replies,I did not mean was inserting null,,, but if you see nothing in the column , is it not similar to inserting unknown value. I dont know why they would allow that entry as for unknown values you use Null. So, I thought you can never insert a zero length value as data into any table.
Also, my question would be , is there any way of not allowing this in the development or production environment without using check contraints on the table as they consume a lot of resources.
Thanks,
Nick
Inserting a zero-length string is somewhat similar to inserting NULL (in layman's terms at least). But the others are right, it isn't the same thing. "NOT NULL" requires a value that is not null, which '' or ' ' are definitely not the same thing as NULL. NULL's are tricky because they can never be compared with ANYTHING. 1 + NULL = NULL, concatenate NULL to anything, and it's NULL (other than if you set SQL Server session-specific settings to handle this).
Please cite your reference(s) backing up the contention that Check Constraints use "a lot of resources". If you're thinking of Triggers then yes, more resources can be used depending. But Check Constraints, Foreign Keys (and other DRI) are at the heart of your data integrity.
MJM
October 7, 2009 at 12:42 pm
toparsi (10/7/2009)
Also, my question would be , is there any way of not allowing this in the development or production environment without using check contraints on the table as they consume a lot of resources.Thanks,
Nick
If you'd use a stored procedure to insert the data you could check for zero length before the insert statement (among other advantages like improved security, variety of logging mechanism a.s.o.).
October 7, 2009 at 12:53 pm
thanks for the replies Guys,
CREATE TABLE TEST (TEST2 VARCHAR(MAX) NOT NULL);
go
ALTER TABLE [TEST]
with check add constraint [ck_test2]
CHECK (len(ltrim(rtrim(isnull([test2],''))))) > 0 );
go
This is the check constraint am using right now, and inserts really are slow. I need to do something which will block the developers from entering zero length data. So, is this the best way of doing it?
Even if I write a Stored Proc, I will hav to do somthing similar to this.
Thanks,
Nick
October 7, 2009 at 1:05 pm
Like Lutz said, here it is in code:
DECLARE
@Test2Col VARCHAR(MAX)
SET @Test2Col = NULL
IF LEN(LTRIM(RTRIM(ISNULL(@Test2Col, '')))) <= 0
BEGIN
RAISERROR('Invalid value for this column.', 16, 1)
END
ELSE
BEGIN
--Do INSERT
SELECT 'INSERTING ''' + @Test2Col + ''' into table.'
END
When you say the INSERT is running slow, could you elaborate a little? Is VARCHAR(MAX) absolutely necessary? Comparing a VARCHAR(MAX) to something will be pretty expensive compared to looking at a VARCHAR(512) or similar.
MJM
October 7, 2009 at 1:25 pm
I can change that to varchar(512) or something, but I was wondering if there is a configuration setting or something in sql server which will not allow that to happen. I dont want to create a check contraint on all the columns in a table and on all the tables in a database. I always thought if you select not null, you need to enter something meaningful or if not some data into that column, but never thought it would allow blank piece of data which according to me is meaningless data.
I would like to know if we can do some setting in sql server which would do it for me.
thanks,
Nick
October 7, 2009 at 1:46 pm
No, there is not a database-wide or server wide setting for that. That's like asking for
ALTER DATABASE mydb SET REJECT_DATA_I_THINK_IS_MEANINGLESS ON
😉
I wasn't thinking earlier when I looked at your constraint. You should be able to use the following (see the test cases):
CREATE TABLE TEST (TEST2 VARCHAR(MAX) NOT NULL);
GO
ALTER TABLE [TEST]
with check add constraint [ck_test2]
CHECK (LEN([test2]) > 0 );
go
INSERT INTO dbo.TEST(TEST2) VALUES(NULL)
INSERT INTO dbo.TEST(TEST2) VALUES('')
INSERT INTO dbo.TEST(TEST2) VALUES(' ')
INSERT INTO dbo.TEST(TEST2) VALUES('Not meaningless to me.')
GO
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'TEST2', table 'MARINO.dbo.TEST'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the CHECK constraint "ck_test2". The conflict occurred in database "MARINO", table "dbo.TEST", column 'TEST2'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint "ck_test2". The conflict occurred in database "MARINO", table "dbo.TEST", column 'TEST2'.
The statement has been terminated.
(1 row(s) affected)
No need for all of the (L/R)TRIM/ISNULL functions. Either the length is not null or the LEN/DATALENGTH is greater than 0. NULL is always NULL, so it can NEVER be > 0.
MJM
October 7, 2009 at 2:08 pm
thanks for that.
October 7, 2009 at 3:59 pm
It's easier than that. To reject things that are either blank or null in VARCHAR columns, you simply need to check for anything greater than a blank...
WHERE somecolumn > ' '
... and you can use the same thing (sans the WHERE) as a constraint that will reject NULL, Empty Strings, and Blank strings. AND, it doesn't matter how many spaces there are.
Using Mark's fine code as an example...
CREATE TABLE TEST (TEST2 VARCHAR(MAX) NOT NULL);
GO
ALTER TABLE [TEST]
WITH CHECK ADD CONSTRAINT [ck_test2]
CHECK (Test2 > ' ');
go
INSERT INTO dbo.TEST(TEST2) VALUES(NULL)
INSERT INTO dbo.TEST(TEST2) VALUES('')
INSERT INTO dbo.TEST(TEST2) VALUES(' ')
INSERT INTO dbo.TEST(TEST2) VALUES('Not meaningless to me.')
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2009 at 7:34 am
thanks jeff
October 8, 2009 at 8:14 am
Jeff Moden (10/7/2009)
It's easier than that. To reject things that are either blank or null in VARCHAR columns, you simply need to check for anything greater than a blank...WHERE somecolumn > ' '
Hey thanks Jeff! I didn't know that expression (> ' ') would behave in that fashion. Good to know 😉
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply