I can't do a foreign key, constraint error

Question

I'm having a commum error and I can't go over it, know as [Illuminate\Database\QueryException] SQLSTATE[42000] here is the full error:

[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'unsigned null' at line 1 (SQL: alter table files add slug varchar(255) unsigned null)

A separated error:

[PDOException] SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'unsigned null' at line 1

He are the tables I'm trying to do the foreign key:

Files

  public function up()
    {
        Schema::create('files', function($table)
        {
            $table->engine = 'InnoDB';
            $table->increments('id')->unsigned();
            $table->string('name')->nullable();
            $table->boolean('enable_sch')->nullable();
            $table->datetime('schdate')->nullable();
            $table->string('flsize')->nullable();
            $table->timestamps();
        });
        Schema::table('files', function($table)
        {
          $table->string('slug')->unsigned()->nullable();
          $table->foreign('slug')->references('slug')->on('slugs');
        });
    }

Slugs

public function up()
    {
        Schema::create('slugs', function($table)
        {
            $table->engine = 'InnoDB';
            $table->string('nameslug');
            $table->string('slug')->unsigned()->nullable();
            $table->timestamps();
        });
    }
    public function down()
    {
        Schema::dropIfExists('slugs');
    }

What I'm trying to do is to add to the files table the *slug column* from the slugs table.

score 3 · Answer 1 · answered May 13 '16 at 10:53

3

I guess string data type cannot be unsigned(), that's why you're getting an error.

Use this in both migrations:

$table->string('slug')->nullable();

answered May 13 '16 at 10:53

Alexey Mezenin

158,981
26
290
279

Mhhh I change it to `$table->integer('slug')->nullable();` the error I was getting dispeared but I have another one ``[Illuminate\Database\QueryException] SQLSTATE[HY000]: General error: 1005 Can't create table 'october.#sql-3f9_3b' (errno: 150) (SQL: alter tab le `files` add constraint files_slug_foreign forei gn key (`slug`) references `slugs` (`slug`))`` – Ricardo Cruz May 13 '16 at 10:57
Try to separate migrations. Temporarily remove `files` migration from the directory and run `php artisan migrate` command. Then move this migration back and run the command again. – Alexey Mezenin May 13 '16 at 11:00
Try to move `$table->string('slug')->nullable();` to the `Schema::create` clause. Then put `Schema::table` part where you're adding foreign key to a table into separate migration file. Run migration which will create table, then run migration which will add foreign key. Sometimes it works for me. – Alexey Mezenin May 13 '16 at 11:23
That works, but it's something wrong when I try to do the relation, but I dont know what... – Ricardo Cruz May 13 '16 at 11:35
I don't know if I can do these relations with a `string`, that might be the problem. – Ricardo Cruz May 13 '16 at 13:59

score 0 · Accepted Answer · edited Jun 16 '16 at 09:41

0

As I'm using OctoberCMS with Laravel, there are relationships like $hasMany and $belongsTo on the **models** file.

If are in trouble with this on OctoberCMS, just consult this

edited Jun 16 '16 at 09:41

user1853181

813
6
12

answered May 16 '16 at 14:49

Ricardo Cruz

117
10

Please avoid link only answer but quote the relevant part that was a solution for you. – k0pernikus Jun 16 '16 at 09:41

I can't do a foreign key, constraint error

2 Answers2