Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

It's Not All About The Keys Expand / Collapse
Author
Message
Posted Saturday, April 12, 2014 11:44 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 9:16 AM
Points: 6,784, Visits: 1,895
Comments posted to this topic are about the item It's Not All About The Keys

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #1561229
Posted Saturday, April 12, 2014 12:01 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 8,832, Visits: 9,389
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
Post #1561233
Posted Sunday, April 13, 2014 1:38 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:47 PM
Points: 1,025, Visits: 3,070
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.
Post #1561295
Posted Monday, April 14, 2014 12:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
Thank you, Andy for question.



---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1561329
Posted Monday, April 14, 2014 1:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:05 AM
Points: 2,842, Visits: 3,875
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
Post #1561343
Posted Monday, April 14, 2014 2:04 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 12:30 AM
Points: 684, Visits: 677
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)
Post #1561360
Posted Monday, April 14, 2014 2:52 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:50 AM
Points: 3,263, Visits: 1,286
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 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])


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...

Post #1561375
Posted Monday, April 14, 2014 2:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 3,959, Visits: 5,194
Good question, thanks Andy
almost 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.


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1561377
Posted Monday, April 14, 2014 3:53 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 612, Visits: 890
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 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])


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 Andy
almost 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?



Post #1561408
Posted Monday, April 14, 2014 6:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 1,821, Visits: 2,173
Ugh, Monday and not enough coffee yet. Thanks for the question.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Post #1561445
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse