Suggestion on Foreign Key Constraint

  • 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

  • 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 ?

  • 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

  • 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!

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

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

  • 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

  • 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]

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

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

  • 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

  • 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

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

  • 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

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

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply