Many of us consider form validation to be a server-side language effort, with a sprinkle of front-end JavaScript to enforce the simpler rules. It can be easily forgotten though, that the database is a great tool for validating data.<\/p>\n
There's a lot of code out there (I'm guilty of this myself) that "works around" the database's innate ability to validate information inserted into it's tables. This validation comes in the form of constraints<\/em>. You can define constraints on data, such as primary & foreign keys, the data type of the field or uniqueness. When I talk about "working around" the database's ability to validate data, I'm primarily referring to this practice:<\/p>\n In the ColdFusion example above, we actually perform a query first, to determine if it's okay to insert data into the database.<\/p>\n <\/p>\n To leverage the power of database validation, we need to make sure we model our database tables properly. The most obvious technique is to use a primary key<\/strong> or unique<\/strong> constraint on a single field. For instance, if we needed to ensure that the same email address can not be used twice to create a new user account, we might place a unique<\/strong> constraint on the email<\/em> field in our users table.<\/p>\n If we wanted to create a wish list feature for our product store, we'd create a "cross reference" table, which links a record in the users table with a record in the products table. But, we wouldn't want to add the same product to a user's wish list twice.<\/p>\n We could construct the cross reference table as follows:<\/p>\n Constructing a table in this fashion forces uniqueness among the user_id<\/em> and product_id<\/em> fields. If an insert operation attempts the same combination of user_id<\/em> and product_id<\/em> already found in the table, the unique constraint on the primary key will be broken and a duplicate entry error will be thrown.<\/p>\n <\/p>\n Lucky for us, databases communicate constraint related errors to the calling application fairly well. In the case of our primary key constraint, if we attempt to insert a record that breaks the unique constraint on a primary key, the insert operation will fail and an error will be returned to the application.<\/p>\n MySQL returns error code #1062, which means that we've attempted to insert a duplicate entry. <\/p>\n Sticking with ColdFusion to demonstrate:<\/p>\n We now have a script that "validates" our data in just one query. An unquestionable performance boost from the two-query validation script we started with.<\/p>\n","protected":false},"excerpt":{"rendered":" Performing validation at the database level not only protects the integrity of our data, but also presents a performance enhancement while simplifying our application code.<\/p>\n","protected":false},"author":1,"featured_media":1766,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"amp_status":""},"categories":[13],"tags":[82,88,264],"_links":{"self":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/1741"}],"collection":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/comments?post=1741"}],"version-history":[{"count":10,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/1741\/revisions"}],"predecessor-version":[{"id":1768,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/posts\/1741\/revisions\/1768"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/media\/1766"}],"wp:attachment":[{"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/media?parent=1741"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/categories?post=1741"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sitekickr.com\/blog\/wp-json\/wp\/v2\/tags?post=1741"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n\t <\/p>\nTwo-query style validation (not recommended)<\/h3>\n
SELECT id\r\nFROM wishlist\r\nWHERE user_id = 2\r\n AND product_id = 3\r\n\r\n<cfif id.recordCount eq 0>\r\n <!--- insert wishlist item --->\r\n<cfelse>\r\n <cfoutput>This item is already in your wishlist.<\/cfoutput>\r\n<\/cfif><\/pre>\n
Modelling your database table to support validation<\/h3>\n
CREATE TABLE wishlist (\r\n user_id int(11) NOT NULL,\r\n product_id int(11) NOT NULL,\r\n created datetime NOT NULL,\r\n PRIMARY KEY (user_id, product_id)\r\n);<\/code><\/pre>\nLeaving validation to the database<\/h3>\n
<cftry>\r\n\r\n <cfquery>\r\n INSERT INTO wishlist (\r\n user_id,\r\n product_id\r\n created\r\n )\r\n VALUES (\r\n <cfqueryparam value="#the_user_id#">, \r\n <cfqueryparam value="#the_product_id#>, \r\n now()\r\n )\r\n <\/cfquery>\r\n\r\n<cfcatch>\r\n\r\n <cfif cfcatch.NativeErrorCode eq 1062>\r\n\r\n <cfoutput>This item is already in your wishlist.<\/cfoutput>\r\n\r\n <\/cfif>\r\n\r\n<\/cfcatch>\r\n<\/cftry>\r\n\r\n\r\n<\/pre>\n