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 |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2004-06-16 : 06:54:54
|
| Hi there,Parents=======parentID int pkchildID int fkChildren==============childID int pkThese are my requirements...1. Parents don't have to have a child. They can start off without one and then have one and then not have one. But they can only ever have one. If they don't then fk is set to null.2. Children can't exist without being referenced by a parent.3. More than one parent can reference the same child.4. When you delete a child, all parents which were referencing it have to have their fk set to null.5. When you delete a parent, if its the only parent referencing a child, then the child needs to be deleted.I thought that referential integritity would work here but it doesn't.For example, if you delete a parent and its the only parent of a child, the child should be deleted. However, the cascading deletes work the other way around - from pk->fk.Similarly, if one were to delete a child, the fk of the parent should be set to null, not that the parent should be deleted.Are triggers the only way to enforce these rules?Cheers,X-Factor. |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-06-16 : 08:08:34
|
| I do not see any way around a trigger.Only one child ….. is this a Chinese table? LOL JimUsers <> Logic |
 |
|
|
|
|
|
Inverted refrential integrity