Problem in Default constraint in MS SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL-Server

Hi,

I am facing two problem while working with Default constraint.

My table "shippers" includes 3 columns: "Shippers ID(identity colmn) ", "shippersName","ShippedDate", like this:

SQL

Shippers ID (identity)

ShippersName Default = 'ashok' NOT NULL

ShippedDate Default = GetDate() NOT NULL

Now I have provided default constraints on "Shippersname" with Default value ashok and for "shippeddate" as getdate() Note that all the column are not null

This constraints is working fine when I am using a query like:

SQL

insert into shippers (shippersName) values ('nishu')

or

SQL

insert into shippers (shippeddate) values ('01/01/2008')

In first case since I have not provided shippeddate so it is taking my defualt value getdate() and in second I have not provided shippersName so it is taking my default value <ashok>

My problem is when I am using a query like

SQL

insert into shippers (shippersName, shippeddate) values ('','')

Then in above case also I am not providing any value so it should dake my default value but what is happening is shippeddate is taking default value of SQL server 1900-01-01 00:00:00.000 and shippersName is blank. It is not even NULL I want to ask 2 questions

When I am using '' for varchar. Is it working as some value? Isn't equal to null
Why when I am using '' as for dattime it is not taking my default date and rather taking SQL default date

Posted 26-Nov-09 2:11am

nishuthegreat

Updated 26-Nov-09 8:46am

Sean Ewington

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Answer 1 · 2009-11-26T02:42:00

insert into shippers (shippersName, shippeddate) values ('','')

This is doing exactly what you told it to. Instead of using the default values, YOU are providing values, which the system then tries to make sense of. Why would you bother using this SQL ? I don't see any reason for it whatsoever.

Where do the values come from ? How are you building the SQL, and how do you make it secure ?

Abhishek Sur · Answer 2 · 2009-11-26T09:40:00

Default value that you specify while creating the table is set when you dont pass any value while inserting.

So, you cant make this programmatic to ensure that you pass value as NULL and it will detect it automatically.

The only possible wayout from this, is leave the Default value, and define a Trigger for the table. Manipulate the value inside trigger (such as CASE WHEN INSERTED.shippersName IS NULL THEN 'ashok' ELSE INSERTED.shippersName END)

But it is better to avoid this. If I was in your situation, I would have created a Stored Procedure to insert this( rather a batch of statement that commonly inserted) in the database and would have set the logic inside that.

Do whatever suits you.
Cheers.

AspDotNetDev · Answer 3 · 2009-11-26T11:26:00

Solution 3

This should work:

SQL

INSERT shippers DEFAULT VALUES

Source: http://articles.techrepublic.com.com/5100-10878_11-5794899.html

Posted 26-Nov-09 11:26am

AspDotNetDev

Problem in Default constraint in MS SQL

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0