December 25, 2010 at 12:32 am
Dear All,
Scenario:
I have an existing procedure SP1 which would delete records from tableA conditionally.
Now, someone from my team references tableA's field from tableB.
Now my SP1 is failing when deleting from tableA because of this new Foreign key reference.
Question:
How can we avoid this while development. Here in my case, is there a way to get intimated that SP1 may fail because of this new FK reference ?
December 25, 2010 at 3:35 pm
I don't now the structure of the tables and in what conditions are Foreign keys with Primary keys, I think you should enable ON DELETE CASCADE during the relationship between tables, during the definition of your foreign key!
December 25, 2010 at 11:34 pm
THANKS FOR YOUR REPLY..
My Question in more detail:
-------------------- Task 1 ----------------------
--[tEmployee]
CREATE TABLE [dbo].[tEmployee]([empNo] [int] NOT NULL,[empName] [varchar](50) NULL,
CONSTRAINT [PK_tEmployee] PRIMARY KEY CLUSTERED ([empNo] ASC)
--Stored Procedure 'X'
DELETE FROM [dbo].[tEmployee] WHERE empNo = 123
-------------------- Task 1 ----------------------
-------------------- Task 2 ----------------------
--[tEmpLibrary]
CREATE TABLE [dbo].[tEmpLibrary]([BookNo] [int] NULL,[EmpNo] [int] NULL) ON [PRIMARY]
ALTER TABLE [dbo].[tEmpLibrary] WITH CHECK ADD CONSTRAINT [FK_tEmpLibrary_tEmployee] FOREIGN KEY([EmpNo])
REFERENCES [dbo].[tEmployee] ([empNo])
ALTER TABLE [dbo].[tEmpLibrary] CHECK CONSTRAINT [FK_tEmpLibrary_tEmployee]
-------------------- Task 2 ----------------------
I did "Task 1". It was working fine.
someone did "Task2". Then after "DELETION" of 'Task-1' is failing, obviously because of this new FK reference from 'Task 2'.
My Question: Is there a way to avoid this while doing 'Task 2's DEVELOPMENT time itself.
And regarding your suggestion of 'ON CASCADE DELETE', i should not delete the foreign key's (child table's) row blindly. i will change the empno of tEmpLibrary table before deletion based on some business.
December 25, 2010 at 11:45 pm
simply i can say, that i am expecting a message like
"procedure X would fail as u didnt specify delete rule in your new FK reference"
December 27, 2010 at 1:01 am
If you do not want error message for violating foreign key and do not want to use ON DELETE CASCADE, in Task 1 you should handle the case when there are child records (try-catch or explicit check for child records).
In sql2005 and up there are also ON DELETE SET NULL and ON DELETE SET DEFAULT that also might be handy.
In Task 2 do not forget to create INDEX on foreign key column to avoid performance problems. During delete of parent, db searches for child rows with full-table-scan if there is no index on FK columns, and for big tables it can take a LOT of time (your delete of one parent row will perform a FTS on child table if there is no index).
December 27, 2010 at 2:30 am
All child references must go away before you attempt to delete the record.
In this case, the records of Table B must be updated to NULL before removing data from Table A.
You can have a INSTEAD OF DELETE trigger on table A that would first go out and delete/NULL out the child table references.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
December 27, 2010 at 10:35 pm
Thanks Guys.
My question was more on "how to avoid it" rather than "how to fix it"
I knew, that without handling the reference we cant delete the main table's row.
If you notice my question, Task1 was done by X person and Tas2 was by Y person.
I am expecting somehow to avoid this,
because SQL Server knows that there is already a DELETE statement on this table in some procedure and now person Y creates a FK without 'DELETE RULES'. Then obviously you can say for sure, a bug exists. correct?
December 28, 2010 at 12:55 am
No. It's not a bug to create a table with FK, it is very good, and very recommended and anything else would be "bug".
The sole purpose of constraints is to guarantee that data meets certain criteria by not allowing you to break that criteria and informing you if you try so with descriptive error.
While making your "delete" procedure you should be aware that there will be created many child tables (child means that there are foreign keys that reference "your" table) without delete rule,
and decide will you let DBMS to throw "foreign key is violated" errors, or do something else - you really can do what ever you want to.
What do you really want it to do ?
If you want to prohibit your colleague to create FK, it can be done via DDL trigger. But that would be bad, wrong and pervert thing to do. You better make your procedure do what you want it to do, even with that "evil" FK's that reference your table 🙂
December 28, 2010 at 2:25 am
1. I agree FK is good and its importance.
2. I am aware that there would be child tables which may refer mine. but that would be in FUTURE. In my example, as there is no child table reference when 'Task-1' held. so, there is nothing to handle the child table before my deletion of main table. That's why i expect the SQL should warn person Y, when he does 'Task-2' that 'Task-1' may fail. Because person Y doesnt know of this 'Task-1'. In case, SQL intimate person Y abt this, Person Y may handle the 'Task-1' by adding some scripts in 'Task-1' to delete the corresponding row of CHILD table before the MAIN table row is deleted.
or
person Y may inform person X, that his deletion of main table code may break because of this new FK. So that person X would handle it.
hope you went through my question explanations in my previous posts.
Thank you very much
December 28, 2010 at 2:58 pm
I have not seen anything in SQL Server that will identify for a developer the code that will break when creating a foreign key reference on a table. SQL Server cannot identify all the locations that issue a command on the table that could possibly fail. It is up to the developer to understand what the foreign key reference will do, and to find the places that would be impacted when creating the reference. Deletes are not the only potential problem; updates to the primary key or unique index could also affect the reference.
The same is true for other types of changes. For example, when renaming or dropping a table or stored procedure, SQL Server will warn the developer that the change may impact other areas, but it does not identifiy what those areas are.
These changes must be done by the developer, not by SQL Server. The compiler does not understand the logic behind the DML statements; it can only determine that it is syntactically correct and that the necessary assets are present. Even there, with the new two-pass compile (rather than the older three-pass compile), some statments will not be evaluated until runtime, so errors on unavailable assets will not be found until attempting to run the procedure.
I'm sure you understand the process outlined here, but I thought I would reiterate it. The developer wanting to add the foreign key must understand the system well enough to ensure that nothing breaks. He needs to search the exising codebase to see where deletes and updates are being done, both in SQL Server assets as well as external commands issued to the database by applications. Those places must be altered to check for the existence of child records, and either delete or update them before making changes to the parent. Then rigorous regression testing should be performed to ensure that all needed changes have been made. Only then should the new foreign key constraint be moved to a production environment.
December 28, 2010 at 3:31 pm
My understanding is that there are multiple developers writing "CRUD" procedures, and you guys are having a hard time keeping on the same page regarding what foreign key relationships are in place. If it helps, you can use the following queries to document what foreign key relationships currently exist and also what objects have a dependency on another object.
-- Query foreign key relationships:
SELECT
FK.TABLE_SCHEMA AS child_table_schema,
FK.TABLE_NAME AS child_table_name,
PK.TABLE_SCHEMA AS parent_table_schema,
PK.TABLE_NAME AS parent_table_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON RC.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA AND
RC.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA AND
RC.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
order by
child_table_name;
-- Query objects that have an explicit dependency on a referenced object:
SELECT
left(OBJECT_NAME(object_id),40) AS referencing_object_name,
sum(cast(is_selected as int)) count_selects,
sum(cast(is_select_all as int)) count_select_all,
sum(cast(is_updated as int)) count_updates
FROM sys.sql_dependencies
WHERE referenced_major_id = OBJECT_ID('HumanResources.Employee')
group by OBJECT_NAME(object_id)
ORDER BY OBJECT_NAME(object_id);
Notice how the result from the 2nd query indicates wether a stored procedure selects from or insert/updates/deletes from the referenced object. Also, the [count_select_all] column should indicate if the procedure is doing a SELECT *, which another type of dependency issue you'd want to nip in the bud. I believe the sys.sql_dependencies table is maintained whenever an object is compiled, so DML statements executed dynamically probably won't show up.
referencing_object_name count_selects count_select_all count_updates
---------------------------------------- ------------- ---------------- -------------
CK_Employee_BirthDate 0 0 0
CK_Employee_Gender 0 0 0
CK_Employee_HireDate 0 0 0
CK_Employee_MaritalStatus 0 0 0
CK_Employee_SickLeaveHours 0 0 0
CK_Employee_VacationHours 0 0 0
uEmployee 0 0 1
ufnGetContactInformation 2 0 0
uspGetEmployeeManagers 4 0 0
uspGetManagerEmployees 3 0 0
uspUpdateEmployeeHireInfo 0 0 3
uspUpdateEmployeeLogin 0 0 5
uspUpdateEmployeePersonalInfo 0 0 4
vEmployee 3 0 0
vEmployeeDepartment 3 0 0
vEmployeeDepartmentHistory 2 0 0
vSalesPerson 3 0 0
vSalesPersonSalesByFiscalYears 3 0 0
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 28, 2010 at 10:42 pm
Thanks for your detailed answers.
As you said, If such warning is not available in SQL Server, then It's fine. No Issues. I should go with that.
But I don't agree that this cannot be achievable by SQL Server.
As a developer, if I could identify the problem just by seeing/searching the code (as you said) itself even without any testing, why can't the SQL? As this involves some syntactical and machine-achievable-logical verification and not the human logical analysis.
It's the matter of verifying logic like "Deleting/Updating main table" Vs "FK without DELETE/UPDATE Rules"
Thanks for your patience.
December 28, 2010 at 11:06 pm
Thanks for your detailed answers.
As you said, If such warning is not available in SQL Server, then It's fine. No Issues. I should go with that.
But I don't agree that this cannot be achievable by SQL Server.
As a developer, if I could identify the problem just by seeing/searching the code (as you said) itself even without any testing, why can't the SQL? As this involves some syntactical and machine-achievable-logical verification and not the human logical analysis.
It's the matter of verifying logic like "Deleting/Updating main table" Vs "FK without DELETE/UPDATE Rules"
Thanks for your patience.
December 28, 2010 at 11:10 pm
To the comment that SQL Server should be able to find these relationships: I'm sure it would be possible to write a parsing engine to determine what each script is doing and warn the developer. Finding those relationships in code that builds a statement and executes it would be much more difficult:
EXEC ('DELETE FROM ' + @TableName) -- for example
Finding those statements in C# code or JavaScript or PHP code would be even more difficult for the SQL Server. No matter how much work SQL Server did, there could still be cases where external SQL statements are executed that could cause a problem.
December 28, 2010 at 11:29 pm
Yes. I agree that this cannot be 100% achievable by SQL as this depends on the code from C# or other which is out of SQL scope.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply