Nulls being allowed when they shouldnt be?

[grist2mill]

I have a simple table, for some reason, certain columns seem to accept
Nulls even though they shouldn't, for example the I can set the 'Name'
field to Null using my web application or directly in Enterprise
Manager. field How do I prevent this? However the 'RecCreated' doess
not permit nulls.


CREATE TABLE [dbo].[Group] (
[GroupID] [int] IDENTITY (1000, 1) NOT NULL ,
[Name] [nvarchar] (50) NOT NULL ,
[Description] [nvarchar] (750) NULL ,
[RecCreated] [datetime] NOT NULL ,
[RecUpdated] [datetime] NOT NULL ,
[RecCreatedBy] [int] NOT NULL ,
[RecUpdatedBy] [int] NOT NULL ,
[RecActive] [int] NOT NULL
) ON [PRIMARY]
GO
thanks for any help you can give on this

[Muhd]

Re: Nulls being allowed when they shouldnt be?

If your adding data to varchar field using EM are actually entering an empty
string? and most likely this is what your web application is doing as well.

1) You really shouldn't be using EM to enter data into your tables, or
rather you shouldn't be relying on it as a primary means to enter data.
It's great for testing and the like but it should be limitted to that.
2) It sounds like your web application needs some additional middle tier
logic to make sure that empty strings aren't passed into the database.

Hopefully these ideas help,
Muhd.

"grist2mill " <grist2mill@exc ite.com> wrote in message
news:46e240e.04 10080831.b3bd7d 8@posting.googl e.com...[color=blue]
>I have a simple table, for some reason, certain columns seem to accept
> Nulls even though they shouldn't, for example the I can set the 'Name'
> field to Null using my web application or directly in Enterprise
> Manager. field How do I prevent this? However the 'RecCreated' doess
> not permit nulls.
>
>
> CREATE TABLE [dbo].[Group] (
> [GroupID] [int] IDENTITY (1000, 1) NOT NULL ,
> [Name] [nvarchar] (50) NOT NULL ,
> [Description] [nvarchar] (750) NULL ,
> [RecCreated] [datetime] NOT NULL ,
> [RecUpdated] [datetime] NOT NULL ,
> [RecCreatedBy] [int] NOT NULL ,
> [RecUpdatedBy] [int] NOT NULL ,
> [RecActive] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> thanks for any help you can give on this[/color]

[David Portas]

Re: Nulls being allowed when they shouldnt be?

Can you post some code that will reproduce the problem. I assume you are
aware that NULL is different to the empty string? How did you check that
NULLs were present in the table? Can you actually run the following queries
in Query Analzer and see what they return:

SELECT [name]
FROM dbo.[Group]
WHERE [name] IS NULL

SELECT COLUMNPROPERTY( OBJECT_ID('dbo.[Group]'),'name','Allo wsNull')

You can't always believe what you see in Enterprise Manager because the
display isn't always refreshed when you would expect it to be. Also, I
wouldn't trust EM as a method for entering data into tables.

P.S. "GROUP" is a reserved word. It's not a good idea to use reserved words
for table names. Anyway "Group" is too meaningless to make a good table
name. Group of what?

--
David Portas
SQL Server MVP
--

[Simon Hayes]

Re: Nulls being allowed when they shouldnt be?


"grist2mill " <grist2mill@exc ite.com> wrote in message
news:46e240e.04 10080831.b3bd7d 8@posting.googl e.com...[color=blue]
>I have a simple table, for some reason, certain columns seem to accept
> Nulls even though they shouldn't, for example the I can set the 'Name'
> field to Null using my web application or directly in Enterprise
> Manager. field How do I prevent this? However the 'RecCreated' doess
> not permit nulls.
>
>
> CREATE TABLE [dbo].[Group] (
> [GroupID] [int] IDENTITY (1000, 1) NOT NULL ,
> [Name] [nvarchar] (50) NOT NULL ,
> [Description] [nvarchar] (750) NULL ,
> [RecCreated] [datetime] NOT NULL ,
> [RecUpdated] [datetime] NOT NULL ,
> [RecCreatedBy] [int] NOT NULL ,
> [RecUpdatedBy] [int] NOT NULL ,
> [RecActive] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> thanks for any help you can give on this[/color]

In Enterprise Manager, if you type NULL in the table data screen, it will
put the literal string 'NULL' into the table, which is not the same as a
real NULL; you need Ctrl+0 to get a real NULL. I suspect your application is
doing the same thing, or perhaps displaying an empty string as NULL, which
it isn't.

EM isn't a good application for modifying data, because it's not always
completely clear what it's doing. You should use Query Analyzer instead,
because then you can control every detail of the SQL you execute, and you
can also save scripts for future use more easily.

Simon

[grist2mill]

Re: Nulls being allowed when they shouldnt be?

Thanks to all for your help and tips.

So if I understand things correctly, (having run David's suggested
queries),

if you set a column to be nvarchar to not accept nulls, the column
will still accept empty strings. So to make a field mandatory, I must
enforce this at the application level (which is not such good
practice, because if another application should access the table I
won't know if they correctly enforce that certain fields should be
mandatory).

Have I understod things correctly (or should a be using a different
data type from nvarchar)?

[grist2mill]

Re: Nulls being allowed when they shouldnt be?

Thanks to all for your help and tips.

So if I understand things correctly, (having run David's suggested
queries),

if you set a column to be nvarchar to not accept nulls, the column
will still accept empty strings. So to make a field mandatory, I must
enforce this at the application level (which is not such good
practice, because if another application should access the table I
won't know if they correctly enforce that certain fields should be
mandatory).

Have I understod things correctly (or should a be using a different
data type from nvarchar)?

[Hugo Kornelis]

Re: Nulls being allowed when they shouldnt be?

On 11 Oct 2004 03:05:00 -0700, grist2mill wrote:
[color=blue]
>if you set a column to be nvarchar to not accept nulls, the column
>will still accept empty strings. So to make a field mandatory, I must
>enforce this at the application level[/color]

Hi grist2mill,

You can use a CHECK constraint:

CREATE TABLE xxxx (....,
NonEmptyCol nvarchar(78) NOT NULL
CHECK (NonEmptyCol <> ''),
....)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[grist2mill]

Re: Nulls being allowed when they shouldnt be?

Thanks Hugo,
thats the problem solved
regards
GM

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<ouokm0lou i5brq4hi1f7qnuq bdd4fm3kl0@4ax. com>...[color=blue]
> On 11 Oct 2004 03:05:00 -0700, grist2mill wrote:
>[color=green]
> >if you set a column to be nvarchar to not accept nulls, the column
> >will still accept empty strings. So to make a field mandatory, I must
> >enforce this at the application level[/color]
>
> Hi grist2mill,
>
> You can use a CHECK constraint:
>
> CREATE TABLE xxxx (....,
> NonEmptyCol nvarchar(78) NOT NULL
> CHECK (NonEmptyCol <> ''),
> ....)
>
> Best, Hugo[/color]