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?

(Obviously) This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. Please in code questions give a [mcve]--cut & paste & runnable code & desired output & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) — philipxy, Apr 27 '19 at 02:33

Prafulla Kumar Sahu · Accepted Answer · 2019-04-27T03:26:27.837

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');
    });
}

edited Apr 27 '19 at 03:26

answered Apr 27 '19 at 02:38

Prafulla Kumar Sahu

9,321
11
68
105

and On Table parent i changed this string to BigIncrements sir ? – Adhik Mulat Apr 27 '19 at 03:10
@AdhikMulat you do not need to, for example `email_id` can be a `unique` column and can be a `foreign key` in another table. – Prafulla Kumar Sahu Apr 27 '19 at 03:11
@AdhikMulat you are making it wrong dear, if you have already created tables, you should use a new migration to modify the `pasien` table to make the `RM_id` unique and if you have rolledback your migrations you just need to add `$table->unique('RM_id');` your `pasien` table and specify `RM_id` as foreign key in reference with `pasien` table. – Prafulla Kumar Sahu Apr 27 '19 at 03:18
hmmm i really dont get it . See on this table 'Pasien' i add string to type data . is this true for foreign type : "unique' – Adhik Mulat Apr 27 '19 at 03:24
look if you are using laravel 5.8 you need to use bigincrements for the primary key and unsignedBigInteger for the foreign key https://laravel.com/docs/5.8/migrations#foreign-key-constraints – Ahmed Aboud Apr 27 '19 at 06:43
@AhmedAboud that is an example can you identify where it has said, you are bould to use `unsignedBigInteger ` in parent table? – Prafulla Kumar Sahu Apr 27 '19 at 06:44
https://github.com/laravel/framework/pull/26472 and if the parent table id is bigIncrements the reference key must be same type unsignedBigInteger – Ahmed Aboud Apr 27 '19 at 06:48
1

@AhmedAboud to make foreign key, you only need the column to be unique in parent table see https://learn.microsoft.com/en-us/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-2017 – Prafulla Kumar Sahu Apr 27 '19 at 06:51
oh after 2nd check he is using a string as a reference iam so sorry – Ahmed Aboud Apr 27 '19 at 06:51

score -1 · Answer 2 · answered Apr 27 '19 at 02:12

-1

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

answered Apr 27 '19 at 02:12

Matius Nugroho Aryanto

801
3
17
41

How to make a foreign key not using primary key

2 Answers2