Insert statement with a NON-NULLABLE column

Question

I have a SQL Server script that I'm using to insert some data into a database. I won't upload the whole script here just for space/time savings sake, but I will include the important bits.

So here is the problem. I have a table that has a column for some loginhtml, this column is of a non-nullable type. I would like for this column to be left blank on this particular add so it can default back to the parent that I'm pointing it at. So here we have the declaration for this important portion:

declare @loginTitle varchar(250), @loginHtml varchar(max)

And here we have what it will be set to:

set @loginHtml = null

And here is the insert part that is inevitably going to fail:

insert dbo.ApplicationLogin(ApplicationID, Title, Html)
   select @appID, @loginTitle, @loginHtml

EDIT: How can I have this script "default" the loginhtml column to whatever the PARENT Application is? Is there some "IF" statement/clause that can be used to accomplish this?

If it's non-nullable, you have to insert something or give it a default. Can you use a blank instead of a null? — Dave C, Jul 15 '14 at 14:16
No, by giving it a blank, it will not default to the value in the parent application. — Skrubb, Jul 15 '14 at 14:17
Can you just define a default value of `(no value)` or something? Either that, or remove the `NOT NULL` restriction - you cannot have both (`NOT NULL` and inserting nothing ....) — marc_s, Jul 15 '14 at 14:17
You appear to be leaving out some pretty important bits... `I would like for this column to be left blank on this particular add so it can default back to the parent that I'm pointing it at.` So you want it blank, but you can't use blank. Update your question please. — Dave C, Jul 15 '14 at 14:19
What "parent"? The column/parameter names you're showing have no obvious indication that any of them involve a "parent" — Damien_The_Unbeliever, Jul 15 '14 at 14:44

score 0 · Answer 1 · answered Jul 15 '14 at 14:17

As long as the column in non null, then you can't insert a null value into it. You could try setting it to a blank string or other default value. You could aslo select the parent's html and insert that instead. There is no way to skip over inserting any column.

score 0 · Answer 2 · answered Jul 15 '14 at 14:18

0

As others have stated, if there is a Not Null constraint on the column, you are out of luck, but you could do

if @loginhtml is null 
    begin
       set @loginhtml = ''
    end

Or change

set @loginhtml = ''

to lookup whatever the default in the parent application is.

and you will write a blank instead of anything. You, obviously, run into whatever issues there will be with having no loginhtml, but if that's what you want that is what you want!

answered Jul 15 '14 at 14:18

franglais

928
2
15
39

Cant you use logic like this to make the loginhtml default to whatever the parent application is though? – Skrubb Jul 15 '14 at 14:39
@Skrubb SQL won't read up to application level, so it depends on where the default is set. If it's stored in a table of defaults, then you can do `set @loginhtml = (select default from foo where defaultname = 'loginhtml')` or whatever the structure is. Also if a default was set in this table then if you passed `NULL` it would default to the default (if that makes sense). My guess is that the default is not set in this table, however! – franglais Jul 15 '14 at 14:41

score 0 · Answer 3 · answered Jul 15 '14 at 14:20

0

You can adjust your script:

insert dbo.ApplicationLogin(ApplicationID, Title, Html)
select @appID, @loginTitle, ISNULL(@loginHtml, '')

Or adjust your table and add a default constraint:

ALTER TABLE ApplicationLogin ADD DEFAULT (('')) FOR [Html];

answered Jul 15 '14 at 14:20

Andrea Colleoni

5,919
3
30
49

score 0 · Accepted Answer · answered Jul 15 '14 at 15:08

I may not have mentioned this in the initial post, and I would like to apologize for that. But the way the database is set up, is applications can have parent applications, so setting the loginhtml to the parent application and "technically" skipping adding it to the new application can be done by doing this:

if(@loginHtml is not null)
begin
    insert dbo.ApplicationLogin(ApplicationID, Title, Html)
    select @appID, @loginTitle, @loginHtml
end

This runs successfully and makes the "@loginhtml" default to whatever the parent application has set for that value.

Insert statement with a NON-NULLABLE column

4 Answers4