[RESOLVED] Refer to same Id on table

[Greyskull]

Hello all,

I just have a quick question on sql, something that I thought was strange but it does exist.

I need to create a table which contains a filed that needs to refer to some other record on the same table based on the unique id.

I.e

EmployeeID [primary key, identity]
Employee_Name
WeeklyHours
Salary
ManagerID (this column with need to refer to the employeeId field but can be null)

How should I proceed on creating the managerId field?

Thanks in advance,

[si_the_geek]

The ManagerID field should be the same kind of data type as the EmployeeID field, but without the Primary Key and Identity properties set.

In terms of using it for queries etc, that is a little more complex. There is an example in the "SQL" section of the FAQs, but that is for SQL Server so may need tweaking to whatever database system you are using.

[Greyskull]

Thanks for the reply,

Yup I am using SQL server. My only concern with that idea is that say, if a new record is entered, someone could accidentally insert a managerid that can't be referenced.

Is possible if you could point me out to which FAQ as there is quite a few?

Thanks again,

[FunkyDexter]

someone could accidentally insert a managerid that can't be referenced

You just need to set it up as a foreign key and tell it to enforce integrity. If someone tries to enter a manager id that doesn't exist as an employee id it will throw an error.

[si_the_geek]

Is possible if you could point me out to which FAQ as there is quite a few?

The last article in the "SQL" section of our Database Development FAQs/Tutorials