October 25, 2013 at 6:12 am
Today i was trying to delete one entry from my table,but i was getting following error
The query processor ran out of stack space during query optimization. Please simplify the query
After limit search i found that recommend foreign key for any table is 253.
http://msdn.microsoft.com/en-us/library/ms175464.aspx
That means it will be good if i am creating 253 Foreign Key for any table.
I want to that what should i do if i am having more then 253 Foreign Key for any table and i am getting error during deleting it.I don' want to disable foreign keys during deleting it.
October 25, 2013 at 6:24 am
shahi.alokchandra (10/25/2013)
After limit search i found that recommend foreign key for any table is 253.http://msdn.microsoft.com/en-us/library/ms175464.aspx
That means it will be good if i am creating 253 Foreign Key for any table.
The documentation recommends to create no more than 253 FKs on a table.
Not to create 253 FKs on every table.
Why would you need so many FK on a single table?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 25, 2013 at 7:14 am
Koen Verbeeck (10/25/2013)
shahi.alokchandra (10/25/2013)
Why would you need so many FK on a single table?
Agreed. There may be a "small design issue" that needs to be addressed if you need 253 foreign keys on a single table.
Another point is that deleting from a table that has a FK reference to another is fine. The referential integrity problem arises when you try to delete from the table that's the parent side of that relationship.
October 25, 2013 at 11:23 am
I have one table for login Details
I am using this with each records to know that who has created any entry. and i am having more then 300 tables.
As i am deleting any records from login then i am getting this error.
i am having same scenario with another table too. suggest me what should i do in this case.
October 25, 2013 at 11:29 am
shahi.alokchandra (10/25/2013)
I am managing creation detail of each records due to some reason.so saving login with each entry.and i am having more then 300 tables in which i am referencing it.I am having same scenario with another table to. can you suggest me that what should i do in this case.
So what you're saying is that you have over 300 foreign keys referencing a single table's primary key, right? If you're trying to delete a row from the parent table with any foreign keys that reference it, you're not going to be able to do so because referential integrity would be lost. I don't mean for this to sound overly-theoretical, but it does matter. The solution I see here is to disable the login by updating a status so the user is unable to login.
October 25, 2013 at 11:51 am
I have few more question which is raise after getting replies.
1. Should i create such a huge number of foreign keys,that means 300+ tables having foreign key with single primary key of any table.
if your answer is yes
what if i want to delete that entry because it was created accidentally.how to delete it.
if no
then how to restrict to insert wrong data into reference table.
2. How to design database for such type of requirement.
3. What if having such huge fk and want to delete any data,which doesn't exists in any reference table.why i can't delete it.
4. will it make any performance issue?
October 25, 2013 at 12:15 pm
shahi.alokchandra (10/25/2013)
I have few more question which is raise after getting replies.1. Should i create such a huge foreign key on any table if having such type of requirement,that means if i am referring same table any other,so should i create foreign key.
if your answer is yes
what if i want to delete that entry because it was created accidentally.how to delete it.
if no
then how to restrict to insert wrong data into reference table.
2. How to design database for such type of requirement.
3. What if having such huge fk and want to delete any data,which doesn't exists in any reference table.why i can't delete it.
You talk about "a huge foreign key on any table". Maybe I'm misinterpreting what you're saying. Do you have 300+ tables with foreign keys that reference a single primary key? Or is it something different?
October 25, 2013 at 12:20 pm
Yes i am having 300+ tables which are referencing a single primary key of a table.
October 25, 2013 at 12:55 pm
Okay, this means that you don't have a single "huge foreign key" at all, but rather a lot of foreign keys to a primary key on a single table. This is good...relational databases are good at this and creating the foreign key constraints is a good idea.
Since you're defining the foreign keys, the constraint itself won't allow a child row to exist without a parent unless you have the foreign key column defined as nullable.
To delete a row from the parent table, you'll need to make sure no rows exist in any of the 300+ child tables. If you have any grandchild tables that are children of the child tables, you'll need to delete those rows first, and so on. I would suggest writing a stored procedure to do this instead of writing it multiple times.
I don't know exactly what you're tracking here and I don't want to assume too much, but if you're storing a login id on transactional rows to keep track of who created the row, you're definitely not going to want to delete all the child rows when you delete a login or user account. Instead, I'd suggest that you disable the account by using a status field of some kind.
October 25, 2013 at 1:03 pm
I am doing same. i am deleting an entry for which there is no row in its child table or in referencing table,though i am getting same error.
The query processor ran out of stack space during query optimization. Please simplify the query
October 25, 2013 at 1:05 pm
shahi.alokchandra (10/25/2013)
I am doing same. i am deleting an entry for which there is no row in its child table or in referencing table,though i am getting same error.The query processor ran out of stack space during query optimization. Please simplify the query
Okay, we're now to the point where you're going to need to post the procedure for anyone to have a chance at seeing the problem.
October 25, 2013 at 1:24 pm
It is very simple procedure..
Suppose I am having
Table LoginMain(LoginNo int PK,LoginName varchar(300))
TransactionMain(TransNo Pk,LoginNo int FK of LoginMAin)
TransactionMain2(TransNo Pk,LoginNo int FK of LoginMAin)
TransactionMain3(TransNo Pk,LoginNo int FK of LoginMAin)
.
.
.
and so on.
Now i am deleting data from LoginMain.if there is any data exists in any of the transactionTables then i will get error due to foreign key,if there is no data for any loginNo in any of the transaction then it must be deleted.
So in my delete procedure of LoginMain
I have just written
Delete from loginMain where LoginNo=@LoginNo
where @LoginNo is parameter of procedure.
Here i don't think so that i must check whether it is exists in each 300+ table or not and then delete it.This record will be deleted if there is no child or referenced row is existing.
October 25, 2013 at 1:54 pm
Koen Verbeeck (10/25/2013)
The documentation recommends to create no more than 253 FKs on a table.Not to create 253 FKs on every table.
Well, going back to what Koen said, read the following:
Msg 8621: The query processor ran out of stack space during query optimization
The footnote says on the Capacity Specifications page says:
Although a table can contain an unlimited number of FOREIGN KEY constraints, the recommended maximum is 253. Depending on the hardware configuration hosting SQL Server, specifying additional FOREIGN KEY constraints may be expensive for the query optimizer to process.
It appears you've hit up against the upper limit of foreign key constrains to a single table. I was thinking it was the number of foreign keys in a single table. There are other posts discussing the issue and how it should be addressed in a future version. I don't know where to go from here, so hopefully someone else has an idea for dealing with it. It appears that MS does not.
October 25, 2013 at 2:44 pm
You should verify that each table that has a foreign key has an index on the foreign key column(s). When you do a delete, it will force a table scan of each referencing table that does not have an index on the foreign key.
The issue of having that many foreign key references to a single table is something I have never seen, so there may be some database design issues. However, they probably cannot be easily addressed in the short term.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply