Oracle Foreign Key Constraints: ON DELETE and ON UPDATE

[luantranminh]

Problem Description

Oracle behaviour:

1. ON UPDATE: Oracle does not support any ON UPDATE actions for foreign keys
2. ON DELETE: Oracle only supports CASCADE and SET NULL actions

Currently, Bun sets NO ACTION as the default for both OnUpdate and OnDelete in foreign key relations, which causes SQL errors when working with Oracle databases.

Expected Behavior

When using Oracle dialect, Bun should:

1. Automatically set OnUpdate to empty/null (since Oracle doesn't support it)
2. Only allow CASCADE and SET NULL for OnDelete actions
3. Either throw a validation error or automatically clear unsupported OnDelete values

Current Behavior

Bun generates foreign key constraints with NO ACTION for both OnUpdate and OnDelete, which fails on Oracle with errors like:

[!CAUTION] ORA-02000: missing CASCADE keyword

Example Code

Model Definition

type User struct {
	ID     int64 `bun:",pk,autoincrement"`
	Name   string
	Emails []string
}


type Story struct {
	ID       int64 `bun:",pk,autoincrement"`
	Title    string
	AuthorID int64
	Author   *User `bun:"rel:belongs-to,join:author_id=id"`
}

Generated SQL (Current - Fails on Oracle)

CREATE TABLE "stories" ("id" INTEGER GENERATED BY DEFAULT AS IDENTITY, "title" VARCHAR2(255), "author_id" INTEGER, PRIMARY KEY ("id"), FOREIGN KEY ("author_id") REFERENCES "users" ("id")  ON UPDATE NO ACTION ON DELETE NO ACTION

[j2gg0s]

Thank you for your feedback and the solution you provided. It’s very detailed, and I basically don’t need to look up any additional information.

In the related PR, I attempted to provide default values for each dialect. However, I deferred the check for user-defined values until SQL execution, as I believe such errors are extremely rare.