September 2, 2004 at 1:15 pm
Is there any way to create a foreign-key within a table...For example, I have an employees table, and one of the fields is for the Employee's manager (who is also an employee)...basically I want a constraint to limit the manager field to existing employee IDs, however when I try to create a foreign key constraint, it throws the error:
"ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'FK_tblEmployees_tblEmployees'. The conflict occurred in database 'INTDB', table 'tblEmployees', column 'Employee_PK'."
Can this be done? (do i need to use a trigger? what is the best method?)
September 3, 2004 at 1:11 am
Self-referencing foreign key is supported (I used it, on MS SQL 2000). The syntax does not differ from the standard syntax for creating FK on a different table. based, on the error message in the post, I think that some FK-violating data is present in the tblEmployees table. Try detecting that using something like:
SELECT
Employee_PK, manager_id
FROM
tblEmployees AS E1
WHERE
NOT EXISTS (SELECT Employee_PK
FROM tblEmployees AS E2
WHERE (E2.Employee_PK = E1.manager_id)
)
This will give a list of employees for which the manager is not in the employees table any more.
The use of trigger for this purpose is not recomended in general, although there may be situations where the trigger may be a better solution.
Regards,
Goce.
September 3, 2004 at 1:27 am
If it's still an option at this point, consider avoiding a self-referencing table altogether.
There's an alternative presented in the September issue of SQL Server Professional and in the free e-book at this URL...
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
September 3, 2004 at 2:33 am
Hi Aaron and Wingenious,
g_smilevski has given excellent suggestions regarding your question. There's nothing I need to add to that. I do, however, want to react to Wingenious post in which he/she states that self-referencing tables (SRT from now on) should be avoided alltogether. I've read the free book which is mentioned in the post and saw that the argument for not using SRT's is that this can cause problems with automated database analysis tools.
I think that the primary purpose of a data model should be that it is an as adequate and as efficient possible mirror of the real life data it is representing. I've never seen a database who's primary purpose was "to be analyzed (on the meta level) by automated tools" (which , by the way, are not very sophisticated if they end up in endless loops when they encounter a SRT). The proposed alternative to a self referencing employee table like employee(employee_id, manager_employee_id, rest of columns...) with three tables models another reality and has a far more serious flaw:
employee(employee_id, rest of columns...)
manager(employee_id (I sincerely hope that the rest of the columns are not also duplicated) )
position(employee_id, manager_employee_id, some columns describing the supervising relation)
position models a n to n relation between employees and managers, so this model represents a network organization structure which is fundamentally different from a hierarchical organization structure. This is not a bad thing, but simply a different situation.
A major flaw however is the manager table which contains redundant records for each employee that has supervising rights. One of the goals of a good logical data model is to reducy data redundancy as much as possible. Think of the triggers that must be written to keep both tables identical, and how must a delete from the managers table interpreted? Should the employee also be removed or is it merely revoking supervising rights? It is also hard to check that every employee is managed by at least one manager (except the CEO), whilst this is simply a matter of making the manager_employee_id column nullable and defining a unique constraint on it in the RST solution.
A SRT can be a very effective and elegant solution, depending on the situation.
Cheers,
Henk
September 3, 2004 at 12:24 pm
I don't know where Henk is coming from, but some of the statements seem rather strange.
The material I referenced does not contain any statement about the primary purpose of any database. A good architecture is not the primary purpose of a database, it's simply prudent.
The comment about the sophistication of an analysis tool seems disingenuous. If a tool follows the relationship trail between tables an SRT presents a roadblock. A tool can avoid an endless loop, but that leaves the analysis incomplete.
The quotation apparently attributed to the material I suggested does not appear in that material.
The suggested alternative to an SRT does not involve any redundant data. Is a foreign key considered redundant data?
The suggested alternative to an SRT is an equivalent hierarchical structure, but with additional flexibility.
The suggested alternative to an SRT does not require any triggers to enforce referential integrity.
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
September 5, 2004 at 3:57 am
Hi Wingenious,
I'll try to clarify the statements I made:
First: what I think I know about your opinion regarding database architecture comes from reading the pfd document http://www.wingenious.com/database.pdf. I assume that this is the free e-book you refered to in your first post. If this is not the e-book you were referringto, let me know.
The statement about the primary purpose was not taken from the material I found at http://www.wingenious.com, it refelects my personal opinion.
I agree with you that a good database architecture is not a goal but a means.
The point I was trying to make is that I consider the argument that a SRT solution is not good because a database analysis tool will fail not valid. And besides that, I still think that such a tool should be able to detect and handle criculair references well. Consider department - employee tables, where an employee always works for a department and a department is always managed by an employee. This requires two foreign keys and creates a circulair reference as well.
If I quoted the material in general and the alternate solution especially falsely, please let me know how it should be interpreted. After reading the solution again, I still think there is redundancy in it. Isn't it true that a supervisor is always also an employee? In that case the supervisor tabel is 100% redundant. If this is not the case: where do you leave the supervisors that are supervised by other supervisors?
You say the alternative solution is equivalent "with additional flexibility". This sounds nice, but I strongly oppose to "additional flexibility", as it allows the situation in which one employee has more than one supervisor. I want the model to be as rigid as possible within limits of the specifications. With a SRT the constraint that an employee can have only one supervisor is guarded by the datamodel, no additional checks are needed. In the database.pdf document you write yourself on page 10 in the paragraph "Normalization" that among the goals of normalization are ensuring data integrity and reducing redundancy. (This is for sure something on which we do agree). If flexibility was the only goal, I'd model all tabels as "create myFlexibleTable(myFlexibleData text null)". No disrespect intended, but this is the ultimate consequence of adding unspecified additional flexibility.
If the suggested alternative doesn't require additional triggers, how is the constraint "an employee can have only one supervisor" enforced?
Your reaction will be highly appreciated. It looks like we both have strong opinions, let's use them to have a firm, professional and reasonable discussion from which we both - and maybe others - may learn something.
Cheers,
Henk (from Amsterdam, the Netherlands, by the way)
September 5, 2004 at 4:56 pm
Henk,
I'm glad that you would like to have a professional and reasonable discussion. I don't think your first post came across that way.
When writing a quotation the phrasing is not open to interpretation. By definition the phrasing is verbatim.
An analysis tool might probe the database hierarchy recursively by following parent/child relationships. What would you suggest such a tool do when it encounters an SRT? Should it be susceptible to an endless loop, should it stop the probing and ignore a chunk of the hierarchy, or do you have another option to propose? How many layers deep in the hierarchy is a normal child table of an SRT? I think the answer would vary by row.
In the suggested alternative the Supervisor table could be replaced with a Department table. The two meanings could be considered very similar.
The Supervisor table in the suggested alternative contains no redundant data. It contains an employee foreign key.
An SRT, with a tidy one employee, one position, one supervisor structure, may work in a small organization. A larger organization needs more flexibility. I used to have a single position with two supervisors. It also seems plausible that a single employee could have two positions within the same organization. I think those situations would require redundant data with an SRT.
Your last post seems to refer twice to specifications. I may be overlooking them, but I do not see any specifications in the original post for this topic. Are you assuming specifications that match your experiences?
If appropriate, the suggested alternative could enforce one supervisor per employee with a unique constraint on the employee foreign key in the Position table.
The suggested alternative makes it more involved to have the database enforce that an employee does not supervise himself/herself. In my opinion, that's a very small price to pay to avoid the problems associated with an SRT.
Wingenious
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply