Why does FireDAC ignore index name?

Question

I'm trying to creating a table in a SQL Server database using FireDAC. However, instead of using the index name I provide, FireDAC uses a bad index name, raising an exception and the table does not get created. Am I doing something wrong? If not, is there a work-around?

Note that I'm using the valid database schema name cnf for TableName. I specifically need to create the table in a schema.

Simplest test case:

var
  Connection: TFDConnection;
  Table: TFDTable;
begin
  Connection := TFDConnection.Create(nil);
  Table := TFDTable.Create(nil);
  try
    Connection.Params.Add ('DriverID=MSSQL');
    Connection.Params.Add ('OSAuthent=No');
    Connection.Params.Add ('User_Name=sa');
    Connection.Params.Add ('Password=XXXXXX');
    Connection.Params.Add ('Server=DAVE-DELL\MSSQLSERVER2016');
    Connection.Params.Add ('Database=PROJECT_DB');
    Connection.Params.Add ('MARS=No');

    Connection.Open;
    Table.Connection := Connection;

    Table.TableName := 'cnf.TestTable';
    Table.FieldDefs.Add ('TableID', ftAutoInc, 0, true);
    Table.FieldDefs.Add ('Field1', ftInteger, 0, true);
    Table.FieldDefs.Add ('Field2', ftstring, 100, true);

    Table.IndexDefs.Add ('PK_XYZ', 'TableID', [ixPrimary]);   // should use this index name!

    Table.CreateTable (true);

  finally
    Table.Free;
    Connection.Free;
  end;

end;

An exception is raised:

[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '.'.

Running SQL Server Profiler shows me that FireDAC is trying to create the index using the following SQL code:

ALTER TABLE temp.TestTable ADD CONSTRAINT [cnf].[PK_TestTable] PRIMARY KEY (TableID)

And, of course, [cnf].[PK_TestTable] is not a valid index name in T-SQL, which is the crux of the problem.

If I remove the line Table.IndexDefs.Add, the table is created properly, but without the index.

If I replace that line with the following, it gives the same problem:

with Table.IndexDefs.AddIndexDef do begin
  Name := 'PK_XYZ';
  Options := [ixPrimary];
  Fields := 'TableID';
end;

If I replace setting the table name with the following, it gives the same problem:
```
Table.TableName := 'TestTable';
Table.SchemaName := 'cnf';
```

Why is it using it's own (wrong) index name, instead of the name I gave it? (i.e. PK_XYZ)

Embarcadero® Delphi 10.1 Berlin Version 24.0.25048.9432
SQL Server 2016 (SP2-CU4) - 13.0.5233.0 (X64)

Do you get the same behaviour if you use the MS OleDB driver instead of the ODBC one? — MartynA, Jul 01 '19 at 19:42
@MartynA good suggestion - I will check when I get a chance. — Reversed Engineer, Jul 02 '19 at 14:49
Based on Peter's well scoped out answer, below, why not just avoid using the FD index creation routines in this case? Create the index the an `ExecSQL('Alter Table ...')` call — JacalarRick, Jul 03 '19 at 15:30
@JacalarRick Thank you - I've done exactly that for now, until I can experiment more. — Reversed Engineer, Jul 03 '19 at 16:02

score 4 · Accepted Answer · answered Jul 02 '19 at 02:31

Am I doing something wrong?
Why is it using it's own (wrong) index name, instead of the name I gave it?

You seem to be doing everything just right. The issue is with the generated SQL command as you have tracked that down. SQL Server doesn't allow schema name in constraint name when adding a constraint using ALTER TABLE. Constraints created this way automatically become part of schema of the related table, however you should later use schema name when referring to the constraint:

SELECT OBJECT_ID('cnf.PK_XYZ')

Now where do the things go wrong? FireDAC uses TFDPhysCommandGenerator and its ancestors to generate SQL commands for specific DBMS. Your call to CreateTable method results in call to TFDPhysCommandGenerator.GetCreatePrimaryKey, which is responsible for generating SQL for primary key. It also contains this code:

sTab := GetFrom;
FConnMeta.DecodeObjName(sTab, rName, nil, [doUnquote]);
rName.FObject := 'PK_' + rName.FObject;
Result := 'ALTER TABLE ' + sTab + ' ADD CONSTRAINT ' +
  FConnMeta.EncodeObjName(rName, nil, [eoQuote, eoNormalize]) + ' PRIMARY KEY (';

What this code does is that it takes your fully qualified table name (sTab) splits it (DecodeObjName) into parts (rName) prepends 'PK_' to table name and joins the parts (EncodeObjName) back to fully qualified name, which is then used as the constraint name for your primary key. Now we can clearly see that command generator ignores your index name and generates erroneous T-SQL. This can either be a bug or just a not supported feature. EMBT has to make decision on that. I'd recommend reporting it as a bug.

Is there a work-around?

Yes, you can either hook problematic method or you can override it in your own derived class. Implementation none of these is trivial and due to legal issues I'm not going to extend it here, because I would have to duplicate the original FireDAC code.

As for the syntax error adding these lines to 'TFDPhysCommandGenerator.GetCreatePrimaryKey' implementation after DecodeObjName would fix the issue:

rName.FCatalog := '';
rName.FSchema := '';
rName.FBaseObject := '';
rName.FLink := '';

Fixing constraint name is going to be more cumbersome than that, because the method only receives index column names as argument and has no obvious access to original IndexDefs where you could just use index name as primary key constraint name. Gaining access to index name from there would also allow you to get rid of decoding/encoding table name into index name. This process, however, could be essential for other DMBS's than SQL Server.

PS: If only half of all the questions were written in this manner ... Thank you for this wonderful question.

Thank you for your great answer! I never got into the internal parts of FireDAC, so this helps a lot. — Reversed Engineer, Jul 02 '19 at 12:32

Why does FireDAC ignore index name?

1 Answers1