Contents<\/b><\/p>\n If you are completely confident about the data that is being inserted or updated in a table, then you won’t need constraints. Even with a complete logical interface of stored procedures you’ll still require them for development work because the impossible always seems to happen.<\/p>\n In the real world, however, and particularly if the real world includes programmers using ‘Dynamic SQL’, or data feeds into tables and so on, then they are a vital defence for the integrity of your data.<\/p>\n There are several different ways of putting constraints and checks on data, and the purpose of this workshop is just to point out the odd quirk that might be of practical help, whilst running through the list.<\/p>\n Rules, defaults, indexes, and triggers are used for:<\/p>\n I have a certain difficulty with this workbench, as the SQL Server team have given advanced notice of its intention to replace Rules and Defaults, probably with ANSI Domains. However, until that time comes, they are the most effective way to implement certain business rules in the database.<\/p>\n One could argue that they shouldn’t be used because they are deprecated, but the fact is that there is no realistic substitute in SQL 2005 for the functionality, and safety mechanisms, they provide.<\/p>\n Would you refuse to wear a seatbelt, on hearing that a better design was due in four years time?<\/p>\n Rules are the best way of ensuring that business rules, especially those that are liable to change, are implemented in one place only.<\/p>\n As mentioned above, Rules are considered deprecated in SQL Server 2005. However there is nothing yet that replaces the functionality they provide until Domains are properly implemented. One should be aware of possible problems when upgrading. BOL gives the mysterious advice to use Check constraints instead, as if the two devices had the same functionality.<\/p>\n Rules are used to check data that is being inserted or updated in columns. If you need to perform a fairly complex check, and it has to be done in more than one table, then a rule is very handy. One rule can be referenced from any number of tables or user-defined data types in the database. You can bind a rule to to a user-defined data type, so it will then apply to any column where the datatype is used. This is even more convenient and safe.<\/p>\n The process is…<\/p>\n You can create a rule easily in EM or SMSS but don’t even think about altering the rule if you have bound it to an object. The interface was not implemented with any enthusiasm.<\/p>\n Imagine that you have a database in which you wish to handle postcodes. Normally, you’d want to put the whole messy business of checking that the format is valid into a stored procedure, as well as requiring that the front-end applications do all the appropriate filtering. However, this isn’t always possible. Whatever happens, complex code like this must<\/strong> be kept in one place and heavily documented.<\/p>\n Postcode validation criteria are as follows (slightly simplified):<\/p>\n We put these criteria, which will define our With that done we can then bind the rule to a column of a table. You can bind a rule to as many columns as you wish within the database: it keeps everything neat without duplication of the implementation of business rules.<\/p>\n Now, could we use a function in a rule? This would make it so much easier to develop and we can do a bit more processing.<\/p>\n Sadly no. A missed opportunity.<\/p>\n Not what you’d expect; it accepts a silly postcode. There is, sadly, no checking of a rule on variable with a user-Defined datatype.<\/p>\n Defaults and rules seem to have attracted the displeasure of the SQL Standards committee.<\/p>\n Defaults are deprecated by SQL Server 2005 but are actually rather useful because they can be ‘bound’ to a user-defined Data Type as well as any column in the entire database. There is no other way of doing this.<\/p>\n Take a common example. I believe that every table should have a column that gives the insertion date. You can, of course laboriously set up a default constraint on every table but why not do the following…<\/p>\n Triggers can be quite complex, as Pop Rivett explains elsewhere on this site with his excellent ‘Auditing’ trigger. We’ll only discuss triggers as data constraints.<\/p>\n Imagine we want to check addresses that are being placed in an address table. We want to ensure that we’ve done reasonable sanity checks to the address. For a start, we believe that an address should contain at least four words and alphanumeric characters with just a sprinkling of punctuation<\/p>\n Here is one of Phil Factor’s routines (thanks Phil).<\/p>\n Integrity Constraints, like rules, limit or ‘constrain’ you in what you can put in a table or column. Unlike triggers, integrity constraints cannot cascade changes through related tables in the database, enforce complex restrictions by referencing other database objects, perform “what if” analyses or roll back the current transaction as a result of enforcing data integrity. (With triggers, you can either roll back or continue the transaction, depending on how you handle referential integrity.) Nevertheless they are the most commonly used constraint and simple to define and alter.<\/p>\n There are three types of Table-level constraint:<\/p>\n We’re most concerned with Check constraints in this article. Like a rule, the check has to be an expression that would fit in a Imagine we want to check that an email address is valid.<\/p>\n So we see that the table-level check has done a check based on both columns.<\/p>\n A unique index permits no two rows to have the same index value, including You cannot create a unique index on a column that contains null values in more than one row \u2013 an error is, obviously, triggered.<\/p>\n You can use the The use of referential constraints is beyond the scope of this article, as it is difficult to talk about them without going on to write the entire chapter of a book, touching on the complexities of referential data models, cascading deletes and so on.<\/p>\n This workshop started off when I was staring at the tree view of Enterprise Manager, and suddenly realising I’d never used Rules and wondering what they were for! I hope you’ve learned something new too.<\/p>\n Just a few questions<\/strong> just to check …<\/p>\n Robyn Page provides essential techniques for ensuring the validity of the data being entered into your SQL Server tables.…<\/p>\n","protected":false},"author":221812,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4546,4544,4543,4547,4149,4545,4150,4151,4460],"coauthors":[6814],"class_list":["post-195","post","type-post","status-publish","format-standard","hentry","category-learn","tag-check-contraints","tag-data-integrity","tag-data-validation","tag-default-constraints","tag-learn-sql-server","tag-rules","tag-sql","tag-sql-server","tag-workbench"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/195","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\/221812"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=195"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/195\/revisions"}],"predecessor-version":[{"id":81282,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/195\/revisions\/81282"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=195"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=195"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=195"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=195"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n
Introduction<\/h2>\n
\n
Rules<\/h2>\n
\n
CREATE RULE<\/code>.<\/li>\n\n
RULE<\/code>, in a test harness to develop it and test it as much as possible, as they are not the easiest of objects to change.<\/p>\n--start of the test harness \r\nDECLARE @Postcode VARCHAR(10) \r\nSELECT @Postcode='CO10 7SP' \r\n\r\nSELECT CASE WHEN \r\n--start of the validation rules \r\nLEN (LTRIM(@Postcode)) BETWEEN 6 AND 8--between 6 and eight chars \r\nAND \r\nLEN(REPLACE(@Postcode,' ',''))=LEN(@postcode)-1--one space only! \r\n --local (inward) code, to the right of the space, 3 characters \r\nAND--The second and third characters of the local code must be alpha \r\nSUBSTRING(@Postcode,CHARINDEX(' ',@Postcode)+1,3) LIKE '[0-9][a-Z][a-z]' \r\nAND--first character of the Sorting Office (outward) code must be alpha \r\n SUBSTRING(@Postcode,1,CHARINDEX(' ',@Postcode)-1) LIKE '[a-z][0-9a-z]%' \r\nAND--Sorting Office (outward) code the left of the gap, between 2 and 4 \r\nCHARINDEX(' ',@Postcode) BETWEEN 3 AND 5 \r\n--one number followed by two letters. \r\n--end of the validation rules \r\nTHEN 'good' ELSE 'bad' END \r\n--end of the test harness \r\n\r\n\/* Now we create the RULE \r\nYou can use any name for the argument (we chose @Postcode), but the \r\nfirst character must be \"@.\" Make sure it is something meaningful \r\nYou can use built-in functions but anything that references other \r\ndatabase objects is forbidden. \r\n\r\n *\/ \r\n\r\nIF EXISTS (SELECT * FROM dbo.sysobjects \r\n WHERE id = OBJECT_ID(N'[dbo].[PostcodeValidation]') \r\n AND OBJECTPROPERTY(id, N'IsRule') = 1) \r\nDROP RULE [dbo].[PostcodeValidation] \r\nGO \r\n\r\nCREATE RULE PostcodeValidation \r\nAS \r\nLEN (LTRIM(@Postcode)) BETWEEN 6 AND 8--between 6 and eight chars \r\nAND \r\nLEN(REPLACE(@Postcode,' ',''))=LEN(@postcode)-1--one space only! \r\n --local (inward) code, to the right of the space, 3 characters \r\nAND--The second and third characters of the local code must be alpha \r\nSUBSTRING(@Postcode,CHARINDEX(' ',@Postcode)+1,3) LIKE '[0-9][a-Z][a-z]' \r\nAND--first character of the Sorting Office (outward) code must be alpha \r\n SUBSTRING(@Postcode,1,CHARINDEX(' ',@Postcode)-1) LIKE '[a-z][0-9a-z]%' \r\nAND--Sorting Office (outward) code the left of the gap, between 2 and 4 \r\nCHARINDEX(' ',@Postcode) BETWEEN 3 AND 5 \r\n--one number followed by two letters. \r\nGO \r\n\r\n--then we apply the documentation \r\nEXEC sp_addextendedproperty 'MS_Description', \r\n'length between 6 and 8 characters \r\nContaining one space only \r\n3 characters to the right of the space \r\nof which the first myst be numeric and the other two alpha \r\nBefore the space can be between 2 and 4 characters \r\nof which the first must be alpha', \r\n 'user', dbo, 'rule', PostcodeValidation \r\n <\/pre>\nCREATE TABLE TestOfRule (MyID INT IDENTITY(1,1), Postcode VARCHAR(10)) \r\n\r\n\/* \r\nYou cannot bind a rule to a text, image, or timestamp datatype column. \r\nYou cannot bind rules to system tables. Still, who cares?*\/ \r\n\r\n--First bind the rule to the table column \r\nEXEC sp_bindrule PostcodeValidation, 'TestOfRule.Postcode' \r\n\r\n--Now let's try out some inserts \r\nINSERT INTO TestOfRule(postcode) SELECT 'CM20 3EQ'--OK \r\nINSERT INTO TestOfRule(postcode) SELECT 'CM2 3EQ'--OK \r\nINSERT INTO TestOfRule(postcode) SELECT 'CM 3EQ'--OK \r\nINSERT INTO TestOfRule(postcode) SELECT 'CM2 30EQ'--not so good \r\nINSERT INTO TestOfRule(postcode) SELECT 'CM2 3EQ'--not so good \r\nINSERT INTO TestOfRule(postcode) SELECT 'SQUIFFY'--Not so good \r\n\r\n\/*---error---! \r\nA column insert or update conflicts with a rule imposed by a previous \r\nCREATE RULE statement. The statement was terminated. The conflict \r\noccurred in database 'master', table 'TestOfRule', column 'Postcode'. \r\nThe statement has been terminated. \r\n<\/pre>\n
CREATE FUNCTION [dbo].[ufiCleverStuff] \r\n( @string VARCHAR(8000)) RETURNS INT AS BEGIN RETURN 1 END \r\n\r\nALTER TABLE dbo.TestOfRule ADD Address VARCHAR(255) NULL \r\n\r\nCREATE RULE AddressValidation AS dbo.ufiCleverStuff(@address)=1 \r\n--This will cause an error! \r\n\r\n\/* However, we can bind the rule to a user-defined data type *\/ \r\nEXEC sp_addtype Postcode,'Varchar(10)',NONULL --NONULL=no nulls allowed \r\nEXEC sp_bindrule PostcodeValidation, 'Postcode' \r\nsp_help Postcode --just to chck that the rule is there (Rule_Name) \r\n\r\n--And use it wherever we want \r\nCREATE TABLE SecondTestOfRule (MyID INT IDENTITY(1,1), Postcode postcode) \r\n\r\n--and try it out \r\nSELECT * FROM SecondTestOfRule \r\nINSERT INTO SecondTestOfRule(postcode) SELECT 'ME3 5EQ' \r\n--OK \r\nINSERT INTO SecondTestOfRule(postcode) SELECT 'AR3 567' \r\n--didn't like the 567 bit \r\nINSERT INTO SecondTestOfRule(postcode) SELECT 'ME2 30RP' \r\n--not so good \r\nINSERT INTO SecondTestOfRule(postcode) SELECT 'ME 3RP' \r\n--OK \r\nINSERT INTO SecondTestOfRule(postcode) SELECT 'DE52 3EQ' \r\n--not so good \r\nINSERT INTO SecondTestOfRule(postcode) SELECT 'SQUIFFY' \r\n--Not so good \r\nUPDATE SecondTestOfRule SET postcode='SE34 2DS' WHERE myid=5 \r\n\r\nUPDATE SecondTestOfRule SET postcode='squiffy' WHERE myid=5 \r\n--good, it chucked it out \r\n\/* \r\nRules bound to columns take precedence over rules bound to user \r\ndatatypes. \r\n*\/ \r\n--now what happens when we create a variable of the datatype \r\nDECLARE @MyPostcode Postcode \r\nSELECT @Mypostcode='&^bananas****' \r\n\/* \r\n<\/pre>\n
Defaults<\/h2>\n
--just create a simple default of the current date and time \r\nCREATE DEFAULT DateOfInsertion AS GETDATE() \r\n\r\n--now document it \r\nEXEC sp_addextendedproperty 'MS_Description', \r\n'this is a default supplying the current date', \r\n 'user', dbo, 'default', DateOfInsertion \r\n\r\n--add a new type called InsertionDate \r\nEXEC sp_addtype InsertionDate,'DateTime',NONULL \r\n\r\n--add the default to the type \r\nsp_bindefault DateOfInsertion, \"InsertionDate\"\r\n\r\nsp_help InsertionDate --check that the default is there (Default_name) \r\n\r\n--now the whole process is a lot simpler \r\nCREATE TABLE EventLog (MyID INT IDENTITY(1,1), \r\n TheDescription VARCHAR(100), \r\n insertionDate insertionDate) \r\nINSERT INTO Eventlog(theDescription) \r\n VALUES ('the martians are attacking') \r\nINSERT INTO Eventlog(theDescription) \r\n VALUES ('It is the treens') \r\nINSERT INTO Eventlog(theDescription) \r\n VALUES ('Call in Dan Dare') \r\nINSERT INTO Eventlog(theDescription) \r\n VALUES ('Dan Dare to the rescue') \r\nSELECT * FROM EventLog--note that the dates have been filled in \r\n\r\n\/* but there is a feeling that there is something missing.. Why not \r\nhave a nice identity field User-Defined Data Type too? *\/ \r\n\r\nEXEC sp_addtype counter,INT,'IDENTITY' -- Sybase only. Sorry \r\n--you'll get an error \r\n<\/pre>\nTriggers<\/h2>\n
CREATE FUNCTION [dbo].[ufiWordcountVarchar] --counts the number of \r\n--words in a VARCHAR. We need something like this to check a valid \r\n--address. \r\n( \r\n@string VARCHAR(8000), \r\n@WordStartCharacters VARCHAR(255)='a-z', \r\n@WordCharacters VARCHAR(255)='-a-z''' \r\n) \r\nRETURNS INT \r\nAS \r\nBEGIN \r\nDECLARE @Position INT, --index of current search \r\n @WhereWeAre INT,--index into string so far \r\n @wordcount INT,--the number of words found so far \r\n @next INT, --where the next search starts \r\n @size INT --the total size of the text \r\n\r\nSELECT @WhereWeAre=1,@size=LEN(@string),@Position=1,@wordcount=0 \r\nWHILE @Position>0 \r\n BEGIN \r\n SELECT @Position=PATINDEX('%['+@wordStartCharacters+']%', \r\n SUBSTRING(@string,@whereWeAre,8000)) \r\n IF @Position>0 \r\n BEGIN \r\n SELECT @next=@WhereWeAre+@Position,@wordcount=@Wordcount+1 \r\n SELECT @Position=PATINDEX('%[^'+@wordCharacters+']%', \r\n SUBSTRING(@string,@next,8000)+' ') \r\n SELECT @WhereWeAre=@next+@Position \r\n END \r\n END \r\nRETURN @wordcount \r\nEND \r\nGO \r\n\r\n\/* so we create a test table and add a trigger to it, putting the two \r\nchecks in the trigger *\/ \r\n\r\nCREATE TABLE MyAddresses (MyID INT IDENTITY (1,1), Address VARCHAR(100)) \r\nGO \r\nCREATE TRIGGER tCheckAddress_MyAddress \r\nON MyAddresses \r\nFOR INSERT, UPDATE \r\nAS \r\nBEGIN \r\nIF (SELECT MIN(dbo.ufiWordcountVarchar(Address,DEFAULT,DEFAULT)) \r\n FROM inserted)<4 \r\nOR \r\n EXISTS (SELECT 1 FROM inserted WHERE address LIKE '%[^-a-z0-9''\",.()]%') \r\n BEGIN \r\n ROLLBACK TRANSACTION \r\n RAISERROR ('Suspect address inserted into MyAddresses',16,1) \r\n END \r\nEND \r\n\r\n--not forgetting the documentation \r\nEXEC sp_addextendedproperty 'MS_Description', \r\n'This checks to see if there are at least four words in the address \r\nand checks for characters you wouldn''t expect to see in an address ', \r\n 'user', dbo, 'table', MyAddresses,'trigger', tCheckAddress_MyAddress \r\n\r\nINSERT INTO MyAddresses (address) \r\n VALUES ('32, Acacia Avenue, Goldhay, Berkshire') \r\nINSERT INTO MyAddresses (address) \r\n VALUES ('12 the Firs, Clive West Way, Slough, Berks') \r\nINSERT INTO MyAddresses (address) \r\n VALUES ('46, the promenade, felixstowe essex fizzbang<>*') \r\nINSERT INTO MyAddresses (address) \r\n VALUES ('Buckingham palace') \r\nUPDATE Myaddresses SET address='Aargh!' WHERE MyID=2 \r\nSELECT * FROM MyAddresses \r\n\r\n<\/pre>\nIntegrity Constraints<\/h2>\n
\n
WHERE<\/code> or IF<\/code> Clause, but can’t involve a subquery. You can apply several constraints on the one column.<\/p>\nCREATE TABLE TestConstraint \r\n( MyID INT IDENTITY(1,1), \r\n MyEmailAddress VARCHAR(50) CONSTRAINT my_check_constraint \r\n CHECK (MyEmailAddress NOT LIKE '%[^a-z-_.@]%' \r\n AND LEN(REPLACE(MyEmailAddress,'@',''))=LEN(MyEmailAddress)-1) \r\n) \r\n\r\nEXEC sp_addextendedproperty 'MS_Description', \r\n'Check to make sure that only valid characters are in the email address \r\nand there is only one @ sign', \r\n 'user', dbo, 'table', testconstraint,'constraint', my_check_constraint \r\n\r\nINSERT INTO TestConstraint (MyEmailAddress) \r\n SELECT 'Robyn.Page@Simple-talk.com' \r\nINSERT INTO TestConstraint (MyEmailAddress) \r\n SELECT 'Silly@>@Simple-talk.com' \r\nINSERT INTO TestConstraint (MyEmailAddress) \r\n SELECT 'Bill.Gates@Microsoft.com' \r\nUPDATE TestConstraint \r\n SET MyEmailAddress='dodgy@@simple-talk.com' WHERE MyID=1 \r\n\r\n\/* the second one and fourth will fail. Ideally, one should be able to \r\nbind a user-defined error message to the constraint. Again, sorry - \r\nSybase only! \r\n\r\nif we want the constraint to check other columns, then we need a table \r\n-level constraint rather then a column constraint*\/ \r\n\r\nCREATE TABLE SecondTestConstraint \r\n( MyID INT IDENTITY(1,1), \r\n MyName VARCHAR(50), \r\n MyDomain VARCHAR(50), \r\nCONSTRAINT my_Second_check_constraint \r\n CHECK (MyName+MyDomain NOT LIKE '%[^a-z-_.@]%' \r\n AND LEN(REPLACE(MyName+MyDomain,'@','')) \r\n =LEN(MyName+MyDomain)-1) \r\n) \r\n\r\nINSERT INTO SecondTestConstraint (MyName,MyDomain) \r\n SELECT 'Robyn.Page','@Simple-talk.com' \r\n\r\nINSERT INTO SecondTestConstraint (MyName,MyDomain) \r\n SELECT 'Mad <andBad>','@Simple-talk.com' \r\n\r\n\r\n<\/pre>\n
NULL<\/code>. A unique index checks that all data in the column is unique when it is created and also when an insert or update is attempted.<\/p>\nunique<\/code> keyword on composite indexes.<\/p>\nCREATE UNIQUE INDEX idxUniqueEmail ON TestConstraint(MyEmailAddress) \r\nINSERT INTO TestConstraint(MyEmailAddress) SELECT 'Gengis@Kahn.com' \r\nINSERT INTO TestConstraint(MyEmailAddress) SELECT 'Kubla@Kahn.com' \r\nINSERT INTO TestConstraint(MyEmailAddress) SELECT 'Robyn@Kahn.com' \r\nINSERT INTO TestConstraint(MyEmailAddress) SELECT 'Anyone@Kahn.com' \r\nINSERT INTO TestConstraint(MyEmailAddress) SELECT 'Admin@Kahn.com' \r\nINSERT INTO TestConstraint(MyEmailAddress) SELECT 'Gengis@Kahn.com' \r\n--yes, they all go in except for the last one which triggers an error \r\n<\/pre>\n
\n