| Author |
Topic |
|
jeroena
Yak Posting Veteran
76 Posts |
 Posted - 2002-10-23 : 04:14:10
|
| HiI want to delete the contents of an entire database each day and update it from another. I want to use TRUNCATE TABLE for all tables, but you can't use TRUNCATE TABLE if the table is referenced by a foreign key. Is there a work around?DELETE is no option: to much logging and identity counter is not reset.Thnx. Jeroen. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-10-23 : 05:27:00
|
| is it valid/reasonable....to have a 'spare' blank database with all the tables, procs, etc. defined...and restore it over the live database each morning????Is there ANYTHING in the permanent database that needs to be retained across days....user lists, calendars, etc? |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-10-23 : 05:30:42
|
| Is this a reporting database i.e. is it only select activity with no insert/update/delete activity ? Is it all the tables from the other database or just some ?HTHJasper Smith |
|
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2002-10-23 : 07:03:59
|
| The database is like an index of a part of a mainframe database. For performance the mainframe database is 'copied' into a SQL server database. Only SELECTS... would not be very usefull to perform an update and TRUNCATE it all an hour later, cause that's what the question was about... Can't I temporarily disable the foreign keys or something?Jeroen. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-23 : 08:06:05
|
| I think you'll have to ALTER TABLE...DROP CONSTRAINT on all of the foreign key constraints, and then ALTER TABLE again to re-establish them after the import. That's the only way you'll be able to use TRUNCATE TABLE.Is this database a read-only? Does anyone actually update it or insert new rows? It would be unusual since you wipe it out and reimport it regularly. In that case, why do you need the foreign keys at all? I'm assuming that they would be enforced in the mainframe system and really don't need to be used in the SQL Server copy. |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-10-23 : 08:38:51
|
| Echoing Rob's point and why i asked for confirmation that this was read only, you don't need the constraints in the table assuming the data is scrubbed before loading. Also when you've finished loading you can make the db readonly to save a few k on locks. You just need the indexes that will benefit your queries. You might need to experiment but it could prove quicker to drop all indexes,load the tables with tablelock, recreate indexes and make the db readonly.Make sure the database recovery mode is SIMPLE or BULK LOGGED.HTHJasper Smith |
|
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2002-10-23 : 09:24:02
|
| Hi,I introduce my own key values to link identies, because the keys in the original data are large and alfanumeric. (I do this with lookups in DTS.) I also rearrange tables etc. so look at it as a new read-only database with a random data source. Anyway, that's how I look at it. No foreign keys? Yikes! No swearing please. Maybe, someday in a galaxy far, far away some other datasource might be added to add data to it as well and is not clean.Meditate on the tablelock stuff I must.Thnx., Jeroen. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-23 : 09:40:41
|
| OK, here's another question: so what if the original keys are "large and alphanumeric"? Are they unique? That's all that matters. What's the point of creating NEW primary keys, that you then have to LINK and POPULATE throughout tables that are otherwise identical to the source? And I'll be that that the end user never even sees this new key, right? By doing this you've tremendously complicated a relatively simple import process. And if you use the original keys, as long as the source files come from the same source, you don't NEED to clean anything up if a new table comes along.There's no logic in creating a new key to replace an already existing key, and adding all kinds of overhead to accommodate the new key. And you don't have to rearrange tables, you can simply create views that have the structure you want, and have your reporting use the views instead of the base tables. |
|
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2002-10-23 : 09:58:52
|
| Don't worry. It's not tremendously complicated. When a key is 20 characters long and consists of multiple columns, all this data is used in the referencing tables as well. A simple numeric value is much easier and performce better as well. So I prefer that. But I get your point.About rearranging tables, when I copy the data, why should I recreate bad design and add some cumbersome views?? For example, when a single table with products holds a varchar column with the product type and another one with the product type description, and there are only a few different product types, I prefer to create a table "product type".For application views on the data I use stored procedures.Jeroen. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-10-24 : 11:41:24
|
| "Echoing Rob's point and why i asked for confirmation that this was read only, you don't need the constraints in the table assuming the data is scrubbed before loading. Also when you've finished loading you can make the db readonly to save a few k on locks. You just need the indexes that will benefit your queries."from Jasper_Smith is KEY to solving your problem....and by the sounds of it.....all modification to the data are done elsewhere....this database/system is a reporting one.....Humour us....experiment with the advice given to date....Create a (test) version of the database all the structure except for the constraints...make sure your indices remain....then load the data from the source and then test performance and finally the TRUNCATE process.....you might be surprised....(if you go the read-only database route as advised elsewhere...do remember to set the database not to be read-only BEFORE issuing the truncate statements)CONSTRAINTS exist to control modifications to existing data via inserts, updates and deletes.....IF you are NOT going to do this in this database, you can drop the constraints....that IS not to say that you drop the indices. |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
|
|
transio
Starting Member
7 Posts |
Posted - 2002-10-24 : 12:25:20
|
An off-topic note:If numeric keys are so important, why not just alter the mainframe DB to use them?  Then you won't have to worry about modifying the structure of the DB after import, and you can eliminate FKs and TRUNCATE to your heart's content!Otherwise, you should probably just use the ALTER TABLE... DROP CONSTRAINT before the TRUNCATE, and ALTER TABLE... ADD CONSTRAINT after the TRUNCATE.That would probably require the least amount of work on your part (despite the fact that it's the wrong way to go), and you'll keep your job  -------------------------Steve (aka SteQL)http://www.synthelogic.comhttp://www.transio.com |
|
|
|
srf
Starting Member
42 Posts |
Posted - 2002-10-24 : 12:47:44
|
| If you want to get around FKs, it's usually easier to to "alter table xxxx nocheck constraint all" than to add/drop constraints. |
|
|
|
jeroena
Yak Posting Veteran
76 Posts |
Posted - 2002-10-25 : 06:58:40
|
| Hi all,Transio, you mentioned the words "alter" and "mainframe" in one sentence, hehe. But it's good to stay optimistic. I don't have access to this mainframe anyway.I indeed drop and recreate the FK's and it works fine. The NOCHECK option doesn't work, as srf suggested. Truncating data is still not possible when a table is referenced by a foreign key.The reactions in this thread and some further reading on the internet have really made me more aware of the difficult choise between surrogate or natural keys. Both have advantages and disadvantages. I don't think one is best. Of course, sometimes it clear, like in dataware house systems.Somehow I just tend more to surrogate keys, because of the trouble I had with natural keys in the past. (Like dropdown boxes in VB that hold database values, etc.) In the case of my current project I now think the surrogate keys do not add much value beside the programming ease for me, and those who build the application on top of the database, and maybe performace, although I cannot prove that.Anymore reading material on the subject is always welcome.Jeroen. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-25 : 08:05:23
|
quote:
...and maybe performace, although I cannot prove that...
haha ... that's just the thing ... you can prove it by trying both, testing, collecting metrics and reviewing the results ...What you mean to say is "I don't {feel like putting the effort in to,have time to,know how to,am allowed to,whatever} prove that." In which case, I would say, "The way bastardize the relational model with contrived keys? If it ain't broke ..."Do a forum search on "surrogate" and "Page47" as author. I've got much to say on the natural vs. surrogate key debate ...My current client is in much the same pickle that you are in. SQL Server isn't really a OLTP or OLAP data tier ... really it's just a nice little place to stage data to go out to the web. All the work is on a mainframe and all the presentation is on the web. It is just easier for the web to play with SQL than some mainframe.Here's the thing. I believe in the relational model. I believe in FK constraints and the like. In fact, when I first got on this job, I stood up from my test, grasped my chest the Fred Sanford and cried out, "No Foreign Keys?!? What the hell is this?" ... Well, lemme ask you this? Lets say something does happen where the data coming in violates a FK constraint? What are you going to do about it? It's not your problem, right? Data integrity is handled by the mainframe, not your SQL. Do you take the entire business down because you think the mainframe as effed up? In my case no. If there is a "fk violation" (I say that in quotes, cause there are no FKs), I just keep on rolling and present the effed up data to the web ... data integrity is NOT my job ... staging the mainframe data is ...Just my 2 pretty polly, my appy polly loggy ...Jay White{0} |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2002-10-27 : 21:14:48
|
| Must be nice to be a contractor and say "it's not my job" !As for me, a mere employee, when marketing reused old product codes and screwed the aggregates in a sales data mart project, it wasn't my job but guess who had to fix it and then implement surrogate keys to prevent it happening again?Should have done it in the first place. ----Nancy Davolio: Best looking chick at Northwind 1992-2000 |
|
|
|
transio
Starting Member
7 Posts |
Posted - 2002-10-28 : 11:30:41
|
quote:
Transio, you mentioned the words "alter" and "mainframe" in one sentence, hehe. But it's good to stay optimistic. I don't have access to this mainframe anyway.
LOL, I did. It was intended more as sarcasm than optimism, but I'm glad it was appreciated  -------------------------Steve (aka SteQL)http://www.synthelogic.comhttp://www.transio.com |
|
|
|
|
truncate table