How to relate two tables in a DBMS?

Question

I created two tables EMP and DEPT; now I want to relate these two tables. That is, one table's data will come to another table. How is it possible?

Answer 1

One department can have many employees; it's a classic one-to-many relationship.

Both tables should have primary keys; put a FOREIGN KEY in the employee table to point to its department.

Answer 2

Classically, the DEPT table will have a primary key column such as DeptNum. The EMP table will also contain a column DeptNum of the same type as DEPT.DeptNum. When you insert a record into EMP, you ensure that the DeptNum value matches correct department in DEPT.

You have the DBMS enforce this by declaring that EMP.DeptNum is a foreign key that references DEPT.DeptNum. Then the DBMS will

1. prevent you inserting or updating a value in EMP.DeptNum that doesn't match a value in DEPT.DeptNum, and
2. prevent you deleting or updating a value in DEPT.DeptNum that still has rows in EMP that use the department number.

Answer 3

duffymo and Jonathan Leffler gave you the solution.

In adition, if you want to see the work done, you could look to the "HR schema". HR schema(Human Resources) is usually installed as example when you install oracle, and you have en EMP table and a DEPT table related exactly as you want.

Answer 4

Either you create a foreign key constraint when you create the tables

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
  FOREIGN KEY (column1, column2, ... column_n)
  REFERENCES parent_table (column1, column2, ... column_n)
);

or Using an ALTER TABLE statement

e.g.

ALTER TABLE table_name
add CONSTRAINT constraint_name
  FOREIGN KEY (column1, column2, ... column_n)
  REFERENCES parent_table (column1, column2, ... column_n);

For example:

ALTER TABLE EMP
add CONSTRAINT fk_dept
  FOREIGN KEY (dept_id)
  REFERENCES dept(dept_id);

I assume since you've tagged this question with Oracle that you want the answer for Oracle. Other DB's may be a little different.

On reading your question again, are you also wanting to know how to link them together in a SQL query? If so, what you need is a join.

e.g. if there is a name and id column for both the emp and the dept tables then this will join them

select emp.name, dept.name from emp inner join dept where emp.id = dept.id

Is that what you're after?