{"id":73384,"date":"2011-08-30T17:11:21","date_gmt":"2011-08-30T17:11:21","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/business-rule-only-one-per-day-but-keep-the-time\/"},"modified":"2021-07-14T13:08:10","modified_gmt":"2021-07-14T13:08:10","slug":"business-rule-only-one-per-day-but-keep-the-time","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/business-rule-only-one-per-day-but-keep-the-time\/","title":{"rendered":"Business Rule: Only One per Day, but Keep the Time"},"content":{"rendered":"<p>The business rule states:<\/p>\n<p>Only one entry is allowed per ID and per day and the time should be recorded.<\/p>\n<p>The table involved (simplified for the blog post)<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2011\/08\/BusinessRule-img1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-605\" title=\"BusinessRule-img1\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2011\/08\/BusinessRule-img1.png\" alt=\"\" width=\"240\" height=\"147\" \/><\/a><\/p>\n<p>Wouldn&#8217;t it be nice if it was possible to do it like this?<\/p>\n<p><a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2011\/08\/BusinessRule-img2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-606\" title=\"BusinessRule-img2\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/oracle\/2011\/08\/BusinessRule-img2.png\" alt=\"\" width=\"500\" height=\"274\" \/><\/a><\/p>\n<p>This way you still have the complete date information (time is a component of the date column), and only use the TRUNC (inspection_dt) to constrain the data entry.\u00a0 As you can tell from the error message, this is not allowed.<\/p>\n<p>Oracle 11g Release 1 introduced Virtual Columns which can implement this requirement declaratively. A Virtual Column is a column which is based on an expression which can be used<br \/>\nin e.g. constraints &#8211; just what the doctor ordered.<\/p>\n<p>You can read the full article on my blog: <a href=\"http:\/\/nuijten.blogspot.com\/2011\/08\/business-rule-only-one-per-day-but-keep.html#more\">http:\/\/nuijten.blogspot.com\/2011\/08\/business-rule-only-one-per-day-but-keep.html#more<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>The business rule states: Only one entry is allowed per ID and per day and the time should be recorded. The table involved (simplified for the blog post) Wouldn&#8217;t it be nice if it was possible to do it like this? This way you still have the complete date information (time is a component of the date column), and only&hellip;<\/p>\n","protected":false},"author":316184,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73384","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73384","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316184"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73384"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73384\/revisions"}],"predecessor-version":[{"id":91815,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73384\/revisions\/91815"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73384"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73384"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73384"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73384"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}