Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shapper
Constraint Violating Yak Guru
450 Posts |
 Posted - 2014-07-18 : 10:32:33
|
Hello,I have a list of Employers and Workers. I need to register the projects an Worker has made for the different Employers but I also need to know the current Worker's Employer. I have: create table dbo.Employers ( Id int identity not null Name nvarchar not null ); create table dbo.Workers ( Id int identity not null CurrentEmployerId int not null, -- FK Name nvarchar (120) not null, ); create table dbo.Projects ( Id int identity not null WorkerId int not null, -- FK EmployerToWhichTheProjectWasMadeId int not null, -- FK Name nvarchar (120) not null, ); Does this not create a circular reference?I tried to use On Delete Cascade and On Update Cascade and I get an error ...Thank You,Miguel |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-18 : 11:35:57
|
| I would set this up a little different from what you have. I would have 3 tables to being with - Employers, Workers, and Projects. But those tables would only have information about that entity itself. For example, Workers table would have their Id, Name, and perhaps address or other pertinent information that intrinsically belongs to the worker. I would not put the CurrentEmployerId into the Worker table.Then, I would have additional tables - example, ProjectWorkers table - where there would be columns for the project_id, worker_id, and perhaps other information that is relevant to project and workers taken together; for instance, startdate and enddate to indicate when a given worker started on a project and when they ended work on it. Similarly I would have a ProjectEmployer table (assuming a project can have multiple employers or multiple employers can share the same project).Didn't quite get what you meant about cascade delete. Even though many people find it very useful, I usually don't use cascade delete - mainly because when I delete something, I want it to be me who explicitly deletes it. |
 |
|
|
|
|
|
|
|
Circular Reference and Cascade