Add basic support for foreign key

[thinkAmi]

Detail

Add basic support for foreign key.

This will allow foreign keys to be displayed in DataGrip.

Limitation

In SQLite, you can name foreign key constraints when you CREATE TABLE .

However, the foreign key constraint names themselves are not stored anywhere as table columns. System tables have SQL but are hard to reference.
How to get the names of foreign key constraints in SQLite? - Stack Overflow

Therefore, the foreign key constraint name is currently fixed to <table_name>_<id>_<seq>.

For more information, please see the screenshots.

Screenshot

Test

I have confirmed that it works by following these steps.

1. Create a new database in Cloudflare D1. (like name: d1-driver-test)
2. Save the following SQL to a file.

CREATE TABLE `shops` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text
);

CREATE TABLE `staffs` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text
);

CREATE TABLE `products` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text
);

CREATE TABLE `orders` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text,
  `product_id` integer,
  `shop_id` integer,
  `staff_id` integer,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON UPDATE cascade ON DELETE set null,
  FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON UPDATE set default ON DELETE restrict,    
  CONSTRAINT `fk__staff_who_ordered` FOREIGN KEY (`staff_id`) REFERENCES `staffs`(`id`) ON UPDATE no action ON DELETE no action
);

3. Execute SQL in Wrangler.

npx wrangler d1 execute d1-driver-test --remote --file=./d1.sql

4. The result looks like the screenshot.

[isaac-mcfadyen]

Hey,

Thanks for the PR! I'm sorry I haven't gotten much time to work on this myself - your PR is greatly appreciated 😄

I'll merge now.

[thinkAmi]

Thank you for the quick merge!

d1-jdbc-driver has been very helpful!

[isaac-mcfadyen]

No problem!

I'll try and get a release out today - just have to remember how builds worked, it's been so long 😅

[steebchen]

@isaac-mcfadyen would be awesome to have this release! thanks a lot for this project, it's such a lifesaver