Skip to main content

External foreign keys

An external foreign key preserves an ordinary SQL relationship when the target table belongs to another Kurabase instance on the same data chain. It stores the target's resolved contract address, rather than an ENS name, so a later ENS record update cannot silently retarget an integrity constraint.

CREATE TABLE posts (
  id bigint PRIMARY KEY,
  profile_id bigint,
  body text NOT NULL
);

ALTER TABLE posts
  ADD EXTERNAL FOREIGN KEY (profile_id)
  REFERENCES INSTANCE '0x1234567890abcdef1234567890abcdef12345678'
  TABLE profiles (id);

The source column must be nullable. The target columns must be that table's primary key or a declared unique key, and source and target key arities must match. Constraint DDL requires administrative authority, like local schema DDL.

Bind an ENS-discovered instance​

ENS is useful for discovery, not durable authorization. First resolve the name:

GET /meta/v1/ens/community.eth

Then copy the returned contract address into REFERENCES INSTANCE. This makes the intended binding reviewable in the migration and stable even if community.eth later resolves elsewhere. Ordinary cross-instance reads may still use the quoted ENS schema syntax:

SELECT posts.id, profiles.display_name
FROM posts
JOIN "community.eth".profiles AS profiles ON profiles.id = posts.profile_id;

Write and read semantics​

When a source row introduces or changes a non-NULL reference, the gateway rebuilds the fixed target contract at one pinned data-chain block. The target row must exist and be visible through the target instance's anonymous RLS policy. A source-side owner or service_role never inherits remote authority.

If the remote target is later deleted, hidden by its policy, becomes unavailable, or no longer has the declared key, Kurabase does not rewrite the source contract. Instead, gateway reads expose the source key as logical SQL NULL. This rule applies consistently to SQL, REST table reads, materialized view reads, and read-only SQL-function RPCs. Unrelated source updates remain valid; only a new or changed external reference requires target validation.

Remove a binding with its complete identity:

ALTER TABLE posts
  DROP EXTERNAL FOREIGN KEY (profile_id)
  REFERENCES INSTANCE '0x1234567890abcdef1234567890abcdef12345678'
  TABLE profiles (id);

Boundaries​

  • The target must be a Kurabase instance on the same configured EVM data chain. Cross-chain references are not implemented.
  • This is an integrity and read-projection feature. It does not create cascading deletes, cross-instance writes, RPC calls, or PostgREST embedded relations.
  • The current demo projection replays from contract deployment. A production indexer needs bounded deployment metadata and event indexing before using this across long-lived instances.