vefcommunications.blogg.se - Sqlite foreign key deferrable

#SQLITE FOREIGN KEY DEFERRABLE UPDATE#

#SQLITE FOREIGN KEY DEFERRABLE UPDATE#

SQLSTATE: General error: 1 near " 0": syntax error ( SQL: CREATE TABLE tasks (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, account_id INTEGER NOT NULL, user_id INTEGER DEFAULT NULL, subject VARCHAR( 255) NOT NULL COLLATE BINARY, start_date DATETIME NOT NULL, state INTEGER DEFAULT 0 NOT NULL, task_type INTEGER DEFAULT 0 NOT NULL, task_queue_id INTEGER NOT NULL, created_by_type VARCHAR( 255) DEFAULT NULL COLLATE BINARY, created_by_id INTEGER DEFAULT NULL, created_at DATETIME DEFAULT NULL, updated_at DATETIME DEFAULT NULL, content CLOB DEFAULT NULL COLLATE BINARY, end_date DATETIME DEFAULT NULL, CONSTRAINT 0 FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE NO ACTION ON DELETE SET NULL NOT DEFERRABLE INITIALLY IMMEDIATE, CONSTRAINT 1 FOREIGN KEY (account_id) REFERENCES accounts (id) ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE))Īt vendor/laravel/framework/src/ Illuminate/ Database/ Connection.php: 671 667| // If an exception occurs when attempting to run a query, we'll format the error 668| // message to include the bindings with SQL, which will make this exception a 669| // lot more helpful to the developer instead of just the database's errors. My two change suggestions try to fix both of the points and i can create PRs for both of them if it helps

The \Doctrine\DBAL\Platforms\AbstractPlatform::getForeignKeyBaseDeclarationSQL uses $foreignKey->getQuotedName($this) but that does not escape numbers which leads us to the above 0 in the query.

The \Doctrine\DBAL\Schema\SqliteSchemaManager::listTableForeignKeys has a "fallback" for creating a name (when it does not find one after the CONSTRAINT keyword) which uses its "id" which is a number generated by the sqlite database (effectively "adding" one that was never there, for functionality using null here is better as its the value thats expected by other methods).

The existing sqlite table before the change has a foreign key without a name.

#9 Illuminate\Database\Schema\Builder->table() called at #8 Illuminate\Database\Schema\Builder->build() called at #7 Illuminate\Database\Schema\Blueprint->build() called at #6 Illuminate\Database\Schema\Blueprint->toSql() called at #5 Illuminate\Database\Schema\Grammars\Grammar->compileChange() called at #4 Illuminate\Database\Schema\Grammars\ChangeColumn::compile() called at

#3 Illuminate\Database\Schema\Grammars\ChangeColumn::getChangedDiff() called at #2 Doctrine\DBAL\Schema\SqliteSchemaManager->listTableDetails() called at #1 Doctrine\DBAL\Schema\AbstractSchemaManager->listTableDetails() called at #0 Doctrine\DBAL\Schema\SqliteSchemaManager->listTableForeignKeys() called at #12 Illuminate\Database\Schema\Builder->table() called at Ĭall of Blueprint $table->string($column, $length)->change() in a migration while running unit testsĪnd when getting the foreign keys from the sqlite db in \Doctrine\DBAL\Schema\SqliteSchemaManager::listTableForeignKeys: #11 Illuminate\Database\Schema\Builder->build() called at #10 Illuminate\Database\Schema\Blueprint->build() called at #9 Illuminate\Database\Schema\Blueprint->toSql() called at #8 Illuminate\Database\Schema\Grammars\Grammar->compileChange() called at

#7 Illuminate\Database\Schema\Grammars\ChangeColumn::compile() called at #6 Doctrine\DBAL\Platforms\SqlitePlatform->getAlterTableSQL() called at #5 Doctrine\DBAL\Platforms\SqlitePlatform->getCreateTableSQL() called at #4 Doctrine\DBAL\Platforms\AbstractPlatform->getCreateTableSQL() called at #3 Doctrine\DBAL\Platforms\SqlitePlatform->_getCreateTableSQL() called at #2 Doctrine\DBAL\Platforms\SqlitePlatform->getForeignKeyDeclarationSQL() called at #1 Doctrine\DBAL\Platforms\AbstractPlatform->getForeignKeyDeclarationSQL() called at #0 Doctrine\DBAL\Platforms\AbstractPlatform->getForeignKeyBaseDeclarationSQL() called at