Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nomadsoul
Yak Posting Veteran
89 Posts |
 Posted - 2006-11-15 : 23:09:05
|
| Do you always need a fk-pk relationship to enforce referential integrity or can it be done between non-key tables?It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-11-15 : 23:12:19
|
| Another option is writing trigger to ensure integrity. Also, you can have SPs which handle all insert/update/deletes and which also takes care of referential integrity. But then also, I would go for having RIs in place at table level, so that you can't even accidentally add something which is violating the constraints. It's always good to be Proactive rather than Reactive. (Your negligence may cost you a job)Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-15 : 23:41:50
|
| Thanks harsh. Because I was thinking about what to with tables created from the SELECT * INTO statements and trying to give them RI without adding constraints for each table?. I hope you'll excuse my newbness but are you saying I could create just one trigger or sp that would enforce RI for every non key table I produce? Can you point me to any instruction online that could show me how to do this?I am going to books online now to see what I can find thereIt is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-15 : 23:46:32
|
You shouldn't be creating permanent tables using SELECT INTO, you should design your database and implement referential integrity across all the tables accordingly. If you let the tables create themselves you'll lose control of the entire database in no time  |
|
|
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-16 : 00:02:11
|
| Thanks snSQL. But then what is SELECT INTO useful for? Just to copy?It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-11-16 : 00:05:32
|
quote:
Originally posted by nomadsoul
Thanks snSQL. But then what is SELECT INTO useful for? Just to copy?It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity
Yes. It just copy data and not indices, constrainst, etcYou should pre-create table and then insertCreate table .........Insert into tableSelect columns from.......MadhivananFailing to plan is Planning to fail |
|
|
|
nomadsoul
Yak Posting Veteran
89 Posts |
Posted - 2006-11-16 : 00:19:50
|
| Thanks Madv. I'm on the path of learning the art of backups and copying so I'll probably have more questions like this and am fortunate to benefit from everyones experience here.It is better to be prepared and not have an opportunity than to not be prepared and have an opportunity |
|
|
|
|
|
|
A referential integrity question