SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Suggestion on Foreign Key Constraint


Suggestion on Foreign Key Constraint

Author
Message
zubairmasoodi
zubairmasoodi
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
zubairmasoodi
zubairmasoodi
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28829 Visits: 9671
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
zubairmasoodi
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28829 Visits: 9671
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.
zubairmasoodi
zubairmasoodi
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28829 Visits: 9671
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.
zubairmasoodi
zubairmasoodi
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28829 Visits: 9671
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search