A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
|How important are constraints like NOT NULL and FOREIGN KEY if I'll always control my database input with PHP?||#1|
I am trying to create a column in a table that's a foreign key, but in MySQL that's more difficult than it should be. It would require me to go back and make certain changes to an already-in-use table. So I wonder, how necessary is it for MySQL to be sure that a certain value is appropriate? Couldn't I just do that with a language like PHP, which I'm using to access this database anyway?
Similarly with NOT NULL. If I only access this database with PHP, couldn't I simply have PHP ensure that no null value is entered?
Why should I use MySQL to do enforce these constraints, when I could just do it with PHP?
I realize that NOT NULL is a very stupid part to neglect for the above reasons. But MySQL doesn't enforce foreign keys without a serious degree of monkeying around.
In your opinion, would it still be bad to use the "fake" foreign keys, and simply check if the values to be entered are matched in other tables, with PHP?
posted date: 2008-12-19 13:47:00
|Re: How important are constraints like NOT NULL and FOREIGN KEY if I'll always control my database input with PHP?||#3|
They are quite important. You don't want to define your model entirely through PHP. What if there is a bug in your PHP code? You could easily have null'ed columns where your business rules state you should not. By defining it at the database level, you at least get that check for free. You're going to really hate it when there are bugs in your PHP or if any other tool ever uses your database. You're just asking for problem, IMHO.Be advised, this is the very short version of the story.
posted date: 2008-12-19 13:54:00
|Re: How important are constraints like NOT NULL and FOREIGN KEY if I'll always control my database input with PHP?||#4|
It's important to implement constraints in the database because it's impossible to predict the future! You just never know when your requirements will change. Also consider the possibility that you may have multiple developers working on the same application. You may know what all the constraints are, but a junior developer may not. With constraints on the database, the junior developer's code will generate an error, and he'll know that something needs to be fixed. Without the constraints, the code may not fail, and the data could get corrupt.
posted date: 2008-12-19 13:56:00
|Re: How important are constraints like NOT NULL and FOREIGN KEY if I'll always control my database input with PHP?||#5|
If you can swear for the life of you that nothing will ever access the DB though any other means then your (of course bug-free) PHP page, then doing it with PHP alone will be fine.Since real-world scenarios always contain some uncertainty, it is good to have the DB server watching the integrity of your data.For simple databases, referential integrity constraints might not be an absolute requirement, but a nice-to-have. The more complex the application gets, the more benefit can you draw from them. Planning them in early makes your life easier later. Additionally, referential integrity does it's part in forcing you to design the database in a more by-the-book manner, because not every dirty hack is possible anymore. This is also a good thing.
posted date: 2008-12-19 13:57:00
|Re: How important are constraints like NOT NULL and FOREIGN KEY if I'll always control my database input with PHP?||#6|
You can't "just" do it with PHP for the same reason that programmers "just" can't write bug-free code. It's harder than you think. Especially if you think it's not that hard.
posted date: 2008-12-19 13:58:00
|Re: How important are constraints like NOT NULL and FOREIGN KEY if I'll always control my database input with PHP?||#7|
The most important thing about using NOT NULL to me, is more the documentation part. When I return to the project after a few months I forget which columns it is acceptable to have nulls in. If the column says NOT NULL, then I know I will never ever have to deal with potential null values from it. And if it allows null, then I know for sure I have to deal with them.The other thing is, as others have noted: You may miss something somewhere, and cleaning up data sucks, or may be entirely impossible. It's better to know for sure that all data in your database is consistent.
posted date: 2008-12-19 14:00:00
|Re: How important are constraints like NOT NULL and FOREIGN KEY if I'll always control my database input with PHP?||#8|
Enabling these constraints in MySQL takes almost zero time. If they save you from even a single bug due to faulty PHP or other code, isn't that worth it?Keep in mind that the sorts of bugs you'll save yourself from can be rather nasty. Finding and fixing the bug itself may not be hard; the nasty part is that once you've fixed the bug you'll be left with a bunch of faulty data that may not even be salvageable.I wouldn't even approach this problem from the "well, something other than PHP might access your data someday" angle. That's true, but even more important in my mind are the the headaches, time (money) and data loss that you can save yourself simply by adding a few simple constraints.
posted date: 2008-12-19 14:02:00
|Re: How important are constraints like NOT NULL and FOREIGN KEY if I'll always control my database input with PHP?||#9|
Use the database for structural data integrity, and use the BR layer for the rest. And catch errors as early as possible. They work together.With luck, when your code as matured, you won't experience databse RI errors; and you can proudly announce yourself to be the first.
posted date: 2008-12-19 14:03:00
|Re: How important are constraints like NOT NULL and FOREIGN KEY if I'll always control my database input with PHP?||#10|
I've asked people how to enable foreign keys in MySQL, and it is rather difficult. Both tables must be InnoDB, which I am told is a bad idea. Also, the table that's being referenced has already been created, and is holding data. Can anyone help?
posted date: 2008-12-19 14:06:00
|Re: How important are constraints like NOT NULL and FOREIGN KEY if I'll always control my database input with PHP?||#11|
I don't think you can be certain that your database will only be accessed by PHP and if so, by developers who will use it to respect those constraints for the entire lifecyle of your database.If you include these constraints in your schema, then one can get a good idea of how the data is used and related by investigating your schema. If you only put all that in the code, then someone would have to look in both the database and the PHP code.But shouldn't that stuff be in the design documentation, data dictionary, and logical database design?Yes, but these documents are notorious for getting out of date and stale. I know you would never allow that to happen, but some people who have experience with projects with less discipline may assume this about your project, and want to consult the actual code and schema rather than documentation.
posted date: 2008-12-19 14:16:00
|Re: How important are constraints like NOT NULL and FOREIGN KEY if I'll always control my database input with PHP?||#12|
I highly appreciate your question, as I am deeply convinced that default-value rules should be implemented on the code-side, not on the database-side, and this for a very simple reason: when users are the one that initiate database changes (INSERTS, SELECTS and UPDATES), these changes shall integrate all business rules, and default values are basically business rules:There is no invoice without invoice numberThere is no invoice line without a quantity, and 0 or nulls are not acceptableThere is no incoming mail without date of receptionetcWe have decided a few years ago to get rid of all these "database-side" artefacts like "not null", "(do not) allow empty strings", and other "default value" tricks, and it works perfectly. Arguments in favor of the default value mainly refer to a kind of "security" principle ("do it on the database side because you will forget to to it on the code side / your language is not made for that/it's easier to do it on the database side") that does not make any sense once you have chosen not to implement any default value on the database side: just check that your business rules are properly implemented while debugging.For the last 2 years, nobody in the team ever thought of declaring a default value in a table. I guess that our younger trainee does not even know about something that is called "default value".EDIT: rereading some of the answers here, my final comment would be: do it on any side, either DB or code, but make your choice and do it on one side only! There is nothing more dangerous than having such controls on both sides, because eventually (1) you'll never know if both sides are really implementing the same rule, meaning that (2) checking the rules will mean checking both sides, which can really become a mess! The worst situation is of course when one part of the job is done on the database side (ie the rules that were identified when the database was created) and the other part (ie the newly identitified rules) done on the client side ... nightmare ....
posted date: 2008-12-19 14:34:00
|select page: « 1 2...»|