Skip to content

DF_INDEX_SQL_PRIMARY_KEY

See Also: Get_Attribute, Set_Attribute

Allows you to define or query the primary key of a table in a SQL database (MSSQL, DB2, or any database connected through ODBC that supports primary keys).

Level

Index

Supported by

The DataFlex SQL Drivers (SQL Server, DB2, and ODBC), revision 6 and higher.

Type

Numeric, permanent

Access

Read / Write

Values

  • True
  • False

Syntax

Use cli.pkg
Get_Attribute DF_INDEX_SQL_PRIMARY_KEY of {tableNumber} {indexNumber} to {BooleanVariable}
Set_Attribute DF_INDEX_SQL_PRIMARY_KEY of {tableNumber} {indexNumber} to {True|False}

Remarks

A primary key is a SQL database concept that consists of a Primary Key constraint and an index. A Primary Key constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values and cannot contain NULL values. A table can have only one Primary Key, which may consist of single or multiple columns.

Setting DF_INDEX_SQL_PRIMARY_KEY to true will also set DF_INDEX_CLUSTERED to true.

To create a table with a primary key in the Studio, you would:

  1. Create a New Table (e.g., Customer).
  2. Create a column (or columns) for your PK (e.g., Customer.Number).
  3. Create an Index for your PK (e.g., Create one segment Customer.Number Index).
  4. For that index, set the attribute DF_INDEX_SQL_PRIMARY_KEY to True.

This will define the PK and will automatically make this a clustered index. This technique will work with both standard and legacy style (Recnum) tables.

Primary Key versus Primary Index

DF_INDEX_SQL_PRIMARY_KEY and DF_FILE_PRIMARY_INDEX are quite similar, but there are also some differences. The logical concept behind both is the same: both are a way to uniquely identify a record in a table.

  • DF_INDEX_SQL_PRIMARY_KEY is a SQL concept. When defining a DF_INDEX_SQL_PRIMARY_KEY on a table, this defines a physical Primary Key (constraint and index) in the SQL database. Note that in SQL, a primary key is not a requirement. It is possible to define tables without a primary key.

  • DF_FILE_PRIMARY_INDEX is a DataFlex API concept. In DataFlex, every table must have a primary index. The primary index is, for example, used for refinding a record (reread).

For Recnum tables: - The DF_FILE_PRIMARY_INDEX always points to the index on the Recnum column (index.0). - The DF_INDEX_SQL_PRIMARY_KEY will be different from the DF_FILE_PRIMARY_INDEX.

For standard tables, DF_FILE_PRIMARY_KEY will usually be the same as DF_FILE_PRIMARY_INDEX, although this is not a requirement. They can be on different indexes.

In DataFlex, any table must always have a DF_FILE_PRIMARY_INDEX, but a SQL_PRIMARY_KEY is not required.

The Primary Key index is usually defined as a clustered index. There may be rare cases where you would not want your PK index to be clustered (if you want either no clustered index or a different clustered index). In such a case, you can use the DF_INDEX_CLUSTERED attribute to assign a different or no clustered index. You can only have one PK and one clustered index. Therefore, DF_INDEX_SQL_PRIMARY_KEY and DF_INDEX_CLUSTERED can only be set to True for one or no indexes.

If you are using Recnum tables, your Recnum column cannot be designated as the PK or the clustered index. This should not be an issue, as it is not recommended to use Recnum as your only unique column (i.e., you don't use Recnum in relationships).