Null strings getting added to primary key field

Question

I have a primary key column year in my form.

Whenever the user creates a new form he should populate this field from the drop down.

But if the user makes a mistake and does not select anything from the drop down a null string gets appended "". And when post operation is selected the record gets created in the database table.

My question:
When user posts to the database after making his selection: the primary key column ends up accepting the null string ""

Shouldn't this throw an error as the primary key field should never have a null/empty string ?

I checked the table creation script and the column is set to Not Null and no default value is assigned.

I am using SQL Server 2008.

Try validating your form before it hits the database, or you could put a trigger or check constraint to disallow the empty string. — lc.
You need to be careful with your terminology: NULL and a empty string are NOT the same. An empty string "" should not be called null string - that's just asking for confusion and trouble ..... — marc_s
@marc_s Unless you're working with Oracle, then they are the same. — NullUserException
What is the datatype of year anyway? I would expect that to be some type of integer rather than a string. — Martin Smith
@NullUserException: the OP clearly mentioned he's using SQL Server - that's the target product I'm referring to. — marc_s

Martin Smith Martin Smith · Accepted Answer · 2012-11-14T16:29:52

NULL and empty string are not synonymous in SQL Server as they are for Oracle. A PK can have an empty string.

Of course a PK must be unique so there can be only one such empty string unless it is a composite PK and the other columns have different values.

You should validate this before the insert attempt but could add a check constraint as a last line of defence.

Null strings getting added to primary key field

1 Answers