Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2002
    Posts
    58

    Angry Unanswered: DEFAULT constraint not working

    I have a 32 column table. Every column is NOT NULL, and all but the first have DEFAULT constraints. In particular, the 21st column has such a constraint. I also have a stored proc which truncates the table and then loads it with a SELECT from a view, like this

    TRUNCATE TABLE NDRS_Call_Data_Table

    INSERT INTO NDRS_Call_Data_Table(<column-list>)
    SELECT <column-list> FROM NDRS_Call_Data_View

    The view is a complex join of several other tables. When I run the proc, I get the error:

    "Cannot insert the value NULL into column 'CC_Time', table 'Tomcat_prod.dbo.NDRS_Call_Data_Table'; column does not allow nulls. INSERT fails."

    BUT, if I simply do this from Query Analyzer......

    INSERT INTO NDRS_Calls_Data_Table(data_indicator)
    VALUES('Z')

    it works. The default constraint on the CC_Time column works correctly and supplies the default value of '0 ' as it should. (the column is a CHAR(2) column, despite the name suggesting it is datetime)

    Recompiling the view and the stored procedure does not help. Anybody else seen this? Is this a known bug?

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: DEFAULT constraint not working

    You constraint works fine. I believe the null value comes from the view. As you said, the view was created from a complex join. Somewhere in the resultset it returns a null value in the view. I would run the query and check the resultset closely.

  3. #3
    Join Date
    Jul 2002
    Posts
    58

    Re: DEFAULT constraint not working

    Got it. Misunderstanding on the operation of DEFAULT constraints. They don't override explicit nulls if you specify the column in the insert. They only provide values if you leave out the column in the column list. Thanks

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Hi,

    Just for my understanding.

    Is column "CC_Time" included in <column-list> in your insert statement or not ?

    If not, do you have any "after insert" triggers on table "Call_Data_Table" ?

    CVM.

  5. #5
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Originally posted by cvandemaele
    Hi,

    Just for my understanding.

    Is column "CC_Time" included in <column-list> in your insert statement or not ?

    If not, do you have any "after insert" triggers on table "Call_Data_Table" ?

    CVM.
    Well, forget about this last post. Just getting a cup of coffee and WHAM problem solved.

  6. #6
    Join Date
    Jul 2002
    Posts
    58

    Re: DEFAULT constraint not working

    Further research shows that this is ANSI standard behavior. A NOT NULL column with a DEFAULT will not use the default if the column is specified in the insert's column list and NULL is provided. Besides being counter-intuitive, frankly this strikes me as dumb. It really gets in the way of using the INSERT .... SELECT syntax

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    To solve the problems of nulls in your view, you can use isnull(a, b) where a is the selected column and b is the default value for that column.

    example:


    select isnull (title, 'No title') as title, au_lname
    from authors a left join titleauthor ta on a.au_id = ta.au_id left join titles t on t.title_id = ta.title_id

  8. #8
    Join Date
    Jul 2002
    Posts
    58
    True.

    Undortunately, when using INSERT ... SELECT you can't say
    SELECT ...., ISNULL(column, DEFAULT), ...

    the way you can use the DEFAULT keyword like this....

    INSERT
    VALUES (x,y,z,DEFAULT,p,q,r)


    You have to know what the default value is, and explicitly put it in as the 2nd parameter of the ISNULL function. So if you ever change the default constraint, you'll have to go back and change all the canned queries, too.

  9. #9
    Join Date
    Sep 2003
    Posts
    522
    you can declare a variable for each column that you want default to be inserted in place of null, and then initialize them according to each variable data type:

    Code:
    declare @value int
    select @value = cast(replace(replace(m.text, ')', ''), '(', '') as int)
       from syscomments m,syscolumns c,sysobjects o 
       where c.id=object_id('dbo.your_table') 
       and c.name=('your_column_name') 
       and o.type='d' 
       and m.id=c.cdefault 
       and m.id=o.id

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •