We don't need constraints
-
Ongoing discussions with DBAs regarding segmenting a large dataset...
Us: But there are issues with PK/FK relationships that preclude use from doing the work the way the DBAs want.
DBAs: no problem, you can turn off the FK constraints for the duration of the project (6-8 months). The application should prevent bad data from getting through to the DB anyway so it won't be a problem.
While technically that may be true, every time someone makes a change in our system, or one of the upstream systems, we get a couple of constraint violation errors, so I'm thinking.... no!
BTW: our system is used to make what could be life-or-death recommendations to our customers, so I'm thinking data-consistency is something about which we should be concerned.
DBAs: but you're just a consultant; it's OUR opinion that matters!
-
@snoofle said:
The application should prevent bad data from getting through to the DB anyway so it won't be a problem.
Would this be akin to relying on all validity checking in Javascript running on the client, while you turn it off in the PHP backend?
-
@snoofle said:
DBAs: no problem, you can turn off the FK constraints for the duration of the project (6-8 months). The application should prevent bad data from getting through to the DB anyway so it won't be a problem.
No problem, you can take off your seat belts for the duration of this trip (6-8 hours). The driver should prevent traffic accidents from happening anyway so it won't be a problem.
-
A SQL consultant who says something like that should be instantly fired.
-
@snoofle said:
...
BTW: our system is used to make what could be life-or-death recommendations to our customers, so I'm thinking data-consistency is something about which we should be concerned.
...
Is this life or death because if your customers lose too much money, they will jump out of a window? Not to worry then. Since the advent of air conditioning in office buildings, they have locked the windows shut.
-
Did anyone think to ask him/them what happens when you try to flip the constraints back on after 6-8 months worth of
garbagedata is in you DB, and after you have likely soiled other DBs downstream?
-
And when the application ends up inserting bad data into the database, then it's ALL. YOUR. FAULT. Because you're the consultant.
So how much longer are you still going to stick to this job?
-
I would hope at least 5 minutes before the ship starts taking on water, but after you have a WRITTEN letter of reference from some of your peers and boss...
-
[quote user="Renan "C#" Sousa"]
So how much longer are you still going to stick to this job?[/quote]
Labor Day - then I'm looking. At the rate things are going, nothing will get done by then, and I'll have an easy, if entertaining summer.
-
@C-Octothorpe said:
Already covered in the OP:Did anyone think to ask him/them what happens when you try to flip the constraints back on after 6-8 months worth of
garbagedata is in you DB, and after you have likely soiled other DBs downstream?The application should prevent bad data from getting through to the DB anyway so it won't be a problem.
The scenario you speculate about 'cannot happen, or if it could, it shouldn't.'
-
@snoofle said:
[quote user="Renan "C#" Sousa"]
So how much longer are you still going to stick to this job?
Labor Day - then I'm looking. At the rate things are going, nothing will get done by then, and I'll have an easy, if entertaining summer.
[/quote]Given the attitude of some of your peers, I might be skeptical the project would last that long. I'd have already floated my resume around. There are DBA's without any knowledge off sql. But they at least recognize this as a bad thing. They are really sysadmins who got stuck managing the DB servers. They ask questions and listen to ideas. A few of them will make fine DBA's one day. It sounds like your company's DBA's have no idea what they are doing.
-
@snoofle said:
DBAs: no problem, you can turn off the FK constraints for the duration of the project (6-8 months). The application should prevent bad data from getting through to the DB anyway so it won't be a problem.
That's the biggest show of faith in developers I've ever heard from an alleged DBA.
-
@snoofle said:
Us: But there are issues with PK/FK relationships that preclude use from doing the work the way the DBAs want.
DBAs that want the FKs turned off? For their convenience? Do you know what they wanted to do they couldn't do it with the constraints enabled?
-
Out of curiocity, I'm not the only one that's used FKs during deletions to automatically remove dependant data, am I?
-
@DOA said:
Out of curiocity, I'm not the only one that's used FKs during deletions to automatically remove dependant data, am I?
That's kind of the entire point of Triggers, as I see it.
-
@Rick said:
Is this life or death because if your customers lose too much money, they will jump out of a window? Not to worry then. Since the advent of air conditioning in office buildings, they have locked the windows shut.
That's not something that's guaranteed to stop them... click
-
@blakeyrat said:
@DOA said:
Out of curiocity, I'm not the only one that's used FKs during deletions to automatically remove dependant data, am I?
That's kind of the entire point of Triggers, as I see it.
That's basically what the ON CASCADE DELETE part of the FK constraint is. Why write separate triggers when you're duplicating built-in functionality?
-
@Anonymouse said:
That's not something that's guaranteed to stop them... click
Our advice is to apply the same rule to architecture as you do to computers: Don't ever bet your life on windows not crashing.
-
@PJH said:
@C-Octothorpe said:
That only covers what should happen. It doesn't even begin to address what actually will happen IRL.
Already covered in the OP:Did anyone think to ask him/them what happens when you try to flip the constraints back on after 6-8 months worth of
garbagedata is in you DB, and after you have likely soiled other DBs downstream?The application should prevent bad data from getting through to the DB anyway so it won't be a problem.
-
@boomzilla said:
@blakeyrat said:
@DOA said:
Out of curiocity, I'm not the only one that's used FKs during deletions to automatically remove dependant data, am I?
That's kind of the entire point of Triggers, as I see it.
That's basically what the ON CASCADE DELETE part of the FK constraint is. Why write separate triggers when you're duplicating built-in functionality?Uh, ok, maybe I have a terminology gap here, but isn't ON CASCADE DELETE... a trigger?
-
@TGV said:
If I had to guess, I would say that the DBAs are doing some kind of home grown mirroring.@snoofle said:
Us: But there are issues with PK/FK relationships that preclude use from doing the work the way the DBAs want.
DBAs that want the FKs turned off? For their convenience? Do you know what they wanted to do they couldn't do it with the constraints enabled?
-
@blakeyrat said:
@boomzilla said:
@blakeyrat said:
@DOA said:
Out of curiocity, I'm not the only one that's used FKs during deletions to automatically remove dependant data, am I?
That's kind of the entire point of Triggers, as I see it.
That's basically what the ON CASCADE DELETE part of the FK constraint is. Why write separate triggers when you're duplicating built-in functionality?Uh, ok, maybe I have a terminology gap here, but isn't ON CASCADE DELETE... a trigger?
A trigger is a specific object you create; ON CASCADE is a property of the table.
But hey, if a driver is hardware for you, then sure, ON CASCADE can be a trigger.
-
@Xyro said:
A trigger is a specific object you create; ON CASCADE is a property of the table.
But hey, if a driver is hardware for you, then sure, ON CASCADE can be a trigger.
If it walks like a duck, and it quacks like a duck...
-
@blakeyrat said:
@Xyro said:
A trigger is a specific object you create; ON CASCADE is a property of the table.
But hey, if a driver is hardware for you, then sure, ON CASCADE can be a trigger.
If it walks like a duck, and it quacks like a duck...
But you're looking at a goose and you don't know what a goose sounds like.
-
@blakeyrat said:
@boomzilla said:
@blakeyrat said:
@DOA said:
Out of curiocity, I'm not the only one that's used FKs during deletions to automatically remove dependant data, am I?
That's kind of the entire point of Triggers, as I see it.
That's basically what the ON CASCADE DELETE part of the FK constraint is. Why write separate triggers when you're duplicating built-in functionality?Uh, ok, maybe I have a terminology gap here, but isn't ON CASCADE DELETE... a trigger?
Maybe. It certainly works in a similar way. I guess that's an implementation detail for the particular RDBMS. My point was that your comment seemed to imply something about writing an explicit trigger. Because why else bring it up, when we're talking about constraints?
-
@boomzilla said:
Maybe. It certainly works in a similar way. I guess that's an implementation detail for the particular RDBMS. My point was that your comment seemed to imply something about writing an explicit trigger. Because why else bring it up, when we're talking about constraints?
Well, don't worry. Nobody's going to let a chance to call me an idiot slip by.
I honestly thought ON CASCADE actions were triggers. Feel free to point and laugh now.
-
@blakeyrat said:
I honestly thought ON CASCADE actions were triggers. Feel free to point and laugh now.
Muahahahaha. Uh...glad I could straighten you out. I guess an example of how they come into being:alter table foo add constraint bar foreign key baz references bat.blarg on delete cascade; create or replace trigger fizz ... end;
Another reason not to write explicit triggers is that it's easy to run afoul of referential integrity checks by the DB (no doubt a lot of this is implementation specific). Also, you've separated your FK constraint from the cascading delete.
-
@blakeyrat said:
Feel free to point and laugh now.
*pointing* HaHaHaHaHa !!But seriously, I agree with you on this one. I also consider cascading actions to be a sort of trigger, for the same duck-related reasons.
-
@blakeyrat said:
Well, don't worry. Nobody's going to let a chance to call me an idiot slip by.
Ha! You're such an idiot slip by!
-
@Xyro said:
@blakeyrat said:
I've never seen a bigger idiot slip by than blakey.Well, don't worry. Nobody's going to let a chance to call me an idiot slip by.
Ha! You're such an idiot slip by!
