How to make a foreign key not using primary key

Question

I have a migration on Laravel for table pasien like this :

public function up()
{
    Schema::create('pasien', function (Blueprint $table) {
        $table->string('No_RM');
        $table->timestamps();


        $table->primary('No_RM');
    });
}

and now I want to make a foreign key to No_RM , NOT to id

public function up()
{
    Schema::create('data_primary', function (Blueprint $table) {
        $table->bigIncrements('id');
        $table->unique('RM_id');
        $table->string('file_primary');
        $table->timestamps();

        $table->foreign('RM_id')->references('No_RM')->on('pasien');
    });
}

and still have error

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 not null, file_primary varchar(255) not null, created_at timestamp ' at line 1")

Can someone correct my error?

Answer 1

Just add another migration to modify your pasien table like

Schema::table('pasien', function (Blueprint $table) {
   $table->unique('RM_id');
});

and now you can declare RM_id as foreign key in data_primary, table, to becoming a foreign key, it should unique key.

If you have dropped your migrations, you are creating new tables you can do it like

pasien table

public function up()
{
    Schema::create('pasien', function (Blueprint $table) {
        $table->increments('id');
        $table->string('No_RM')->unique();
        $table->timestamps();
    });
}

and data_primary table

public function up()
{
    Schema::create('data_primary', function (Blueprint $table) {
        $table->increments('id');
        $table->string('RM_id');
        $table->string('file_primary');
        $table->timestamps();

        $table->foreign('RM_id')->references('RM_id')->on('pasien')
                ->onUpdate('cascade')->onDelete('cascade');
    });
}

Answer 2

Conceptually you cant reference foreign key to a non primary key, it breaks the concept of relational database, can you provide me the ERD maybe i can help to redesign your DB structure