March 11, 2010 at 8:04 am
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.
March 11, 2010 at 8:11 am
DRI should be the default, without some overwhelming reason not to use it,
And by “overwhelming reason”, I don’t mean that the developers were too lazy to bother, didn't know what they were doing, or were sure that the application would take care of it.
Others may disagree with this, but they're wrong. 🙂
March 11, 2010 at 8:39 am
Thank you, Michael, for the sanity check.
April 20, 2011 at 9:30 am
Bear in mind that ERDs may be created for different purposes. Sometimes the purpose of an ERD is A: to model the Universe of Discourse. Sometimes the purpose is B: to represent what should be or is being implemented in the database. People who use ERDs often refer to A as a conceptual or logical ERD and B as a physical ERD.
The declarative constraints existing in Microsoft SQL Server are not capable of implementing every commonly occurring business rule (i.e. the rules intended to ensure the UoD is modelled accurately). Some of the constraints specified or implied by an ERD therefore might not be implemented. Some constraints might be implemented but only as procedural code (e.g. triggers) and some might be impossible or impractical to implement in the database at all. Implementing business rules in a DBMS (SQL ones generally, not just SQL Server) is always a compromise and quite often more integrity rules have to be left out than are put in.
Oracle has more referential integrity options than SQL Server has (e.g. deferred constraints, full/parial matching). Also the semantics of nullable uniqueness constraints in Oracle are different to SQL Server, which also means the semantics of RI constraints referencing those uniqueness constraints can be different too. So someone with an Oracle background might think it worth putting certain constraints into his ERD and only later find that they cannot be implemented in SQL Server in the way the model described.
April 20, 2011 at 11:53 am
Start with: Foreign Keys are useful and effective. They should be there if you don't have a reason not to have them there. No argument.
However, FK's stop truncates, amongst other things. They are a harassment if you don't need them because of sloppy front end code and a lack of transactional procs. How often do you truncate tables? Depends on design. Yes, usually you'd only do it in staging tables, or on report servers to quickly reload smaller tables via a snapshot push, which is where you'd usually reference from your larger tables that you DON'T truncate. I know there are alternatives, they're just not as easy to maintain for a simple task.
So, yes, you probably want them. I don't necessarily consider them the be all end all to the ERD though.
For anyone who wants to prove the truncate problem to themselves:
CREATE TABLE ABC (ParentID INT IDENTITY( 1, 1) NOT NULL, somedata VARCHAR(50))
ALTER TABLE ABC ADD CONSTRAINT PK_ABC PRIMARY KEY CLUSTERED (ParentID)
INSERT INTO ABC (somedata) VALUES ( 'abc')
INSERT INTO ABC (somedata) VALUES ('def')
CREATE TABLE childABC (ChildID INT IDENTITY( 1, 1) NOT NULL, ParentID INT NOT NULL, someotherdata VARCHAR(50))
INSERT INTO childABC (ParentID, someotherdata) VALUES ( 1, 'uuu')
INSERT INTO childABC (ParentID, someotherdata) VALUES ( 1, 'iii')
INSERT INTO childABC (ParentID, someotherdata) VALUES ( 2, 'bbb')
INSERT INTO childABC (ParentID, someotherdata) VALUES ( 2, 'vvv')
ALTER TABLE childABC
ADD CONSTRAINT FK_childABC_ParentID FOREIGN KEY (ParentID) REFERENCES ABC (ParentID)
select * from childABC
TRUNCATE TABLE childABC
TRUNCATE TABLE ABC
/*
Error running the last statement:
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'ABC' because it is being referenced by a FOREIGN KEY constraint.
*/
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 24, 2011 at 3:36 pm
SethT3 (3/11/2010)
The database was an Oracle database converted to SQL Server
It appears to be the case of a half-cooked conversion - nothing more, nothing less.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 24, 2011 at 8:13 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply