Wrong inferred constraint on composite primary key with a default

[avalente]

Description

If a table has a composite primary key with a default constraint on one of the columns, the generated constraint is wrong, leading to ConstraintException errors.

Repro steps

Let's define a table with a composite constraint, such as:

create table test.tbl(
  k1 uniqueidentifier not null, 
  k2 uniqueidentifier not null, 
  k3 uniqueidentifier not null default NEWID(), 
  v DOUBLE PRECISION,
PRIMARY KEY(k1, k2, k3))

Then define the ProgrammabilityProvider and a couple of fixed values:

type DB = SqlProgrammabilityProvider<ConnectionString>

let k1 = Guid.NewGuid()
let k2 = Guid.NewGuid()

Expected behavior

The following code should work (it does if I remove the default from k3 ):

let t = new DB.test.Tables.tbl()
t.AddRow(k1=k1, k2=k2, k3=Guid.NewGuid(), v=Some 1.0)
t.AddRow(k1=k1, k2=k2, k3=Guid.NewGuid(), v=Some 2.0)
t.AddRow(k1=k1, k2=k2, k3=Guid.NewGuid(), v=Some 3.0)

t.Constraints
> 
val it : Data.ConstraintCollection =
  seq [Constraint1 {Columns = [|k1; k2; k3|];
                    ConstraintName = "Constraint1";
                    ExtendedProperties = dict [];
                    IsPrimaryKey = true;
                    Table = [test].[tbl2];}]

Actual behavior

Since it have a default value, the type of k3 becomes Option<>, which means that the AddRow calls should be written as:

t.AddRow(k1=k1, k2=k2, k3=Some(Guid.NewGuid()), v=Some 1.0)
t.AddRow(k1=k1, k2=k2, k3=Some(Guid.NewGuid()), v=Some 2.0)

But on the second AddRow an exception is raised:

> 
System.Data.ConstraintException: Column 'k1, k2' is constrained to be unique.  Value '39477bbb-7256-4477-97de-54fb06458c27, dc74a8e9-38fe-41c8-bb63-168c3ee75ca7' is already present.
   at System.Data.UniqueConstraint.CheckConstraint(DataRow row, DataRowAction action)
   at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)
   at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Boolean suppressEnsurePropertyChanged, Int32 position, Boolean fireEvent, Exception& deferredException)
   at System.Data.DataTable.InsertRow(DataRow row, Int64 proposedID, Int32 pos, Boolean fireEvent)
   at <StartupCode$FSharp-Data-SqlClient>[email protected]`1-Add(T item) in C:\Users\dmitry\Documents\GitHub\FSharp.Data.SqlClient\src\SqlClient\DataTable.fs:line 29
   at <StartupCode$FSI_0038>.$FSI_0038.main@() in C:\Users\myuser\Projects\MyProject\Scripts\scratch.fsx:line 60
Stopped due to error

this is because the primary key constraint isn't inferred correctly:

val it : Data.ConstraintCollection =
  seq [Constraint1 {Columns = [|k1; k2|];
                    ConstraintName = "Constraint1";
                    ExtendedProperties = dict [];
                    IsPrimaryKey = true;
                    Table = [test].[tbl];}]

k3 is missing, I guess because of an ambiguity between None values due to column nullability vs due to the presence of a default value.

Known workarounds

The only workaround I found is to remove the default constraint, such as:

DECLARE @constraintName AS VARCHAR(1024) = (
SELECT d.name
FROM sys.default_constraints d
JOIN sys.schemas s ON d.schema_id=s.schema_id
JOIN sys.tables t ON d.parent_object_id=t.object_id
JOIN sys.columns c ON t.object_id=c.object_id AND d.parent_column_id=c.column_id
WHERE s.name='test' AND t.name='tbl' AND c.name='k3' AND d.type='D')

DECLARE @statement AS VARCHAR(1024) = 'ALTER TABLE test.tbl DROP CONSTRAINT ' + @constraintName 

EXECUTE(@statement)

GO

Related information

• FSharp.Data.SqlClient package 1.8.2 (Latest stable package on nuget.org)
• Windows 10 1703
• SQL Server 14.0
• .NET framework 4.7.1, F# runtime 4.4.3.0
• Visual Studio 2017 (15.6.4)

[smoothdeveloper]

@avalente thanks for the excellent bug report.

Default values are bad, I keep assuring my suroundings about that fact whenever one is added to schemas 😄.

More seriously, if you feel like tackling the issue, it is most likely located in this spot:

https://github.com/fsprojects/FSharp.Data.SqlClient/blob/a13d1e8ae438ada109bbf9bf8e99c28beff34098/src/SqlClient/SqlClientProvider.fs#L374-L401

or in the vincinity around, where the primary key for the table is constructed based on meta data.

You could also just start by contributing a failing unit test in a PR, that would be super helpful in itself.