How to add unique key for 2 columns : employee_id and only month and year from the date column

Question

I have a table where I have all the employee details. I want to make employee_id and only month and year from date_of_salary as unique on the table. Only one row should be present for that particular employee ID and month/year. The date_of_salary is a column with date datatype.

I tried using the following but this works for the employee id and complete date. It takes multiple entries for same month.

$table->unique(['employee_id', 'date_of_salary']);

This is my complete up function for the migration

public function up()
{
    Schema::create('employee_salary_details', function (Blueprint $table) {
        $table->increments('id');

        $table->unsignedInteger('employee_id')->required();
        $table->date('date_of_salary')->required();

        $table->foreign('employee_id')
              ->references('id')
              ->on('employees')
              ->onDelete('restrict');

        $table->unique(['employee_id', 'date_of_salary']);

        $table->timestamps();
    });
}

Each row should be unique for an employee and salary month/year. Because the salary can happen only once a month but on any date.

How about saving splitting `date_of_salary` into 2 columns `year_of_salary`, and `month_of_salary` so you can define the unique value on `employee_id`, `year_of_salary` and `month_of_salary` ? — Oluwatobi Samuel Omisakin, Dec 20 '18 at 17:48
Thanks for the reply @Oluwatobi Samuel Omisakin. This will work, but needs addition of 2 columns in my table. I will check if there is any other work around for this or this can be implemented :) — Shraddha Banerjee, Dec 21 '18 at 07:14
Two columns 'replacement' for date_of_salary is not a bad thing ;) Infact it feels more explicit given your use case. If you even need to later use the payment day, for any reason you can add `day_of_salary`. :) You can find an inspiration from this question: https://stackoverflow.com/questions/9134497/mysql-datatype-to-store-month-and-year-only — Oluwatobi Samuel Omisakin, Dec 21 '18 at 07:52

score 0 · Answer 1 · answered Dec 20 '18 at 19:21

0

->unique() means for example date_of_salary column has unique dates. So you can not add multiple times '2018-12-21', just once in the whole column.

I think you need ->primary(['employee_id', 'date_of_salary']), if you want unique rows by multiple columns.

| employee_id | date_of_salary |
+-------------+----------------|
| 1           | 2018-11-21     | <- OK
| 1           | 2018-12-21     | <- OK
| 2           | 2018-12-21     | <- OK
| 1           | 2018-12-21     | <- Not possible

answered Dec 20 '18 at 19:21

Levente Otta

713
6
17

Thanks for the reply @Levente Otta. My requirement is that there shouldn't be even employee_id = 1 and date_of_salary = 2018-11-22. I must have only one entry for "2018-11" or "2018-12". – Shraddha Banerjee Dec 21 '18 at 07:12
What if you store the date in 3 different column? Year, Month and day. In this case you need make _id, year and month columns to primary – Levente Otta Dec 21 '18 at 10:32
Yes, this can be done. The same solution is provided by @Oluwatobi Samuel Omisakin. I think I will go with it now. Thanks :) – Shraddha Banerjee Dec 21 '18 at 10:43

score 0 · Accepted Answer · answered Dec 21 '18 at 10:44

0

Created 3 different fields in database for day, month and year. Made the employee_id, month and year as unique key with 3 column names.

answered Dec 21 '18 at 10:44

Shraddha Banerjee

197
2
14

How to add unique key for 2 columns : employee_id and only month and year from the date column

2 Answers2