April 12, 2014 at 11:44 am
Comments posted to this topic are about the item It's Not All About The Keys
April 12, 2014 at 12:01 pm
A fairly nice question. I saw that this was just the question of a week or so ago in a new disguise, so picked the answer that you have labelled correct.
Unfortunately it's not really altogether clear that the right answer is the only one that could be right - for all we know this databases object isn't a view on or alias for the system view with the same name, it could be a table created specially for this project and 'name' might not be a key for this table, there could be duplicates. This may lead to some people getting it wrong (or, rather, picking the possibly correct answer that you haven't labelled correct). All the guff in the question about 'name' being a key seems to refer to the table representing the backuplist, not to this databases object. Of course the fact that one (preumably inner) join returnned the correct number 40 while the left join returned no rows with null for the rhs means that there were exactly one row for each value of name in the backuplist, but the afformentioned guss soewhat distracts ones attention from that - I imagine that was not intentional.
Tom
April 13, 2014 at 1:38 pm
The concept of putting the foreign key on databases didn't make sense to me.
Wouldn't it be make more sense to put the foreign key on the backuplist rather than databases? That way you would stop databases being added to the backuplist that don't exist and you could cascade deletes to remove databases from the backuplist if they are dropped.
Also, if the foreign key was able to be created, there was also the potential that there was a name in the databases that was not in the backuplist. That was not checked for.
April 14, 2014 at 12:21 am
Thank you, Andy for question.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 14, 2014 at 1:10 am
🙁 I had guessed that this was a question about case sensitivity and "duplicate" rows that only differ in case (e.g. Master vs. master). I also assumed the table "databases" would exist.
Stupid assumptions when I re-think about it ;-).
Thanks for the question.
Best Regards,
Chris Büttner
April 14, 2014 at 2:04 am
The logic behind the question suggest that the `backuplist` is subset of `databases`, and that FK should be created on `backuplist` to reference `databases`. Check query seems to support that, because it checks whatever all rows from `backuplist` have a match in `databases`
select * from backuplist d left join databases b on d.name = b.name where b.name is null
Following query tries to create FK in opposite direction, on object `databases` which previous check query has nothing to do with.
ALTER TABLE [dbo].[databases] WITH CHECK ADD CONSTRAINT [FK_backlist_backuplist] FOREIGN KEY([name]) REFERENCES [dbo].[backuplist] ([name])
So my vote was for wrong alter command because it should be
ALTER TABLE [dbo].[backuplist] WITH CHECK ADD CONSTRAINT [FK_backlist_backuplist] FOREIGN KEY([name]) REFERENCES [dbo].[databases] ([name])
which could in fact fail because there is no PK on `databases (name)`..
If original alter was correct and FK should be on `databases`, then the check query is not correct. There could be a rows in `backuplist` not existing in `databases` which would cause a fail and that was not checked.
Also other quite possible reason for either fail is that column data types does not match between the two tables. Varchar(10) and Varchar(20) can join but FK creating would fail.
_______________________________________________
www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)
April 14, 2014 at 2:52 am
nenad-zivkovic (4/14/2014)
The logic behind the question suggest that the `backuplist` is subset of `databases`, and that FK should be created on `backuplist` to reference `databases`. Check query seems to support that, because it checks whatever all rows from `backuplist` have a match in `databases`
select * from backuplist d left join databases b on d.name = b.name where b.name is nullFollowing query tries to create FK in opposite direction, on object `databases` which previous check query has nothing to do with.
ALTER TABLE [dbo].[databases] WITH CHECK ADD CONSTRAINT [FK_backlist_backuplist] FOREIGN KEY([name]) REFERENCES [dbo].[backuplist] ([name])
I agree with this, but my answer is that the check query is incorrect. If you are wanting to create a FK on databases (even without knowing if it is a table or a view), you would go and check if all records in databases have a match in backuplist, not the opposite.
So based on your requirements, the FK is incorrect and should be on the backuplist table. Based on you FK on databases, your check query should do a left join on databases.
Nowhere there was a mention of the creation of a synonym called databased. This is an assumption. So you could also assume that there was a table created called databases...
April 14, 2014 at 2:59 am
This was removed by the editor as SPAM
April 14, 2014 at 3:53 am
Mighty (4/14/2014)
nenad-zivkovic (4/14/2014)
The logic behind the question suggest that the `backuplist` is subset of `databases`, and that FK should be created on `backuplist` to reference `databases`. Check query seems to support that, because it checks whatever all rows from `backuplist` have a match in `databases`
select * from backuplist d left join databases b on d.name = b.name where b.name is nullFollowing query tries to create FK in opposite direction, on object `databases` which previous check query has nothing to do with.
ALTER TABLE [dbo].[databases] WITH CHECK ADD CONSTRAINT [FK_backlist_backuplist] FOREIGN KEY([name]) REFERENCES [dbo].[backuplist] ([name])I agree with this, but my answer is that the check query is incorrect. If you are wanting to create a FK on databases (even without knowing if it is a table or a view), you would go and check if all records in databases have a match in backuplist, not the opposite.
So based on your requirements, the FK is incorrect and should be on the backuplist table. Based on you FK on databases, your check query should do a left join on databases.
Nowhere there was a mention of the creation of a synonym called databased. This is an assumption. So you could also assume that there was a table created called databases...
Completely agree with that.
I'm not sure about this other comment though.
Stewart "Arturius" Campbell (4/14/2014)
Good question, thanks Andyalmost tripped up, but remembered that databases is a system view, in the sys schema, not dbo.
The only way to reflect it in the dbo schema is with either a view or synonym, against which a foreign key cannot be created.
I can execute create table dbo.databases (name sysname) with no errors, so why we have to figure out that it is a synonym? Even more when the question specifies all objects are part of the [dbo] schema?
April 14, 2014 at 7:03 am
mickyT (4/13/2014)
The concept of putting the foreign key on databases didn't make sense to me.Wouldn't it be make more sense to put the foreign key on the backuplist rather than databases? That way you would stop databases being added to the backuplist that don't exist and you could cascade deletes to remove databases from the backuplist if they are dropped.
Also, if the foreign key was able to be created, there was also the potential that there was a name in the databases that was not in the backuplist. That was not checked for.
I was wondering about this as well. :unsure:
April 14, 2014 at 9:00 am
greate question Andy.
Thanks for posting.
April 14, 2014 at 11:07 am
I'm glad I'm not the only one who saw that the foreign key and the data check were going in opposite directions and tried to answer accordingly. I have nothing personally against Andy Warren, but his QotD entries the past couple of weeks have been borderline absurd.
April 14, 2014 at 11:38 am
ALTER TABLE [dbo].[databases]
WITH CHECK ADD CONSTRAINT [FK_backlist_backuplist]
FOREIGN KEY([name])
REFERENCES [dbo].[backuplist] ([name])
First, are we creating the [databases] table? There is no table by that name, but there is a system view called [sys].[databases].
2nd, you can't create a foreign key on a system view.
3rd, assuming there were a [databases] table, the foreign key would go on the [backuplist] table.
If you could see the error message the problem would be obvious
4th, Why would I not see the error message? Even a blind DBA can at least hear the error message.
I hope these questions don't reflect what will be on the updated MCSA exams. :w00t:
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 14, 2014 at 11:54 am
Stewart "Arturius" Campbell (4/14/2014)
Good question, thanks Andyalmost tripped up, but remembered that databases is a system view, in the sys schema, not dbo.
The only way to reflect it in the dbo schema is with either a view or synonym, against which a foreign key cannot be created.
+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
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply