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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-27 : 11:46:14
|
| Leon writes "Dear Sir/Madam,Some of the DBA's I support use an external tool to import data from one application into SQL server..This tool uses the "BCP" utility to import data in SQL server 7.In theory this should work fine, however because the data is entered at a random order the tool does not set the " -h "CHECK_CONSTRAINTS" -b 1" parameter.Because of this and a flow in the import data some tables now have a Foreignkey reference that does not reference anything.If this would have happened in DB2 (the other database system we use) DB2 would detect this and report an error.However SQL server does nothing. Evens when I use the DBCC checktable to check the table it still doesn't report the error.The only time SQL server seems to notice this is when I modify the Foreignkey.When I modify any other record in the row that has an illegal Foreignkey SQL server just accepts the command and goes on with it's business.Is there any way, for example by a stored procedure or a DBCC command, to detect this inconsistency and make sure that the database remains integer.Many thanks in advanceLeon KrancherDatabase administrator at the ING group" |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-28 : 10:56:43
|
| I'm not entirely sure if I've understood your problem here, But I'm thinking of just using a select statement to select the foriegn key in tableA and compare it to the column in TableB which it references. Something simple likeselect recordID, FKfrom tableAwhere FK not in (select fk from tableB)That should give you all the records that lack valid foriegn keys. What you would like to do after you find out what isn't valid I guess is up to you.delete from tableAwhere FK not in (select fk from tableB)or update tableAset fk = '1'where FK not in (select fk from tableB)(Allow the number '1' to be a default entry) Can't offer much more without actually seeing your table/db designs. Could you post the create statements for your tables?Feel free to email me if you need more... Or just post it up here. |
 |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-05-28 : 11:51:50
|
| If you want to ensure that the referential integrity is preserved after your data load, you can issue a dummy update statement against your table:UPDATE mytable SET fkeyfld=fkeyfldThis will report referential integrity issues (if any). |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-28 : 16:33:33
|
Leon,Most of the posters here are running SQL Server, so bashing it isn't likely to elicit the best responses ...  Correcting a misstatement below, BCP is not an external tool but rather an integral part of the SQL Server product. It is a very low-level performance-oriented tool that assumes the DBA knows what he is doing. If that's not the case then the outcome might be undesirable.Since you have bad data to start with, I would suggest first remediating it and then re-loading using either BCP with constraint checking enabled or DTS.quote:
Leon writes "Dear Sir/Madam,Some of the DBA's I support use an external tool to import data from one application into SQL server..This tool uses the "BCP" utility to import data in SQL server 7.In theory this should work fine, however because the data is entered at a random order the tool does not set the " -h "CHECK_CONSTRAINTS" -b 1" parameter.Because of this and a flow in the import data some tables now have a Foreignkey reference that does not reference anything.If this would have happened in DB2 (the other database system we use) DB2 would detect this and report an error.However SQL server does nothing. Evens when I use the DBCC checktable to check the table it still doesn't report the error.The only time SQL server seems to notice this is when I modify the Foreignkey.When I modify any other record in the row that has an illegal Foreignkey SQL server just accepts the command and goes on with it's business.Is there any way, for example by a stored procedure or a DBCC command, to detect this inconsistency and make sure that the database remains integer.Many thanks in advanceLeon KrancherDatabase administrator at the ING group"
setBasedIsTheTruepath<O> |
|
|
|
|
|
|
|
|
Maintaining database consistency