1

enter image description here

Assuming that the EMP_SALARY_INFO table does not have any other relations apart from the EMPLOYEE table.

My question is there is no primary key for the EMP_SALARY_INFO table. There is only one foreign key. When I create entity instances for the EMP_SALARY_INFO table, my foreign key i.e., EMP_ID repeats. Is this valid or does it violate any relational database rule? If this repetition is a violation, would I have to create a unique id (primary key) for the EMP_SALARY_INFO table?

myverdict
  • 622
  • 8
  • 24
  • 1
    That's perfectly OK. As long as it solves the business needs, it's good. – The Impaler Oct 25 '19 at 18:37
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. – philipxy Oct 25 '19 at 19:45
  • As long as for some column set all rows are unique & not null, you can declare it a PK. And you should declare any UNIQUEs & any NOT NULLs. Are emp_sal_info (emp_id, f_date) & (emp_id, t_date) subrows unique & not null? Can an employee's date ranges overlap?--If not, those are candidates for PK & the other is UNIQUE. PS A FK constraint just says subrows appear elsewhere as PK/UNIQUE. – philipxy Oct 25 '19 at 20:06
  • By the way, you have some columns defined as `CHAR` when they should be `VARCHAR2`. [CHAR should be avoided.](https://stackoverflow.com/a/42165653/230471) – William Robertson Oct 26 '19 at 08:35

2 Answers2

1

That is perfectly OK. I would call out that in your example there is a possibility of having duplicate date ranges for the same employee. For example, if a mistake is made during data entry you may have two different pay rates for the same date range. Now, this may be a valid scenario in your case, however if it’s not then consider creating a unique constraint on Emp_id, from_date and till_date.

Dmitri M
  • 517
  • 6
  • 13
1

Foreign key of a particular table is a primary key of the table its referring to. Hence, in your case above as long as you have data in your empid of employee and same data if referred from emp_salary_info wont cause any sort of problem even if it repeats in the emp_salary_info table as its not repeating in the table its referring to i.e. Employee

Himanshu
  • 3,830
  • 2
  • 10
  • 29