Laravel模式构建器主键作为外键 [英] Laravel schema builder primary key as foreign key

问题描述

我正在使用Laravel 4.2模式构建器来创建一些互相引用的表，并且遇到了一些问题.

I'm using the Laravel 4.2 schema builder to create some tables referencing each other, and am having some issues.

我有一个简化的ERD.请注意，仅显示相关列:

I have a simplified ERD. Note that only relevant columns are shown:

请注意，由于我正在开发一个模块以挂接到现有系统中，因此我无法以任何方式修改tblcurrencies和tbldomains表.

Note that I cannot modify the tblcurrencies and tbldomains tables in any way, since I am developing a module to hook into an existing system.

我正在努力实现以下目标:

I am trying to achieve the following:

1. extensions表包含有关tbldomains表中行的额外信息
2. prices表包含以某种货币表示的域的定价信息，并带有附加的类型值(注册，续订，转让)
3. 我想使用外键，以便可以级联删除.

1. The extensions table contains extra information about rows in the tbldomains table
2. The prices table contains pricing information about a domain in a certain currency, with the additional type value (registration, renewal, transfer)
3. I want to use foreign keys so that I can cascade deletions.

当前，我使用以下代码创建两个表:

Currently, I use the following code to create the two tables:

Capsule::schema()->create('extensions', function ($table) {
    $table->engine = 'InnoDB';
    $table->integer('relid', 10);
    // ...
    $table->primary(['relid']);
    $table->foreign('relid')->references('id')->on('tbldomains')->onDelete('cascade');
});

Capsule::schema()->create('prices', function ($table) {
    $table->engine = 'InnoDB';
    $table->integer('relid', 10);
    $table->integer('currency', 10);
    $table->enum('type', ['domainregister', 'domainrenew', 'domaintransfer']);
    // ...
    $table->primary(['relid', 'currency', 'type']);
    $table->foreign('relid')->references('relid')->on('extensions')->onDelete('cascade');
    $table->foreign('currency')->references('id')->on('tblcurrencies')->onDelete('cascade');
});

prices表的创建脚本将导致以下SQL查询:

The creation script for the prices table results in the following SQL query:

create table `prices` (`relid` int unsigned null auto_increment primary key, `currency` int unsigned null auto_increment primary key, `type` enum('domainregister', 'domainrenew', 'domaintransfer') not null, ...) engine = InnoDB

继而导致以下错误:

SQLSTATE [42000]:语法错误或访问冲突:1075不正确的表定义；默认值为0.只能有一个自动列，并且必须将其定义为键

SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

我还尝试过将主键设置为唯一，从而确定Laravel会自动将主整数键设置为自动增量.

I have also tried setting the primary keys as unique instead, figuring that perhaps Laravel automatically set primary integer keys as auto increment.

此外，我尝试按照此和此答案

Furthermore, I tried setting the columns as unsigned and index, as suggested by this and this answer

由于它们只是外键，因此如何阻止架构生成器将relid和currency字段设置为自动递增?

How do I stop the schema builder from setting the relid and currency fields to auto increment, since they are simply foreign keys?

推荐答案

，只有string()方法可以具有length属性. Laravel文档Api

based on Laravel Documentation Api on the below link, only string() method can have length attribute. Laravel Documentation Api

因此，要使这两列为unsigned而不是primary key或auto increment，请进行以下更改:

So to make those two column unsigned and not primary key or auto increment make the following change:

从这里:

$table->integer('relid', 10);
$table->integer('currency', 10);

对此:

$table->integer('relid', false, true);
$table->integer('currency', false, true);

---

由于根据文档，integer()方法的语法为:

---

Because as per the documentation the integer() method syntax is:

integer(string $column, bool $autoIncrement = false, bool $unsigned = false)

您所做的就是为布尔变量($ autoIncrement)分配了一个值(10)，该布尔变量在这种情况下将始终返回true.为了进一步证明这一点，请从php.net返回以下链接. php.net布尔

And what you did is you assigned a value (10) to a boolean variable ($autoIncrement) which will always returns true on this case. For further proof of this, please refer back to the below link from php.net. php.net Boolean

我以前也遇到过同样的问题，当我开始回头参考Laravel文档时，90％的困惑将被清除.希望对您有帮助.

I had the same issue before, and when I start referring back Laravel documentation 90% of confusion will be cleared. Hope this helps you.

注意:您还可以使用unsignedInteger()方法，我认为该方法更明确，更容易记住:

Note: you can also use unsignedInteger() method, which i think it's more explicit and easier to remember:

unsignedInteger(string $column, bool $autoIncrement = false)

因此代码将如下所示:

$table->unsignedInteger('relid');
$table->unsignedInteger('currency');

这篇关于Laravel模式构建器主键作为外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！