RE: Foreign Key

When a table makes a reference to itself, it is called a self join. I agree
that this is not as common as a foreign key referencing a different table,
but it is quite common in the big scheme of things.

From the example code you gave, this actually makes sense. Here is a
scenario for you. Bill is employee ID 10. He is a manager. Sue (employee
ID 17), Anna, (employee ID 23) and Jack, (employee ID 64) are all employees
who are managed by Bill. In this situation, they would have their manager ID
field value set to 10.

You could store manager records in another table, but there is no need. By
using the self join, we can store all the data in one table while still
keeping the A works for B relation intact. To view all employees who work
for a certian manager (Bill, employee ID 10 for example), use the following:

SELECT first_name, last_name
FROM employees
WHERE manager_id = 10;

To see a list of all employees along with the last name of the manager they
work for, use this:

SELECT e.first_name, e.last_name, m.last_name AS "Manager"
FROM employees e, employees m
WHERE e.manager_id = m.employee_id;

Notice how I used the same table name twice in the FROM clause. I have to
give table aliases to both copies. I need to use those aliases with all
columns in the SELECT clause because those columns exist in both copies of
the employees table.

I hope this was helpful.

"hangaround" wrote:

Given a table as following
/////////////////////////////
Prompt ****** Creating EMPLOYEES table ....

CREATE TABLE employees
( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
, phone_number VARCHAR2(20)
, hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
, CONSTRAINT emp_salary_min
CHECK (salary > 0)
, CONSTRAINT emp_email_uk
UNIQUE (email)
) ;

CREATE UNIQUE INDEX emp_emp_id_pk
ON employees (employee_id) ;


ALTER TABLE employees
ADD ( CONSTRAINT emp_emp_id_pk
PRIMARY KEY (employee_id)
, CONSTRAINT emp_dept_fk
FOREIGN KEY (department_id)
REFERENCES departments
, CONSTRAINT emp_job_fk
FOREIGN KEY (job_id)
REFERENCES jobs (job_id)
, CONSTRAINT emp_manager_fk
FOREIGN KEY (manager_id)
REFERENCES employees
) ;
...
/////////////////////////////
I dont know when should a table reference the column of itself, and what's
the purpose?
.

Relevant Pages

  • Re: New to SQL server
    ... [CONSTRAINT constraint_name] ... | [FOREIGN KEY] ... Is the name of the database in which the table is created. ... REFERENCES permission on the type is ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Foreign Key Problem errno 150
    ... CONSTRAINT `FK_tblstammblatt_aufsteck` FOREIGN KEY REFERENCES ... So interpretiere ich die Anleitung. ...
    (de.comp.datenbanken.mysql)
  • Problem merging two queries
    ... CONSTRAINT PRIMARY KEY CLUSTERED ... REFERENCES ( ... CONSTRAINT FOREIGN KEY ...
    (microsoft.public.sqlserver.programming)
  • Re: Modelling Disjoint Subtypes
    ... the only constraints one should have are foreign key references. ... I think the answer is that a foreign key constraint enforces a constraint between columns in two tables. ... these tables also have a primary key K which references K as a foreign key. ...
    (comp.databases.theory)
  • Re: Updating from PointBase to Derby
    ... CONSTRAINT pk_location PRIMARY KEY, description varchar)"); ... varchar, skill varchar, CONSTRAINT fk_applicant FOREIGN KEY ... REFERENCES Applicant, CONSTRAINT fk_skill FOREIGN ...
    (comp.lang.java.help)