InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 11:00:29 GMT20http://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspxI've always kept the FKs, tho I've seen systems work well without 'em (microsoft application).When you use the diagrams of SMSS, the FK will allow you to see the relations between tables and joins. If lose that if you drop them.Tue, 08 Feb 2011 15:08:22 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspx[quote][b]Ninja's_RGR'us (2/8/2011)[/b][hr]Yes you're right on the script part.Like I said I NEVER leave the keys out of the dev phase.And then I'm good enough at tuning that I don't need to take 'em. So I'd start there.One more thing to consider is that those FKS are self documenting the DB for you. It's much easier to hire a new guy and let him play with the db rather than explaining all the littles joins one at a time.[/quote]Thanks Ninja,I will just like to trouble a little more, I hope you will bear me while I try and solve the problem.[quote][b]"Like I said I NEVER leave the keys out of the dev phase. And then I'm good enough at tuning that I don't need to take 'em. So I'd start there." .[/quote]Do you mean that during your development process you always put constraints to ensure data integrity during development stages and Once it comes to tuning the database you decide whether to keep the constraint or remove it . [quote][b]One more thing to consider is that those FKS are self documenting the DB for you. It's much easier to hire a new guy and let him play with the db rather than explaining all the littles joins one at a time.[/quote]I could not understand what you mean by the above statement.Thanks ZubairTue, 08 Feb 2011 12:36:13 GMTzubairmasoodihttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspxYes you're right on the script part.Like I said I NEVER leave the keys out of the dev phase.And then I'm good enough at tuning that I don't need to take 'em. So I'd start there.One more thing to consider is that those FKS are self documenting the DB for you. It's much easier to hire a new guy and let him play with the db rather than explaining all the littles joins one at a time.Tue, 08 Feb 2011 12:05:50 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspxhiI think probably you mean the sequence in which the script will be executed .and hence you are concerned about ALTER TABLE [dbo].[UserInfo] WITH CHECK ADD CONSTRAINT [FK_UserInfo_Country] FOREIGN KEY([CountryID])REFERENCES [dbo].[Country] ([CountryID])when i have not as yet created the Country table.If that is your concern, I have just generated this script from two table , one by one to just show you.Correct me if i am wrongThank you so much for all your time and effort. So i have now decided to go ahead with this relationship. and yes i have many more such relations in UserInfo with other tables and i will put non clustered indexes on all.By i means i beleive its a good way to go in terms of Data Integrity and Performance.What is your final wordZubairTue, 08 Feb 2011 12:00:29 GMTzubairmasoodihttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspxWhat I meant is that I can't simply copy and paste the script you provided. It would fail because the 2nd create table happens after you try to build the FK.I always assume failure untill I "reach" success. So in my mind there still might be some issues I can't detect because of this "non" working script you gave me.Tue, 08 Feb 2011 11:57:18 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspx[quote][b]Ninja's_RGR'us (2/8/2011)[/b][hr]Nothing wrong with that. I guess you simply forgot the index on Countryid in the usersinfo table.Also, the script in that sequense qill not work, so maybe we are missing something else, but I doubt it.[/quote]Thanks,Yeah , I am very much that i have to add a Non Clustered Index to CountryID column of UserInfoI have generated this script from Management Studio. Can you please point out what is that you doubt?Now , i beleive yes i should have this relationship link between the two. I appreciate youThanks ZubairTue, 08 Feb 2011 11:53:39 GMTzubairmasoodihttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspxNothing wrong with that. I guess you simply forgot the index on Countryid in the usersinfo table.Also, the script in that sequense qill not work, so maybe we are missing something else, but I doubt it.Tue, 08 Feb 2011 11:48:28 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspx[quote][b]pavan_srirangam (2/8/2011)[/b][hr]In first, I don't think you can reference country id in userinfo table as foriegn key as it is not a composite key or unique key or not not part of a primary in the user info table .correct me if Im wrong.[/quote]Thanks dear,This is my table Structure--------------------------------------------------------------CREATE TABLE [dbo].[UserInfo]( [UserID] [int] IDENTITY(1,1) NOT NULL, [UserName] [varchar](50) NOT NULL, [Password] [varchar](50) NOT NULL, [b][CountryID] [int] [/b]NOT NULL, CONSTRAINT [PK_UserInfo] PRIMARY KEY CLUSTERED ( [UserID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO[b]ALTER TABLE [dbo].[UserInfo] WITH CHECK ADD CONSTRAINT [FK_UserInfo_Country] FOREIGN KEY([CountryID])REFERENCES [dbo].[Country] ([CountryID])[/b]GOALTER TABLE [dbo].[UserInfo] CHECK CONSTRAINT [FK_UserInfo_Country]GO------------------------------------------------------------------------CREATE TABLE [dbo].[Country]( [CountryID] [int] IDENTITY(1,1) NOT NULL, [CountryName] [varchar](50) NULL, CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED ( [CountryID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]Is there anything wrong with this. Or you have a suggestion for meThanksTue, 08 Feb 2011 11:44:40 GMTzubairmasoodihttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspx[quote][b]Ninja's_RGR'us (2/8/2011)[/b][hr]And just to point out something else. You will be able to delete, but not TRUNCATE. The fk removes the ability to truncate, but that's all.[/quote]Yeah , you are absolutely correct. It will allow me to delete the record from UserInfo Table but not TRUNCATE itand i still wonder what is the explanation from Microsoft in not allowing me to Truncate the Table when i beleive that TRUNCATE will recreate the complete structure , reseed the identity and recreate all constraints.Now having said this. My Ultimate Objective to add the constraint is just to speed up my query execution. so that when ever i pick UserInfo and put a join with Country Table , having foreign key will speed up the process.BUTI still wonder if its a good decision to add such a relation or Some expert can question this decision.Thanks ZubairTue, 08 Feb 2011 11:36:59 GMTzubairmasoodihttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspx[quote][b]zubairmasoodi (2/8/2011)[/b][hr]Thanks,but Logically speaking , Dont you think its wrong that it will not allow me to delete a record in UserInfo TableCountry Table is Master Table, I am not going to delete record in my Master TableI am just deleting a record in UserInfo table. which should be no problem I Can delete any user from my DB and it should not affect my Country Table (which is master)Thanks[/quote]No that you'll be allowed to do.Tue, 08 Feb 2011 11:34:16 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspxIt only has to be a key in the countries table, not the other way around.Tue, 08 Feb 2011 11:33:38 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspxIn first, I don't think you can reference country id in userinfo table as foriegn key as it is not a composite key or unique key or not not part of a primary in the user info table .correct me if Im wrong.Tue, 08 Feb 2011 11:31:40 GMT@SQLFRNDZhttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspxThanks,but Logically speaking , Dont you think its wrong that it will not allow me to delete a record in UserInfo TableCountry Table is Master Table, I am not going to delete record in my Master TableI am just deleting a record in UserInfo table. which should be no problem I Can delete any user from my DB and it should not affect my Country Table (which is master)ThanksTue, 08 Feb 2011 11:27:45 GMTzubairmasoodihttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspxAnd just to point out something else. You will be able to delete, but not TRUNCATE. The fk removes the ability to truncate, but that's all.Tue, 08 Feb 2011 11:23:50 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspx[quote][b]zubairmasoodi (2/8/2011)[/b][hr]Thanks for your quick replyI quote you"2 Improves db speed" and this is my primary motivation., but i wonder architecturally if its a good practice. Limitation here i see is , will this constraint allow me to delete a record in UserInfo table ? or will it just sayCannot truncate table 'UserInfo' because it is being referenced by a FOREIGN KEY constraint.ThanksZubair[/quote]Here's 1 of my favorite mantra. There's no point in having the wrong answer fast.I'd strongly recommend to use then in dev and once you're 1 000 0000 0000 000 000 000 000 000 000 % sure that you application is coded correctly, then you can remove the fks.Tue, 08 Feb 2011 11:22:01 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspx[quote][b]zubairmasoodi (2/8/2011)[/b][hr]Thanks for your quick replyCannot truncate table 'UserInfo' because it is being referenced by a FOREIGN KEY constraint.[/quote]No, the constraint will not let you delete the [b]Country [/b]because it is referenced by the foreign key...it will not prevent the referencing record(the child data...userInfo) fromb eing deleted.Tue, 08 Feb 2011 11:18:27 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspxThanks for your quick replyI quote you"2 Improves db speed" and this is my primary motivation., but i wonder architecturally if its a good practice. Limitation here i see is , will this constraint allow me to delete a record in UserInfo table ? or will it just sayCannot truncate table 'UserInfo' because it is being referenced by a FOREIGN KEY constraint.ThanksZubairTue, 08 Feb 2011 11:13:42 GMTzubairmasoodihttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspxI've seen it work both ways.Both have advantages that are no negligible. 1 garantees the data is good. 2 Improves db speed.What would you do ?Tue, 08 Feb 2011 11:07:45 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1060452-391-1.aspxHiI have a very simple Question and i know experts here will give me an instant reply.I have a table UserInfo withe the following columnsUserID P.KUserNamePasswordCountryIDAnd an another Table Country with the following columnsCountryID P.KCountryNameWill it be advisable to add a foreign key constraint to UserInfo table On (CountryID) column which points to CountryID of Country tableIf Yes , why?If Not , Why not? and what is the alternate way, or there should be no relation between the two.All replies are deeply appreciatedThanksNew SQL Server ProgrammerTue, 08 Feb 2011 11:04:56 GMTzubairmasoodi