Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Foreign Keys
- Thread starter jazerr
- Start date
nigelrivett
IS-IT--Management
What's an explicit foreign key.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
- Thread starter
- #3
Sorry, my funny terms..
I mean actually using Foreign keys rather than designing a table and just kind of [remembering] that this references that.
Example:
ALTER TABLE [dbo].[plah] ADD
CONSTRAINT [FK_pla_parent] FOREIGN KEY
(
[plahparentID]
) REFERENCES [dbo].[plah] (
[plah]
GO
I mean actually using Foreign keys rather than designing a table and just kind of [remembering] that this references that.
Example:
ALTER TABLE [dbo].[plah] ADD
CONSTRAINT [FK_pla_parent] FOREIGN KEY
(
[plahparentID]
) REFERENCES [dbo].[plah] (
[plah]
GO
nigelrivett
IS-IT--Management
Depends how you access the database.
If it's always via SPs then you can use those to maintain integrity - but why not put on the foreign keys? You can always drop tyhem temporarily if you need to do some admin work.
They will save you a lot of trouble in invalid data when someone messes up an update.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
If it's always via SPs then you can use those to maintain integrity - but why not put on the foreign keys? You can always drop tyhem temporarily if you need to do some admin work.
They will save you a lot of trouble in invalid data when someone messes up an update.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
- Thread starter
- #5
nigelrivett
IS-IT--Management
Still better to have the FK's unless you find they impact performance.
They will protect against someone doing something silly.
Why are you reluctant to implement them?
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
They will protect against someone doing something silly.
Why are you reluctant to implement them?
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
nigelrivett
IS-IT--Management
They will also help new people to understand the database structure and also give something for diagramming tools to work on in the future.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
- Thread starter
- #8
Mostly because there are SO MANY of them.
I'm trying to implement something that our Dir. of Eng. spec'd up, but he has no knowledge of any of the implications of what he does.
And given that I've managed to go 6 yrs without them, I dont really know why I need them now, since I write good SQL anyway.
I dont know if that made sense to anyone but me.
I'm trying to implement something that our Dir. of Eng. spec'd up, but he has no knowledge of any of the implications of what he does.
And given that I've managed to go 6 yrs without them, I dont really know why I need them now, since I write good SQL anyway.
I dont know if that made sense to anyone but me.
IMO makes some sense... assuming several things: a) you have absolute control over data changes, b) everything goes through SP, c) stored procedures are well written.
Personally I often cannot fullfill requirement a), so I use foreign keys whenever necessary.
Personally I often cannot fullfill requirement a), so I use foreign keys whenever necessary.
nigelrivett
IS-IT--Management
>> since I write good SQL anyway
If I had a penny for every time I've heard that.
The important question is whether anyone else is ever going to work on this system. If you think you are going to be the sole developer involved with this system for it's lifetime then that's fine - if you want to move on then you should consider the affect of your actions and code so that the system is easy to understand and maintain.
I'm wondering if you have been building unnormalised schemas and this is being normalised properly which is why this is comming up.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
If I had a penny for every time I've heard that.
The important question is whether anyone else is ever going to work on this system. If you think you are going to be the sole developer involved with this system for it's lifetime then that's fine - if you want to move on then you should consider the affect of your actions and code so that the system is easy to understand and maintain.
I'm wondering if you have been building unnormalised schemas and this is being normalised properly which is why this is comming up.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
My 2¢...
I wouldn't be without FK constraints. I have never found the overhead to be much of a burden, and it keeps you from having to write a lot of RI (referential integrity) code in your SPs. Trust me, the database can enforce the RI with less overhead via constraints than you can with custom code in SPs.
Furthermore, though from an application standpoint your database will be accessed through SPs, I doubt that is the case for administration. DBAs will use Ent Mgr and Query Analyzer, etc. and you will save yourself tons of headaches by having the constraints in place. And, if you are the DBA, you will be GLAD you have them there.
Lastly, might I lobby for ErWin. Find a copy...go to Ebay...ask on newsgroups, but get a legal copy of the software, even if an older version. The last couple of versions have supported SQL 2K, SQL 7, etc. and haven't changed that much. With ErWin you diagram and IT generates all the schema, with options for including FK constraints, FK indexes, etc.
Regards,
TR
I wouldn't be without FK constraints. I have never found the overhead to be much of a burden, and it keeps you from having to write a lot of RI (referential integrity) code in your SPs. Trust me, the database can enforce the RI with less overhead via constraints than you can with custom code in SPs.
Furthermore, though from an application standpoint your database will be accessed through SPs, I doubt that is the case for administration. DBAs will use Ent Mgr and Query Analyzer, etc. and you will save yourself tons of headaches by having the constraints in place. And, if you are the DBA, you will be GLAD you have them there.
Lastly, might I lobby for ErWin. Find a copy...go to Ebay...ask on newsgroups, but get a legal copy of the software, even if an older version. The last couple of versions have supported SQL 2K, SQL 7, etc. and haven't changed that much. With ErWin you diagram and IT generates all the schema, with options for including FK constraints, FK indexes, etc.
Regards,
TR
- Thread starter
- #12
I have used ErWin for years, and tried Visio when it first got ER diagram capabilities and at that time didn't find Visio as powerful or as easy to use. However, that could be because it wasn't "what I knew" and it may have gotten better since then.
Regards,
TR
Regards,
TR
nigelrivett
IS-IT--Management
ErWin is meant specifically for this task and has been around (and developing) for years.
For Visio it's more of an add-on.
Saying that ErWin is a lot more complicated and has more of a learning curve.
For large projects I use ErWin for small things (which I suspect this is) Visio or word or whatever.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
For Visio it's more of an add-on.
Saying that ErWin is a lot more complicated and has more of a learning curve.
For large projects I use ErWin for small things (which I suspect this is) Visio or word or whatever.
======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question