![]() ![]() ![]() I try to avoid the use of "null" to indicate an explicit value. Then the trigger raises an exception if the value is not in ('Y','N'), although this functionality could be accomplished using the check constraint that you mentioned. To help out a bit, I use a pre-insert/update trigger which converts null values to 'N', and uppercases all values. My implementations have always been more rigid - the column is defined as "Not Null", 'Y' means True and 'N' means False. The other developer suggests that null means False, and any non-null value means True (a C-like paradigm). ![]() One developer suggests that one single value ('Y' for example) means True, and any other value (including null) means False. Since joining my current project, I've heard of 2 more approaches from well-respected Oracle developers. Although I think ('Y','N') is the correct domain, I've seen other implementations where ('T','F') are used, and that's fine - any 2 values are fine as long as they're used consistently across the project. This implies that the column can be defined as "Not Null", and I think that's a good idea, too. You've also suggested that there should be a check constraint in place that ensures that the values are members of an explicit domain - 'Y' or 'N'. So, unless you've revised your original suggestion, let's assume the datatype and length issues have been settled - char(1). I think that's a very good idea, and I've been using a similar approach for years. I'm involved in a large database development project and I'd like to find out your opinion on best practices for what I'm referring to as "pseudo-boolean datatypes".Īs mentioned in this thread, Oracle does not natively support an SQL boolean datatype, and you've suggested an alternative implementation using a char(1) datatype with a check constraint. This is a follow-on to the current thread. ![]() Best I've been able to come up with is "Because they don't _need_ it, and because they don't feel like having it." You have to admit, that's a pretty rare answer to give w/r/t an Oracle feature. Sorry if this sounds whiney, but it's something I find myself explaining to nearly every new Oracle developer, and I've never heard a really good reason for why not. Space: if I'm using a fixed width multi-byte character set, doesn't a char(1) boolean take up more space than a universal boolean would? Speed: doesn't that check constraint take a tiny amount more time than validating against conformance to a particular datatypes? PL/SQL compatibility: It would be nice to be able to declare booleans using "var_name%TYPE".Īnd just to clarify on performance issues: Someone who spoke another language might choose two different characters. When I needed my first one, I used T and F. It would be nice to not have to make that conversion every time.Ĭonsistency: Y/N values with a check constraint are one way to represent a boolean. Sure, if it's a char(1) that's a good indication, but not a sure-fire one.Ĭompatibility: I know you're suspicious of products developed for multiple platforms, but there are a lot of them out there. Clarity: if the value I'm trying to store really is a boolean, then when I'm glancing at my model, it's clear that that's what the column is. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |