SethT3 (3/11/2010)
I am a database administrator. I am not a data architect. I am easily confused.I am working with a SQL Server 2005 database that has little to no existing documentation. One of the existing pieces of documentation is a relatively current ERD diagram. There are candidate key - foreign key relationships depicted in the ERD diagram.
Now for the 'easily confused' part: Am I huffing glue to expect that the candidate key - foreign key relationships depicted in the ERD diagram be implemented as foreign key definitions in the production version of the database?
The database was an Oracle database converted to SQL Server by Oracle DBAs. Not much of the declarative referential integrity came across. Most of the tables may as well be flat files, there is not much that relates the tables to one another.
No, the application in front of this database does not address these issues. Rather, the application exploits these issues.
Please let me know if I am being unreasonable to expect relationships depicted in the ERD to evidence themselves as foreign keys in the physical implementation of the database. My current situation is a lost cause, but there is another database headed towards production with much the same lack of declarative referential integrity.
You say that not much of the DRI came across from the Oracle conversion and, I suspect, there wasn't much there to come across. I agree with what the others have stated... DRI on a production database is an absolute must and you should be able to generate new physical ERDs from it.
--Jeff Moden
Change is inevitable... Change for the better is not.