Duplicate Foreign Key
-
WayneS (10/23/2012)
table can contain unlimited number of duplicate foreign keys, though the recommended maximum is 253
So, we're now recommending building duplicate foreign keys?
Personally, I thought that this is essentially a useless question. Interesting, sure. Useful? Nope.
I think it was useful to know there is no limit to the number of foreign keys on a table, even though there is a recommended limit.
I take it that the code used duplicate keys so that you can add infinite keys to the table to test for this, not because it's recommended to have duplicate keys.
-
Primo Dang (10/23/2012)
WayneS (10/23/2012)
table can contain unlimited number of duplicate foreign keys, though the recommended maximum is 253
So, we're now recommending building duplicate foreign keys?
Personally, I thought that this is essentially a useless question. Interesting, sure. Useful? Nope.
I think it was useful to know there is no limit to the number of foreign keys on a table, even though there is a recommended limit.
I take it that the code used duplicate keys so that you can add infinite keys to the table to test for this, not because it's recommended to have duplicate keys.
+1
Thru this QOTD, I was just trying to share the fact that we can have infinite duplicate FK's. Definitly, what sense does it make to create duplicate keys.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
The question asks how many duplicate keys will be created, not how many can be created. No one can truly have an infinite number of anything. Resources will run out at some point.
The number of duplicate keys that will be created depends on the environment and its resources. So a correct answer would be "it depends".
Sorry for being picky. 🙂
-
OCTom (10/23/2012)
The question asks how many duplicate keys will be created, not how many can be created. No one can truly have an infinite number of anything. Resources will run out at some point.The number of duplicate keys that will be created depends on the environment and its resources. So a correct answer would be "it depends".
Sorry for being picky. 🙂
Since "it depends" is not in the list of specific answers, it falls into "none of the above" -- and the circle of pickiness is complete.
I answered "none of the above" in the hopes that SQL Server was smart enough to catch a duplicate foreign key and disallow it. If it were, the correct answer would have been 0 -- because there would be 1 foreign key, and no duplicates.
Unfortunately, SQL Server sometimes, as in this case, allows developers to do extremely stupid things, so I'll have to make sure not to accidentally script my foreign keys in dynamic SQL within a loop with the counter referenced by the dynamic SQL. Somehow I think I'll manage to do that... :crazy:
-
You can have as many 'duplicate foreign keys' as there are rows in the table. It is just a value, after all. It appears that you were actually talking about foreign key *constraints*, which is a very different thing. You might think this to be 'picky', but we *must* be 'picky', in our profession.
-
vk-kirov (10/23/2012)
call.copse (10/23/2012)
I liked this question, as I wondered whether anyone would try running the sample code, and what would happen if they did (and I got it right comfortably!). Would you get to some kind of out of resource type scenario do you think?After answering the question, I ran the sample code. After 10 minutes I canceled it and I got a table with 5679 foreign keys. After that, I tried to insert a record into that table: "INSERT INTO childTbl (parent_id) VALUES (1)". SQL Server was "thinking" for 22 minutes and failed with the following message:
[font="Courier New"]Msg 8621, Level 17, State 1, Line 1
The query processor ran out of stack space during query optimization. Please simplify the query.[/font]
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Nice question!
Igor Micev,My blog: www.igormicev.com
-
Okay, I've officially got too much time on my hands with this vacation thingy. I ran the following modified code on my laptop (8GB RAM, SQL 2008 Express) :
-- Creating parentTbl
CREATE TABLE parentTbl
( id INT IDENTITY UNIQUE
);
-- Creating childTbl
CREATE TABLE childTbl
( child_id INT IDENTITY UNIQUE
, parent_id INT
);
INSERT INTO parentTbl DEFAULT VALUES;
DECLARE @fk VARCHAR(10),
@i INT = 1;
-- start the timer
PRINT '0|' + convert(varchar,getdate(),121);
INSERT INTO childTbl (parent_id) VALUES (1);
-- Infinite while loop to create duplicate foreign keys
WHILE ( 1 = 1 )
BEGIN
-- time to reach this iteration
PRINT cast(@i as varchar) + '|' + convert(varchar,getdate(),121);
SET @fk='FK' + Cast(@i AS CHAR(5));
EXEC ('ALTER TABLE childTbl ADD CONSTRAINT '+@fk+' FOREIGN KEY (parent_id) REFERENCES parentTbl(id)');
SET @i+=1;
TRUNCATE TABLE childTbl;
INSERT INTO childTbl (parent_id) VALUES (1);
END;
DROP TABLE childTbl;
DROP TABLE parentTbl;
then collated the results. It took a total of 1 hour 16 minutes to get up to 822 foreign key constraints before failing on the insert. The first iteration took 10ms. It took 222 constraints to bring that up above half a second. By 253 constraints, the running average was 716ms. The last 24 iterations (797-821 constraints) all took over 20 seconds each!
The curve was relatively smooth, even though I continued to use my laptop for other things.
-
Nice amusing question.
The number isn't actually unlimited, though - the constraint metadata takes up space in filestore, and the limit on filestore for SQLServer is something less than 17.2 billion terabytes, so eventually - if the code was left running for long enough and it didn't fail for some other reason - it would hit a filestore limit. But not that limit: all the metadata is in one database, so there can't be more than 524,272 Tbytes of it - so it would hit the database size limit and never get to the SQL:Server max filestore limit. But the code provided won't go anything like that far.
Here's why this code will never get anywhere that limit: it is written so that it can only create 99999 foreign keys. After 99999 it will try (and of course fail) to create a foreign key with an illegal character ('*') in its name because it uses cast(@i as char(5)) to build the name, and that will fail and return '**'. It will do that again and again all the way up to the limit of int @i. Once it hits that limit, it will still carry on doing the same thing, that '**' won't change because adding 1 to @i will fail, so the value of @i will remain at 2,147,483,647 in every subsequent iteration of the loop.
Tom
-
I actually thought the load would fail when trying to create a duplicate FK so I chose none of the above. I then ran the code on a test db just to see what would happen and stopped it after a couple of minutes. It created 2579 duplicates. I think I would rather have the Oracle behavior of not allowing dupes.
Thanks for the question.
-
Good One!:-):-)
-
nice question..... Thanks....
*************Manik*******
Manik
You cannot get to the top by sitting on your bottom.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply