Can we get foreign keys ?

[CharlotteJu]

Hello,

Is it possible to retrieve the foreign key constraints of a table?
When I inspect the $customConstraints property, it always returns null.

Exemple :

class Todos extends Table {
IntColumn get id => integer()();

  @override
  Set<Column> get primaryKey => {id};
  }
  
  class Categories extends Table {
  IntColumn get id => integer()();
  IntColumn get todoId => integer()
      .references(Todos, #id,
          onUpdate: KeyAction.cascade, onDelete: KeyAction.setNull)
      .nullable()();

  @override
  Set<Column> get primaryKey => {id};
  }

In my .g.dart, I have :

late final GeneratedColumn<int> todoId = GeneratedColumn<int>(
      'TodoId', aliasedName, true,
      type: DriftSqlType.int,
      requiredDuringInsert: false,
      defaultConstraints: GeneratedColumn.constraintIsAlways(
          'REFERENCES Todos (Id) ON UPDATE CASCADE ON DELETE SET NULL'));

But when I check it at runtime :

database.categories.customConstraints = null; 
database.categories.$columns[1].$customConstraint = null;

I need to check constraint before I receive a SQLite file from server and I want check ON UPDATE and ON DELETE clauses.

[simolus3]

I'm working on a future drift version that will properly expose information about constraints on columns, but at the moment it is indeed very tricky to get access to that.

One thing you could do is:

1. Create a GenerationContext.fromDb(yourDatabase).
2. Call yourColumn.writeColumnDefinition(thatContext).
3. Check GenerationContext.sql to see whether it contains ON UPDATE / ON DELETE as a substring.

I know this isn't great, but that seems to be the only way to get that information at the moment.

[CharlotteJu]

Thanks a lot for your reply!
It works indeed.

Do you happen to know when the update to access foreign keys might be available ?

[simolus3]

No ETA yet unfortunately - it's quite a massive refactoring of the drift package that I've been working on for a while.