Cascading referential integrity.
-
HI, I want to know that while creating cascading referential integrity constraint between tables having foreign key relationships. For e.g:- I have two tables Employee and EmployeeLeave,now i want to use cascading referential integrity constraint code:- Alter Table dbo.EmployeeLeave Add Constraint rfkcEmployeeID Foreign Key(EmployeeID) References dbo.Employee(EmployeeID) ON DELETE NO ACTION ON UPDATE NO ACTION So, My question is that i can even use this Constraint at the time of Creating Table For e.g:- Create Table dbo.EmployeeLeave ( EmployeeID int constraint rfkcEmployeeID Foreign Key(EmployeeID)References dbo.Employee(EmployeeID) ON DELETE NO ACTION ON UPDATE NO ACTION ) Is this code is correct??? Is it will work??? Thanks...
-
HI, I want to know that while creating cascading referential integrity constraint between tables having foreign key relationships. For e.g:- I have two tables Employee and EmployeeLeave,now i want to use cascading referential integrity constraint code:- Alter Table dbo.EmployeeLeave Add Constraint rfkcEmployeeID Foreign Key(EmployeeID) References dbo.Employee(EmployeeID) ON DELETE NO ACTION ON UPDATE NO ACTION So, My question is that i can even use this Constraint at the time of Creating Table For e.g:- Create Table dbo.EmployeeLeave ( EmployeeID int constraint rfkcEmployeeID Foreign Key(EmployeeID)References dbo.Employee(EmployeeID) ON DELETE NO ACTION ON UPDATE NO ACTION ) Is this code is correct??? Is it will work??? Thanks...
-
HI, I want to know that while creating cascading referential integrity constraint between tables having foreign key relationships. For e.g:- I have two tables Employee and EmployeeLeave,now i want to use cascading referential integrity constraint code:- Alter Table dbo.EmployeeLeave Add Constraint rfkcEmployeeID Foreign Key(EmployeeID) References dbo.Employee(EmployeeID) ON DELETE NO ACTION ON UPDATE NO ACTION So, My question is that i can even use this Constraint at the time of Creating Table For e.g:- Create Table dbo.EmployeeLeave ( EmployeeID int constraint rfkcEmployeeID Foreign Key(EmployeeID)References dbo.Employee(EmployeeID) ON DELETE NO ACTION ON UPDATE NO ACTION ) Is this code is correct??? Is it will work??? Thanks...
Member 11161625 wrote:
Is this code is correct???
Syntactic it is correct, but it will do nothing, as you instructed it. That is the default behaviour. If you want the "leave" days to be removed from the database when the employee is removed from the database, then you'd want a cascading delete. (ON DELETE CASCADE, not "NO ACTION"). That way you can prevent getting "orphaned" records. If you still need the leave-data from that person and don't want to wipe the records, I'd recommend "ON DELETE SET NULL", since the key would have become invalid - but then the record isn't deleted, just the key is removed - handy if you need the leave-data for statistics or other stuff. Last, there is also a "SET DEFAULT" option; if a manager is deleted from the database, then you might want to automatically update all affected workers to some default-manager.
Member 11161625 wrote:
Is it will work???
Depends on the specs. Press F5 and see if it does what you expect. Disclaimer; various database-engines may use different keywords and not support them all.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)