rony rony - 24 days ago 4
MySQL Question

I want to insert multiple month's salary in an employee id. right now i can insert only one month's salary in one customer id

"employee" table has E_ID, E_Name, E_Address, E_Mail, E_Dept. [E_ID is primary key]

"salary" table has E_ID, S_Amount, S_Date columns.


Suppose an E_ID is 4001 and it is November month. I can only insert November month's salary into 4001 E_ID. But i want to insert December, January, February. all months salary into one E_ID and also want to view per month's salary. Any help will be appreciated.

Answer

In Salary table , add a new column say 'id' set it as integer , primary key , auto-increment.

Do not make E_ID as primary column in Salary table. As you have defined it as a primary key , you can only able to insert it once. Primary key is a unique key table and does not repeated.

You may use this script

CREATE TABLE IF NOT EXISTS `salary` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `E_ID` int(11) NOT NULL,
  `S_Amount` decimal(10,2) NOT NULL,
  `S_Date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Here S_Amount column is Decimal type as it is used to store salary. You can use integer also

Comments