How to Constraint No Empty Strings on an Nvarchar Column

How to constraint no empty strings on an NVARCHAR column

You could add a check constraint that ensures that the string isn't empty.

CREATE TABLE [dbo].[Foo](
    [bar] [nvarchar](50) NOT NULL
)

ALTER TABLE [dbo].[Foo] WITH CHECK 
ADD  CONSTRAINT [CK_Foo] CHECK  (([bar]<>N''))

Can I put a constraint on a sql server table to not allow empty string?

Yes -- just use CHECK constraints:

ALTER TABLE myTable
    ADD CONSTRAINT CK_table_colHasLength CHECK (col1 IS NULL OR col1 <> '')

Unable to pass empty string into non-null database field

I can reproduce your reported problem using the code below with SS2014, the OLEDB driver and
Seattle and the difference in behaviour when the table has been created with MAX as the column size and a specific number (4096 in my case). I thought I would post this is as an alternative
answer because it not only shows how to investigate this difference systematically
but also identifies why this difference arises (and hence how to avoid it in future).

Please refer to and execute the code below, as written, i.e. with the UseMAX define
active.

Turning on "Use Debug DCUs" in the the project options before executing the code, immediately
reveals that the described exception occurs in Data.Win.ADODB at line 4920

Recordset.Fields[TField(FModifiedFields[I]).FieldNo-1].Value := Data

of TCustomADODataSet.InternalPost and the Debug evaluation window reveals that
Data at this point is Null.

Next, notice that

update jdtest set NonNullFieldName = ''

executes in an SSMS2014 Query window without complaint (Command(s) completed successfully.), so it seems that the
fact that Data is Null at line 4920 is what is causing the problem and the next question is "Why?"

Well, the first thing to notice is that the form's caption is displaying ftMemo

Next, comment out the UseMAX define, recompile and execute. Result: No exception
snd notice that the form's caption is now displaying ftString.

And that's the reason: Using a specific number for the column size means that
the table metadata retrieved by the RTL causes the client-side Field to be created
as a TStringField, whose value you can set by a string assignment statement.

OTOH, when you specify MAX, the resulting client-side Field is of type ftMemo,
which is one of Delphi's BLOB types and when you assign
string values to an ftMemo field, you are at the mercy of code in Data.DB.Pas , which does all the reading (and writing) to the record buffer using a TBlobStream. The problem with that is that as far as I can see, after a lot of experiments and tracing through the code, the way a TMemoField uses a BlobStream fails to properly distinguish between updating the field contents to '' and setting the field's value to Null (as in System.Variants).

In short, whenever you try to set a TMemoField's value to an empty string, what actually happens is that the field's state is set to Null, and this is what causes the exception in the q. AFAICS, this is unavoidable, so no work-around is obvious, to me at any rate.

I have not investigated whether the choice between ftMemo and ftString is made by the Delphi RTL code or the MDAC(Ado) layer it sits upon: I would expect it is actually determined by the RecordSet TAdoQuery uses.

QED. Notice that this systematic approach to debugging has revealed the
problem & cause with very little effort and zero trial and error, which was
what I was trying to suggest in my comments on the q.

Another point is that this problem could be tracked down entirely without
resorting to server-side tools including the SMSS profiler. There wasn't any need to use the profiler to inspect what the client was sending to the server
because there was no reason to suppose that the error returned by the server
was incorrect. That confirms what I said about starting investigation at the client side.

Also, using a table created on the fly using IfDefed Sql enabled the problem effectively to be isolated in a single step by simple observation of two runs of the app.

Code

uses [...] TypInfo;
[...]
implementation[...]

const
   //  The following consts are to create the table and insert a single row
   //
   //  The difference between them is that scSqlSetUp1 specifies
   //  the size of the NonNullFieldName to 'MAX' whereas scSqlSetUp2 specifies a size of 4096

   scSqlSetUp1 =
  'CREATE TABLE [dbo].[JDTest]('#13#10
   + '  [ID] [int] NOT NULL primary key,'#13#10
   + '  [NonNullFieldName] VarChar(MAX) NOT NULL'#13#10
   + ') ON [PRIMARY]'#13#10
   + ';'#13#10
   + 'Insert JDTest (ID, [NonNullFieldName]) values (1, ''a'')'#13#10
   + ';'#13#10
   + 'SET ANSI_PADDING OFF'#13#10
   + ';';

   scSqlSetUp2 =
  'CREATE TABLE [dbo].[JDTest]('#13#10
   + '  [ID] [int] NOT NULL primary key,'#13#10
   + '  [NonNullFieldName] VarChar(4096) NOT NULL'#13#10
   + ') ON [PRIMARY]'#13#10
   + ';'#13#10
   + 'Insert JDTest (ID, [NonNullFieldName]) values (1, ''a'')'#13#10
   + ';'#13#10
   + 'SET ANSI_PADDING OFF'#13#10
   + ';';

   scSqlDropTable = 'drop table [dbo].[jdtest]';

procedure TForm1.Test1;
var
  AField : TField;
  S : String;
begin

//  Following creates the table.  The define determines the size of the NonNullFieldName

{$define UseMAX}
{$ifdef UseMAX}
  S := scSqlSetUp1;
{$else}
  S := scSqlSetUp2;
{$endif}

  ADOConnection1.Execute(S);
  try
    ADOQuery1.Open;
    try
      ADOQuery1.Edit;

      // Get explicit reference to the NonNullFieldName
      //  field to make working with it and investigating it easier

      AField := ADOQuery1.FieldByName('NonNullFieldName');

      //  The following, which requires the `TypInfo` unit in the `USES` list is to find out which exact type
      //  AField is.  Answer:  ftMemo, or ftString, depending on UseMAX.  
      //  Of course, we could get this info by inspection in the IDE
      //  by creating persistent fields

      S := GetEnumName(TypeInfo(TFieldType), Ord(AField.DataType));
      Caption := S;  // Displays `ftMemo` or `ftString`, of course

      AField.AsString:= '';
      ADOQuery1.Post; //<-- Exception raised while posting
    finally
      ADOQuery1.Close;
    end;
  finally
    //  Tidy up
    ADOConnection1.Execute(scSqlDropTable);
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  Test1;
end;

default a column with empty string

Yes - use a DEFAULT constraint:

DROP TABLE IF EXISTS `example`.`test`;
CREATE TABLE  `example`.`test` (
  `string_test` varchar(45) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Avoid NULL columns using DEFAULT Empty String

You can read up on the subject here

CREATE TABLE dbo.Test (ID INTEGER, EmptyString VARCHAR(32) DEFAULT '')

INSERT INTO dbo.Test (ID) VALUES (1)
INSERT INTO dbo.Test (ID) VALUES (2)

SELECT * FROM dbo.Test

DROP TABLE dbo.Test

Stop empty strings at the database level with EF code first

There is MinLength attribute but it does not enforce the constraint on database level, you should add this constraint using migration I think.

public partial class test : DbMigration
{
    public override void Up()
    {
        Sql("ALTER TABLE [dbo].[YOUR_TABLE] ADD CONSTRAINT " + 
            "[MinLengthConstraint] CHECK (DATALENGTH([your_column]) > 0)");
    }

    public override void Down()
    {
        Sql("ALTER TABLE [dbo].[YOUR_TABLE] DROP CONSTRAINT [MinLengthConstraint]");
    }
}

You can add sql code generators for EF to generate these codes for MinLength attribute, I'll give you a simplified hint here:

First mark properties with MinLength

public class Test
{
    public int Id { get; set; }
    [MinLength(1)]
    public string Name { get; set; }
}

Add MinLenghtAttribute to conventions and provide the value, which is the Length :

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Conventions.Add(
        new AttributeToColumnAnnotationConvention<MinLengthAttribute, int>(
            "MinLength",
            (property, attributes) => attributes.Single().Length));
}

the generated code for migration will be:

CreateTable(
    "dbo.Tests",
    c => new
        {
            Id = c.Int(nullable: false, identity: true),
            Name = c.String(
                 annotations: new Dictionary<string, AnnotationValues>
                 {
                     { 
                        "MinLength",
                         new AnnotationValues(oldValue: null, newValue: "1")
                     },
                 }),
         })
     .PrimaryKey(t => t.Id);

Override the SqlServerMigrationSqlGenerator to use this convention in order to generate the constraint sql code:

public class ExtendedSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(AddColumnOperation addColumnOperation)
    {
        base.Generate(addColumnOperation);
        AddConstraint(addColumnOperation.Column, addColumnOperation.Table);
    }

    protected override void Generate(CreateTableOperation createTableOperation)
    {
        base.Generate(createTableOperation);
        foreach (var col in createTableOperation.Columns)
             AddConstraint(col, createTableOperation.Name);
    }
    private void AddConstraint(ColumnModel column, string tableName)
    {
        AnnotationValues values;
        if (column.Annotations.TryGetValue("MinLength", out values))
        {
            var sql = string.Format("ALTER TABLE {0} ADD CONSTRAINT " +
                "[MinLengthConstraint] CHECK (DATALENGTH([{1}]) >= {2})"
                ,tableName, column.Name, values.NewValue);
            Generate(new SqlOperation(sql));
        }
   }
}

the code above contains generation for AddColumn and CreateTable operations you must add codes for AlterColumn, DropTable and DropColumns as well.

internal sealed class Configuration : DbMigrationsConfiguration<TestContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        SetSqlGenerator("System.Data.SqlClient", new ExtendedSqlGenerator());
    }
}

alter column table and make default value to empty string

You need to add a contraint. Referring to http://blog.sqlauthority.com/2008/05/31/sql-server-create-default-constraint-over-table-column/

ALTER TABLE Employee 
        ADD CONSTRAINT DF_Employee _JobTitle
        DEFAULT '' FOR sJobTitle

How to Constraint No Empty Strings on an Nvarchar Column