Primary Keys

A primary key defines a uniqueness constraint on a query's output. The key specifies one or more columns whose combined value must uniquely identify each row in any given result. Declare a primary key by adding a @primaryKey annotation just before the main part of your query.

Examples

The following query shows routes and defines the primary key to be the combination of device name, VRF and route prefix:

@primaryKey(DeviceName, VRF, Prefix)
foreach device in network.devices
foreach networkInstance in device.networkInstances
let afts = networkInstance.afts
where isPresent(afts.ipv4Unicast)
let ipv4Unicast = afts.ipv4Unicast
foreach ipEntry in ipv4Unicast.ipEntries
where length(ipEntry.prefix) == 32
select {
  DeviceName: device.name,
  VRF: networkInstance.name,
  Prefix: ipEntry.prefix,
  "Next Hops": (foreach nextHop in ipEntry.nextHops
                select nextHop.ipAddress)
}

The following query defines a primary key on the union of classic and SNMP devices:

classicDevices =
  foreach device in network.devices
  select {
    name: device.name,
    locationName: device.locationName,
    profileName: ""
  };

snmpDevices =
  foreach snmpSource in network.endpoints
  select {
    name: snmpSource.name,
    locationName: snmpSource.locationName,
    profileName: snmpSource.profileName
  };

@primaryKey(name)
classicDevices + snmpDevices

This example shows that a primary key can be defined on any main expression, not just a comprehension.

Note

NQE History can only be enabled on a query that has a primary key.

Syntax

The attribute consists of the string @primaryKey followed by a parenthesized set of column names. The order of the columns does not matter.

@primaryKey(DeviceName, VRF, Prefix)

If a column name contains spaces, commas, or other special characters, it must be quoted with double quotes.

@primaryKey("Device Name", VRF, "Pre-fix")

Validation

A query has a valid primary key if the following are all satisfied:

• The query's main expression is annotated by @primaryKey.
• The @primaryKey annotation contains at least one column name.
• The @primaryKey annotation does not repeat any column names.
• Each column name in the @primaryKey annotation is present in the query result.

A query with an invalid primary key is itself invalid (and cannot be run).

Enforcement

When running a query with a primary key, if a duplicate key value is found, then the result is an error that includes an example of a duplicate key value.

Best Practices

When selecting columns for the primary key, it is best to achieve the following goals:

• Uniqueness: Include enough columns so that the primary key value for each row is unique.
• Stability: Include columns such that the primary key value for each row is a stable. (Stable values do not change over time unless the identity of the value they represent changes.)
• Minimal: Exclude any column from the primary key if the resulting primary key still satisfies the uniqueness condition.

For example:

• For a query listing interfaces, use device name and interface name.
  • Don't include fields like interface description, since the interface description could change for the same interface and that would make the interface with the changed description appear as a different entry.
• For a query listing routes, use device name, VRF, and prefix.
• For a query listing BGP peers, use device name, VRF, and peer IP address.

Note

There may be multiple possible choices for a primary key. Choose the primary key that best represents the natural identity of the entities in the query's results.