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 ««12

Suggestion on Foreign Key Constraint Expand / Collapse
Author
Message
Posted Tuesday, February 8, 2011 11:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 8, 2012 11:10 AM
Points: 12, Visits: 20
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
Post #1060501
Posted Tuesday, February 8, 2011 11:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 8, 2012 11:10 AM
Points: 12, Visits: 20
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
Post #1060509
Posted Tuesday, February 8, 2011 11:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
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.
Post #1060511
Posted Tuesday, February 8, 2011 11:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 8, 2012 11:10 AM
Points: 12, Visits: 20
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
Post #1060514
Posted Tuesday, February 8, 2011 11:57 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
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.
Post #1060520
Posted Tuesday, February 8, 2011 12:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 8, 2012 11:10 AM
Points: 12, Visits: 20
hi

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

Thank 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 word

Zubair

Post #1060523
Posted Tuesday, February 8, 2011 12:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
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.
Post #1060533
Posted Tuesday, February 8, 2011 12:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 8, 2012 11:10 AM
Points: 12, Visits: 20
Ninja's_RGR'us (2/8/2011)
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.


Thanks Ninja,

I will just like to trouble a little more, I hope you will bear me while I try and solve the problem.

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


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 .

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


I could not understand what you mean by the above statement.

Thanks

Zubair
Post #1060572
Posted Tuesday, February 8, 2011 3:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:45 AM
Points: 21,397, Visits: 9,610
I'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.
Post #1060691
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse