MS SQL中的默认约束问题 [英] Problem in Default constraint in MS SQL

问题描述

在使用默认约束时，我面临两个问题.

I am facing two problem while working with Default constraint.

我的表格托运人"包括3列:发货人ID(身份证明)"， "shippersName"，"ShippedDate"，例如:

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

Shippers ID (identity)

ShippersName Default = 'ashok' NOT NULL

ShippedDate Default = GetDate() NOT NULL

现在，我为"Shippersname"提供了默认约束，默认值为ashok，"shippeddate"为getdate()注意，所有列都不为空

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:

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

或

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

在第一种情况下，因为我没有提供出厂日期，所以它采用了我的默认值getdate()，而在第二种情况下， 还没有提供shippersName，因此它采用的是我的默认值< ashok>

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>

我的问题是当我使用

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

然后在上述情况下，我也没有提供任何值，因此它应该为我的默认值，但 发生的情况是发运日期采用的是SQL Server 1900-01-01 00:00:00.000的默认值，并且 shippersName为空白.我想问两个问题甚至不是NULL

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

1. 当我将''''用于varchar时.它具有一定的价值吗?不等于null
2. 为什么当我使用''''作为dattime时，它并没有采用我的默认日期，而是采用了SQL默认日期

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

推荐答案

插入托运人(shippersName，发货日期)值(''''，'''')

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

 

这完全符合您的要求.您不是在使用默认值，而是在提供值，然后系统会尝试使用这些值.您为什么还要使用此SQL?我没有任何理由.

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.

这些值从何而来?您如何构建SQL，以及如何使其安全?

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

创建表时指定的默认值是在插入时不传递任何值时设置的.

因此，您不能使此程序化，以确保您将值传递为NULL，它将自动检测到它.

唯一可能的解决方法是保留默认值，并为该表定义一个触发器.在触发器内操纵值(例如CASE WHEN INSERTED.shippersName IS NULL THEN ''ashok'' ELSE INSERTED.shippersName END)

最好避免这种情况.如果遇到这种情况，我将创建一个存储过程以将其(而不是通常插入的一批语句)插入数据库中，并在其中设置逻辑.

做适合自己的事情.
干杯.

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.

这应该有效:

INSERT shippers DEFAULT VALUES

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

这篇关于MS SQL中的默认约束问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！