{"id":1953,"date":"2015-02-11T00:00:00","date_gmt":"2015-02-11T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-dri-subject-of-references\/"},"modified":"2021-09-29T16:21:30","modified_gmt":"2021-09-29T16:21:30","slug":"the-dri-subject-of-references","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-dri-subject-of-references\/","title":{"rendered":"The DRI Subject of References"},"content":{"rendered":"
\n

In the early days of SQL, it was assumed that SQL would never be ready for prime time. Oh, it might be useful for ad hoc data analysis of some kind but not the heavy lifting required for production work. That would always belong to COBOL with ISAM, IDMS. IMS, Total and other network based models of databases and file systems. <\/p>\n

Well, that turned out not to be true. But there is more to the story than just “SQL got bigger tables and faster searches”; SQL got data integrity features! This is a major difference between a file system and a database. A file system simply holds the data, without any regard to what it means, business rules or relationships in the data. That kind of thing was the job of an application program. Lots and lots of application programs. You could not keep all those programs in synch and could guarantee data integrity. <\/p>\n

A database should enforce and maintain business rules and relationships in the data. This is not part of any application program; it is part of the data model. <\/i>By having rules separated from the applications, we know that everyone is playing with the same data and done one way, in one place, one time. And we do not have to write thousands of line of the same code over and over. And then maintain them. Yes, it is possible to subvert data integrity, but you have to go out of your way to do it. <\/p>\n

In data modeling, we have the concept of strong and weak entities. A strong entity exists on its own merits, and a weak entity has to have a strong entity paired with it. The classic example is the SQL idiom for an Orders database we have all seen. Here is the simplest version and it is wrong:<\/p>\n

CREATE TABLE Orders -- strong entity\n(order_nbr CHAR(10) NOT NULL PRIMARY KEY,\n ..);\nCREATE TABLE Order_Details -- weak entity\n(order_nbr CHAR(10) NOT NULL,\n line_nbr INTEGER NOT NULL,\n sku CHAR(15) NOT NULL,\n PRIMARY KEY (order_nbr, line_nbr),\n ..);\n<\/pre>\n
 There are lots of errors here.  The physical<\/i> line number in the order details table is not a logical<\/i> construct. This model copies the  paper order form’s lines into rows in a table. We do not do that in a valid logical data model! I can order the same  carton of buzzard eggs several times on different lines on a paper form, but in the logical model, I want to remove that  redundancy by having an “order_qty\"<\/code> column. And if you are a good programmer, you will have a CHECK (order_qty > 0)  <\/code>on  the column. <\/p>\n
 The concept of a tiered  architecture is strange to old monolithic procedural language programmers. You need an input layer that will aggregate  those buzzard eggs under the same sku (Stock Keeping Unit), as a single order detail. This is also where we can validate  check digits, standardize the street address and phone numbers, etc. The goal is to put only clean data into the  database, not use the base tables as a staging area.  <\/p>\n
 This first skeleton schema had  no Declarative Referential Integrity (DRI) between these two tables. Look at what happens when an Order is dropped; the  Order Details will be orphaned. I have seen SQL systems where the orphans fill the disk and have to be cleaned out with  homemade garbage collection procedures every few weeks. Performance is awful, of course. Before you ask, the old network  databases had their versions of this problem. But they came with built-in garbage collection procedures that had to be  used on a schedule as part of a defragmentation run. <\/p>\n
 SQL got smarter, started doing  maintenance functions automatically and added a declarative REFERENCES<\/code> clause on the weak entity table. Here is the  improved version: <\/p>\n
CREATE TABLE Order_Details\n(order_nbr CHAR(10) NOT NULL\n  REFERENCES Orders(order_nbr),\n sku CHAR(15) NOT NULL,\n PRIMARY KEY (order_nbr, sku),\n ..);\n<\/pre>\n
 The Orders table is called the referenced <\/b>table and the Order_Details<\/code> table is called the referencing <\/b>table. Please notice that we do not  have “parent” and “child” tables in RDBMS. Those terms are from the old network databases. They were implemented by  pointers and had to be between two distinct “tables” (actually, files in those days) !  <\/p>\n
 Too many new SQL programmers  will stop at this point. However, we have more power from the options in SQL. The referenced columns do not have to be  the PRIMARY KEY<\/code>; they just have to be a key, which means that they are declared with  NOT NULL<\/code> UNIQUE<\/code> in the referenced  table. But keys can have more than one column! You can use a FOREIGN KEY<\/code>() clause when you have a multiple column  reference. The syntax is <\/p>\n
[CONSTRAINT <constraint name>]\n FOREIGN KEY (referencing_col1, .., referencing_col_n)\n REFERENCES <referenced table name> (referenced_col1, .., referenced_col_n)\n<\/pre>\n
 The  referenced column list must match, by data type and position, to the referencing column list. This is called “union  compatible” in the jargon. Many noobs are so afraid of multi-column keys, they will use all kinds of non-relational  kludges to work around them and get a single column “surrogate key” (actually, Dr. Codd said that surrogates are not  exposed to or maintained by the user, but skip that for now). Let’s explain this with an example, say  (someplace_longitude, someplace_latitude<\/code>), If you assign a GUID, concatenate the values of the pair or whatever kludge  you like, then how do you assure that your “surrogate” is properly matched to a real key pair? And why did you want to  increase redundancy? Learn to use cut & paste if you think that the lengths of column lists are a problem.  <\/p>\n
When you give names to the  constraints with the optional CONSTRAINT<\/code> <constraint name<\/code>><\/code> clause, the errors messages will use the given name and a human being can read them. These  names are global to the schema and, not just local to a table. But these constraint names also mean you can also turn  the constraint off then back on. The syntax is …<\/p>\n
ALTER TABLE <table name> [NOCHECK | CHECK] CONSTRAINT <constraint name> \n Consider this self-reference trick to prevent gaps in a timeline of events:\nCREATE TABLE Events\n(event_id CHAR(10) NOT NULL,\nprevious_event_end_date DATE NOT NULL\nCONSTRAINT Chained_Dates\nREFERENCES Events (event_end_date),\nevent_start_date DATE NOT NULL,\nevent_end_date DATE UNIQUE, -- null means event in progress\nPRIMARY KEY (event_id, event_start_date),\nCONSTRAINT Event_Order_Valid\nCHECK (event_start_date <= event_end_date),\nCONSTRAINT Chained_Dates \nCHECK (DATEADD(DAY, 1, previous_event_end_date) = event_start_date).\n<< other stuff for this event >>\n);\n-- disable the Chained_Dates constraint\nALTER TABLE Events NOCHECK CONSTRAINT Chained_Dates;\n-- insert a starter row\nINSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)\nVALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');\n-- enable the constraint in the table\nALTER TABLE Events CHECK CONSTRAINT Chained_Dates;\n-- this works\nINSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)\nVALUES ('Glob Week', '2010-01-05', '2010-01-06', '2010-01-10');\n-- this fails\nINSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)\nVALUES ('Snoob', '2010-01-09', '2010-01-11', '2010-01-15'); \n<\/pre>\n
 In the SQL model, we have no  idea how REFERENCES<\/code> is implemented and we do not care. We can have a reference back to the same table, make cycles or  pretty any kind of path you want. It is a more abstract and general concept than pointers. Hashing is another approach,  for example, that other SQL products use. <\/p>\n
 You can also add a DRI action  clause to the REFERENCES<\/code>. They were meant to replace TRIGGER<\/code>s with declarative code. A  TRIGGER<\/code> <\/code> cannot be optimized  because it is procedural code that could do anything in the schema. We noticed that 80-90% of the TRIGGER<\/code>s in the real  world were used to maintain the weak-strong entity relationships, so we made it into a declarative feature. Right now,  T-SQL is not that good about optimizing the DRI, like other SQL products, but there is hope for the future releases.  <\/p>\n
 In the ANSI\/ISO standard model  for both TRIGGER<\/code>s and DRI, an INSERT <\/code>or DELETE \t<\/code>is called a database event and it causes a response. Please note that an  INSERT <\/code>is not an event in the ANSI\/ISO world, so the T-SQL model is a little different. In fact, T-SQL’s DRI is minimal,  but very useful. In the full ANSI\/ISO syntax, there are more options about matching rules, and deferring constraints.  The full ANSI\/ISO Standard is also more forgiving about cycles than T-SQL, which disallows any of them.  <\/p>\n
 The DRI syntax is<\/p>\n
ON [UPDATE| DELETE] <DRI action><\/pre>\n
 The actions change the  referencing table when the referenced table has an event.  <\/p>\n
    \n
  •  \t\tNO ACTION <\/strong> \t\tis the default action. If a  \t\tDELETE <\/code>or UPDATE \t\t<\/code>is executed on referenced rows, you get an error message about a constraint violation. This is what  happens with just the REFERENCES<\/code> by itself, but it makes things complete.  \t\t \t\t<\/li>\n
  •  \t\tCASCADE<\/code> specifies that the referencing column will be updated when the referenced column is updated, and  referencing rows will be deleted when the referenced rows are deleted. When a referenced row is change, this can change  another table with DRI actions. The SQL engine has to keep track of all of this stuff because if anything goes wrong,  the engine has to do a ROLLBACK<\/code>. This can be a lot of overhead and get tricky. Try to keep a simple tree structure in  the DRI network. \t\t<\/li>\n
  •  \t\tSET DEFAULT:<\/strong> The referencing column(s) will be set to their DEFAULT<\/code> values when an UPDATE \t\t<\/code>or DELETE<\/code> <\/code>is performed on referenced rows.  Obviously, the referencing column(s) must have DEFAULT<\/code> clauses.  \t\t \t\t<\/li>\n
  •  \t\t \t\tSET NULL:<\/strong> This  action specifies that the column will be set to NULL<\/code> when the referenced column is updated or deleted. Obviously, the  referencing column(s) must be NULL<\/code>-able. \t\t<\/li>\n<\/ul>\n
     Required Weak Entities  <\/b><\/h1>\n
      <\/b>Have you noticed that the  referencing table has to find a match in the referenced table, but not vice versa? Thinking about the Orders <\/code>schema,  this says that we cannot take an order for a product we do not have in stock. You will get  a constraint violation message and a ROLLBACK<\/code>. But it is fine to have products in stock that nobody wants to order.  <\/p>\n
     You might have a business rule  that allows an order to be posted, but not have any items on the order. This would be an abandoned shopping cart on a  website. Now consider a schema that models Personnel and their job skills. We can use the  Standard Occupational Classification (SOC) for the skills.  <\/p>\n
    CREATE TABLE Personnel\n(emp_id CHAR(12) NOT NULL PRIMARY KEY, \n soc CHAR(7) NOT NULL \n CHECK (soc LIKE '[0-9][0-9]-[0-9][0-9][0-9][0-9]'),\n );\nCREATE TABLE Skills\n(soc CHAR(7) NOT NULL \n CHECK (soc LIKE '[0-9][0-9]-[0-9][0-9][0-9][0-9]'),\n emp_id CHAR(12) NOT NULL\n REFERENCES Personnel (emp_id),\n PRIMARY KEY(soc, emp_id)\n);\n<\/pre>\n
     This  guarantees that each employee will have at least one skill. But this skill is modeled as an attribute of an employee,  not a weak entity related to an employee.<\/i> This means we need to union the two tables get all the SOC codes together! <\/p>\n
    CREATE VIEW SOC_List (emp_id, soc)\nAS\n(SELECT emp_id, soc FROM Skills\n UNION \n SELECT emp_id, soc FROM Personnel);\n<\/pre>\n
     But  this is a mess. I can have the same SOC code in both tables; clearly a redundancy. This skeleton usually occurs when  each employee started with only one required skill and it was put into the Personnel table as if it was an attribute and  not a relationship. When the requirements changed to include multiple skills, the programmer added a new table rather  than more columns. The extra columns would be a repeated group and violate First Normal Form (1NF). But this design  already has a repeated group violation – the group simply has one member!  <\/p>\n
     I can  keep the skills unique between the two tables with a trigger and some procedural code. But his means procedural code and  we hate procedural code. It is also trickier than you think. If I delete the skill in the Personnel table, then I have  to look at the Skills table.<\/p>\n
      \n
    • If the Skills table has more than one skill left,  then you need a rule for moving a skill to the Personnel table. You will have to turn off the constraints, lock the  tables, move the data and finally turn on the constraints again. <\/li>\n
    • If the Skills table has just one skill left, then  you need to move that skill to the Personnel table. But then you need to get clean out the skills table.  <\/li>\n
    • <\/li>\n<\/ul>\n
      This  code cannot be optimized and probably not ported easily. You really need to go back to the original strong-weak table  model, so the SOC code appears in one and only one place. What happens when we have a cycle between the tables? It is a  formal version of “You need experience to get work and you need work to get experience” vicious cycle in employment. \t\t<\/p>\n
      In  full ANSI\/ISO Standard SQL, we have the CREATE ASSERTION<\/code> statement. This is a schema level  \t\tCHECK() <\/code>constraint. This is  why a constraint name has to be global and not local to a table. <\/p>\n
      \t\tCREATE ASSERTION One_Skill_per_Employee\n\t\tCHECK (NOT EXISTS \n\t\t   (SELECT emp_id FROM Personnel \n\t\t    EXCEPT\n\t\t    SELECT emp_id FROM Skills));\n<\/pre>\n
      What this says is that when you remove all the employees in the Skills table from  all the employees in the Personnel table, you should get an empty table. Since this is at the schema level, a violation  will cause an error in your current session. <\/p>\n
      But SQL Server doe not have the CREATE ASSERTION<\/code> or deferrable constraints. It has  the WITH CHECK OPTION<\/code> on VIEWs and you can “fake it” a little bit with this feature. This feature says to re-check the  \t\tWHERE<\/code> clause if the underlying table(s) changes. The VIEW<\/code> has to be on one table and updatable for this to work best. \t\t<\/p>\n
      \t\tCREATE VIEW Valid_Skills\n\t\tAS\n\t\tSELECT * \n\t\t  FROM Skills\n\t\t WHERE \n\t\t   NOT EXISTS\n\t\t   (SELECT emp_id FROM Personnel \n\t\t    EXCEPT\n\t\t   SELECT emp_id FROM Skills)\n\t\tWITH CHECK OPTION; \n\t\t \n<\/pre>\n
       \t\tAnd likewise, you can copy the constraint into a second view that covers the  \t\tPersonnel <\/code>table data.<\/p>\n
      \t\tCREATE VIEW Valid_Personnel\n\t\tAS\n\t\tSELECT * \n\t\t  FROM Personnel\n\t\t WHERE \n\t\t  NOT EXISTS\n\t\t  (SELECT emp_id FROM Personnel \n\t\t   EXCEPT\n\t\t   SELECT emp_id FROM Skills)\n\t\tWITH CHECK OPTION; \n<\/pre>\n
       \t\tLet’s see how this works. Add a new employee:<\/p>\n
      \t\tINSERT INTO Personnel VALUES ('Wilma', '03-0000');<\/pre>\n
       \t\tBut now the Valid_Skills VIEW i<\/code>s empty! The lack of skills for Wilma empty the  whole VIEW<\/code>. You can add the skills and Valid_Skills<\/code> will return:<\/p>\n
      \t\tINSERT INTO Skills VALUES ('Wilma', '03-0001'), ('Wilma', '03-0002');<\/pre>\n
      We do not really need the with check<\/code> option in this case, but it is a good safety  feature if people use the view for updating. The real trick is to write procedures that updates, inserts into and  deletes from both base tables in a single transaction. I will leave that as a exercise for the reader.  <\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
      A database must be able to maintain and enforce the business rules and relationships in data in order to maintain the data model. It does this through referential constraints. They aren’t complex, but are powerful, especially with the means to attach DRI actions to them. Joe Celko explains all, and pines for the ANSI CREATE ASSERTION  statement…<\/p>\n","protected":false},"author":96214,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[4168,4150,4252],"coauthors":[],"class_list":["post-1953","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-database","tag-sql","tag-t-sql-programming"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1953","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\/96214"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1953"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1953\/revisions"}],"predecessor-version":[{"id":41127,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1953\/revisions\/41127"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1953"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1953"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1953"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1953"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}