Suggestion on Foreign Key Constraint

• zubairmasoodi

  SSC Enthusiast

  Points: 140

  More actions

  February 8, 2011 at 11:04 am

  #234177

  Hi

  I have a very simple Question and i know experts here will give me an instant reply.

  I have a table UserInfo withe the following columns

  UserID P.K

  UserName

  Password

  CountryID

  And an another Table Country with the following columns

  CountryID P.K

  CountryName

  Will it be advisable to add a foreign key constraint to UserInfo table On (CountryID) column which points to CountryID of Country table

  If 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 appreciated

  Thanks

  New SQL Server Programmer

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  February 8, 2011 at 11:07 am

  #1283883

  I'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 ?

• zubairmasoodi

  SSC Enthusiast

  Points: 140

  More actions

  February 8, 2011 at 11:13 am

  #1283891

  Thanks for your quick reply

  I 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 say

  Cannot truncate table 'UserInfo' because it is being referenced by a FOREIGN KEY constraint.

  Thanks

  Zubair

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 8, 2011 at 11:18 am

  #1283898

  zubairmasoodi (2/8/2011)

  ---

  Thanks for your quick reply

  Cannot truncate table 'UserInfo' because it is being referenced by a FOREIGN KEY constraint.

  No, the constraint will not let you delete the Country because it is referenced by the foreign key...it will not prevent the referencing record(the child data...userInfo) fromb eing deleted.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  February 8, 2011 at 11:22 am

  #1283904

  zubairmasoodi (2/8/2011)

  ---

  Thanks for your quick reply

  I 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 say

  Cannot truncate table 'UserInfo' because it is being referenced by a FOREIGN KEY constraint.

  Thanks

  Zubair

  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.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  February 8, 2011 at 11:23 am

  #1283907

  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.

• zubairmasoodi

  SSC Enthusiast

  Points: 140

  More actions

  February 8, 2011 at 11:27 am

  #1283911

  Thanks,

  but Logically speaking , Dont you think its wrong that it will not allow me to delete a record in UserInfo Table

  Country Table is Master Table, I am not going to delete record in my Master Table

  I 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

• @SQLFRNDZ

  SSCrazy Eights

  Points: 9095

  More actions

  February 8, 2011 at 11:31 am

  #1283915

  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.

  [font="Tahoma"]
  --SQLFRNDZ[/url]
  [/font]

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  February 8, 2011 at 11:33 am

  #1283918

  It only has to be a key in the countries table, not the other way around.

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  February 8, 2011 at 11:34 am

  #1283920

  zubairmasoodi (2/8/2011)

  ---

  Thanks,

  but Logically speaking , Dont you think its wrong that it will not allow me to delete a record in UserInfo Table

  Country Table is Master Table, I am not going to delete record in my Master Table

  I 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

  No that you'll be allowed to do.

• zubairmasoodi

  SSC Enthusiast

  Points: 140

  More actions

  February 8, 2011 at 11:36 am

  #1283923

  Ninja's_RGR'us (2/8/2011)

  ---

  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.

  Yeah , you are absolutely correct. It will allow me to delete the record from UserInfo Table but not TRUNCATE it

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

  BUT

  I still wonder if its a good decision to add such a relation or Some expert can question this decision.

  Thanks

  Zubair

• zubairmasoodi

  SSC Enthusiast

  Points: 140

  More actions

  February 8, 2011 at 11:44 am

  #1283930

  pavan_srirangam (2/8/2011)

  ---

  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.

  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,

  [CountryID] [int] 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]

  GO

  SET ANSI_PADDING OFF

  GO

  ALTER TABLE [dbo].[UserInfo] WITH CHECK ADD CONSTRAINT [FK_UserInfo_Country] FOREIGN KEY([CountryID])

  REFERENCES [dbo].[Country] ([CountryID])

  GO

  ALTER 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 me

  Thanks

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  February 8, 2011 at 11:48 am

  #1283932

  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.

• zubairmasoodi

  SSC Enthusiast

  Points: 140

  More actions

  February 8, 2011 at 11:53 am

  #1283935

  Ninja's_RGR'us (2/8/2011)

  ---

  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.

  Thanks,

  Yeah , I am very much that i have to add a Non Clustered Index to CountryID column of UserInfo

  I 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 you

  Thanks

  Zubair

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  February 8, 2011 at 11:57 am

  #1283941

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