{"id":690,"date":"2011-08-23T20:19:39","date_gmt":"2011-08-23T20:19:39","guid":{"rendered":"http:\/\/craftydba.com\/?p=690"},"modified":"2012-12-28T17:39:33","modified_gmt":"2012-12-28T17:39:33","slug":"crafting-databases-%e2%80%93-part-4","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=690","title":{"rendered":"Crafting Databases \u2013 Part 4"},"content":{"rendered":"<p>The <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms190273.aspx\">ALTER TABLE<\/a> statement is part of the Data Definition Language (DDL). The statement is used to modify a table after it has been created. Today, I would like to talk about the three integrity concerns that data modelers deal with.<\/p>\n<p>Integrity Concerns<\/p>\n<ul>\n<li><a href=\"http:\/\/en.wikipedia.org\/wiki\/Entity_integrity\">Entity Data Integrity <\/a>&#8211; Adding primary keys to makes sure rows are unique.<\/li>\n<li><a href=\"http:\/\/en.wikipedia.org\/wiki\/Referential_integrity\">Referential Data Integrity <\/a>&#8211; Adding foreign keys to make sure relationships are maintained.<\/li>\n<li><a href=\"http:\/\/en.wikipedia.org\/wiki\/Domain_integrity\">Domain Data Integrity <\/a>&#8211; Creating constraints or triggers enforce the range of data values.<\/li>\n<\/ul>\n<p>Again, we will be working with the Boy Scouts of America (BSA) hypothetical database.<\/p>\n<p>The code snipets below add <a href=\"http:\/\/en.wikipedia.org\/wiki\/Unique_key#Defining_primary_keys\">PRIMARY KEYS <\/a>to all five tables using the ADD CONSTRAINT clause. Please note that primary key (PK) constraints are defined in the sys.indexes table since they default to a clustered index. Since PK is an index, optional parameters such as fill factor, pad index, or etc can be supplied. I will talk more about these options when indexes are covered in a later talk.<\/p>\n<pre><span style=\"color: #008000;\">-- *\r\n-- * Address table 1\r\n-- *\r\n\r\n-- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Delete existing primary key\r\nIF EXISTS (SELECT * FROM sys.indexes \r\nWHERE object_id = OBJECT_ID(N'[RECENT].[TBL_ADDRESS]') AND name = N'PK_TBL_ADDRESS')\r\nALTER TABLE [RECENT].[TBL_ADDRESS] DROP CONSTRAINT [PK_TBL_ADDRESS]\r\nGO\r\n\r\n-- Add new primary key\r\nALTER TABLE [RECENT].[TBL_ADDRESS] \r\nADD CONSTRAINT [PK_TBL_ADDRESS] PRIMARY KEY CLUSTERED ([ADDRESS_ID] ASC)\r\nWITH (FILLFACTOR = 75, PAD_INDEX = ON) ON [PRIMARY];\r\n\r\n-- *\r\n-- * Merit badge table 2\r\n-- *\r\n\r\n-- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Delete existing primary key\r\nIF EXISTS (SELECT * FROM sys.indexes \r\nWHERE object_id = OBJECT_ID(N'[RECENT].[TBL_MERIT_BADGE]') AND name = N'PK_TBL_MERIT_BADGE')\r\nALTER TABLE [RECENT].[TBL_MERIT_BADGE] DROP CONSTRAINT [PK_TBL_MERIT_BADGE]\r\nGO\r\n\r\n-- Add new primary key\r\nALTER TABLE [RECENT].[TBL_MERIT_BADGE] \r\nADD CONSTRAINT [PK_TBL_MERIT_BADGE] PRIMARY KEY CLUSTERED ([BADGE_ID] ASC)\r\nWITH (FILLFACTOR = 75, PAD_INDEX = ON) ON [PRIMARY];\r\nGO\r\n\r\n-- *\r\n-- * Rank table 3\r\n-- *\r\n\r\n-- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Delete existing primary key\r\nIF EXISTS (SELECT * FROM sys.indexes \r\nWHERE object_id = OBJECT_ID(N'[RECENT].[TBL_RANK]') AND name = N'PK_TBL_RANK')\r\nALTER TABLE [RECENT].[TBL_RANK] DROP CONSTRAINT [PK_TBL_RANK]\r\nGO\r\n\r\n-- Add new primary key\r\nALTER TABLE [RECENT].[TBL_RANK] \r\nADD CONSTRAINT [PK_TBL_RANK] PRIMARY KEY CLUSTERED ([RANK_ID] ASC)\r\nWITH (FILLFACTOR = 75, PAD_INDEX = ON) ON [PRIMARY];\r\nGO\r\n\r\n-- *\r\n-- * Scout table 4\r\n-- *\r\n\r\n-- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Delete existing primary key\r\nIF EXISTS (SELECT * FROM sys.indexes \r\nWHERE object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT]') AND name = N'PK_TBL_SCOUT')\r\nALTER TABLE [RECENT].[TBL_SCOUT] DROP CONSTRAINT [PK_TBL_SCOUT]\r\nGO\r\n\r\n-- Add new primary key\r\nALTER TABLE [RECENT].[TBL_SCOUT] \r\nADD CONSTRAINT [PK_TBL_SCOUT] PRIMARY KEY CLUSTERED ([SCOUT_ID] ASC)\r\nWITH (FILLFACTOR = 75, PAD_INDEX = ON) ON [PRIMARY];\r\nGO\r\n\r\n-- *\r\n-- * Scout earns merit badge table 5\r\n-- *\r\n\r\n-- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Delete existing primary key\r\nIF EXISTS (SELECT * FROM sys.indexes \r\nWHERE object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT_EARNS_BADGE]') AND name = N'PK_TBL_SCOUT_EARNS_BADGE')\r\nALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] DROP CONSTRAINT [PK_TBL_SCOUT_EARNS_BADGE]\r\nGO\r\n\r\n-- Add new primary key\r\nALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] \r\nADD CONSTRAINT [PK_TBL_SCOUT_EARNS_BADGE] PRIMARY KEY CLUSTERED\r\n(\r\n[SCOUT_ID] ASC,\r\n[BADGE_ID] ASC\r\n)\r\nWITH (FILLFACTOR = 75, PAD_INDEX = ON) ON [PRIMARY];\r\nGO<\/span><\/pre>\n<\/p>\n<p>The code snipets below adds <a href=\"http:\/\/en.wikipedia.org\/wiki\/Foreign_key\">FORIEGN KEYS <\/a>to four tables using the ADD CONSTRAINT clause. Please note that foreign key constraints are defined in the sys.foreign_key table. The WITH CHECK option is used to make sure that a valid relationship is possible with existing table data.<\/p>\n<pre><span style=\"color: #008000;\">-- *\r\n-- * Scout table\r\n-- *\r\n\r\n-- ** Foreign Key 1 **\r\n\r\n-- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Delete existing foreign key 1\r\nIF EXISTS (SELECT * FROM sys.foreign_keys \r\nWHERE object_id = OBJECT_ID(N'[RECENT].[FK_TBL_SCOUT1]') AND parent_object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT]'))\r\nALTER TABLE [RECENT].[TBL_SCOUT] DROP CONSTRAINT [FK_TBL_SCOUT1]\r\nGO\r\n\r\n-- Add new foreign key 1\r\nALTER TABLE [RECENT].[TBL_SCOUT] WITH CHECK \r\nADD CONSTRAINT [FK_TBL_SCOUT1] FOREIGN KEY([ADDRESS_ID])\r\nREFERENCES [RECENT].[TBL_ADDRESS] ([ADDRESS_ID])\r\nGO\r\n\r\nALTER TABLE [RECENT].[TBL_SCOUT] CHECK CONSTRAINT [FK_TBL_SCOUT1]\r\nGO\r\n\r\n-- ** Foreign Key 2 **\r\n\r\n-- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Delete existing foreign key 2\r\nIF EXISTS (SELECT * FROM sys.foreign_keys \r\nWHERE object_id = OBJECT_ID(N'[RECENT].[FK_TBL_SCOUT2]') AND parent_object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT]'))\r\nALTER TABLE [RECENT].[TBL_SCOUT] DROP CONSTRAINT [FK_TBL_SCOUT2]\r\nGO\r\n\r\n-- Add new foreign key 2\r\nALTER TABLE [RECENT].[TBL_SCOUT] WITH CHECK \r\nADD CONSTRAINT [FK_TBL_SCOUT2] FOREIGN KEY([RANK_ID])\r\nREFERENCES [RECENT].[TBL_RANK] ([RANK_ID])\r\nGO\r\n\r\nALTER TABLE [RECENT].[TBL_SCOUT] CHECK CONSTRAINT [FK_TBL_SCOUT2]\r\nGO\r\n\r\n-- *\r\n-- * Scout earns merit badge table\r\n-- *\r\n\r\n-- ** Foreign Key 3 **\r\n\r\n-- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Delete existing foreign key 3\r\nIF EXISTS (SELECT * FROM sys.foreign_keys \r\nWHERE object_id = OBJECT_ID(N'[RECENT].[FK_TBL_SEB1]') AND parent_object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT_EARNS_BADGE]'))\r\nALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] DROP CONSTRAINT [FK_TBL_SEB1]\r\nGO\r\n\r\n-- Add new foreign key 3\r\nALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] WITH CHECK \r\nADD CONSTRAINT [FK_TBL_SEB1] FOREIGN KEY([SCOUT_ID])\r\nREFERENCES [RECENT].[TBL_SCOUT] ([SCOUT_ID])\r\nGO\r\n\r\nALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] CHECK CONSTRAINT [FK_TBL_SEB1]\r\nGO\r\n\r\n-- ** Foreign Key 4 **\r\n\r\n-- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Delete existing foreign key 4\r\nIF EXISTS (SELECT * FROM sys.foreign_keys \r\nWHERE object_id = OBJECT_ID(N'[RECENT].[FK_TBL_SEB2]') AND parent_object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT_EARNS_BADGE]'))\r\nALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] DROP CONSTRAINT [FK_TBL_SEB2]\r\nGO\r\n\r\n-- Add new foreign key 4\r\nALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] WITH CHECK \r\nADD CONSTRAINT [FK_TBL_SEB2] FOREIGN KEY([BADGE_ID])\r\nREFERENCES [RECENT].[TBL_MERIT_BADGE] ([BADGE_ID])\r\nGO\r\n\r\nALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] CHECK CONSTRAINT [FK_TBL_SEB2]\r\nGO<\/span><\/pre>\n<\/p>\n<p>There are two ways to enforce domain data integrity. The first way to enforce this integrity is by adding a <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms179491.aspx\">CHECK CONSTRAINT<\/a>. For our BSA database, will will only allow records that have a state value of RI since that is where the organization resides.<\/p>\n<p>The code snipet below does the following.<\/p>\n<ol>\n<li>Drop existing constraint\u00a0<\/li>\n<li>Create the new constraint<\/li>\n<li>Insert one record in the table<\/li>\n<li>Update that record, this will fail<\/li>\n<li>Remove the record for the next demo<\/li>\n<\/ol>\n<p>The main point to observe is that data is never entered into the table.<\/p>\n<pre><span style=\"color: #008000;\">-- ** Check constraint **\r\n\r\n-- Which database to use.\r\nUSE [BSA]\r\nGO\r\n\r\n-- Delete check constraint\r\nIF EXISTS (SELECT * FROM sys.check_constraints \r\nWHERE object_id = OBJECT_ID(N'[RECENT].[CHK_TBL_ADDRESS1]') AND parent_object_id = OBJECT_ID(N'[RECENT].[TBL_ADDRESS]'))\r\nALTER TABLE [RECENT].[TBL_ADDRESS] DROP CONSTRAINT [CHK_TBL_ADDRESS1]\r\nGO\r\n\r\n-- Add check constraint\r\nALTER TABLE [RECENT].[TBL_ADDRESS]\r\nADD CONSTRAINT CHK_TBL_ADDRESS1 CHECK (STATE_TXT = 'RI');\r\nGO\r\n\r\n-- Champlin Scout Reservation\r\nINSERT INTO [RECENT].[TBL_ADDRESS]\r\n(\r\n[STREET1_TXT],\r\n[STREET2_TXT],\r\n[CITY_TXT],\r\n[STATE_TXT],\r\n[ZIP_CODE_TXT]\r\n) VALUES\r\n(\r\n'233 Scituate Avenue',\r\n'',\r\n'Cranston',\r\n'RI',\r\n'02921'\r\n)\r\nGO\r\n\r\n-- Update data in the table\r\nUPDATE [RECENT].[TBL_ADDRESS]\r\nSET [STATE_TXT] = 'RX';\r\nGO\r\n\r\n-- Remove records from table\r\nDELETE FROM [RECENT].[TBL_ADDRESS];\r\nGO<\/span><\/pre>\n<\/p>\n<p>The second way to enforce this integrity is by adding using the CREATE TRIGGER statement to<br \/>\nmonitor INSERT and UPDATE actions. If any inserted data violates the condition, the action is rolled back. This is different the thec CHECK CONSTRAINT since data makes it to the table and then is removed.<\/p>\n<p>The code snipet below does the following.<\/p>\n<ol>\n<li>Drop existing trigger<\/li>\n<li>Create the new trigger<\/li>\n<li>Insert one record in the table, this will fail<\/li>\n<\/ol>\n<pre><span style=\"color: #008000;\">-- ** Insert Trigger **\r\n\r\n-- Delete existing trigger\r\nIF EXISTS (SELECT * FROM sys.triggers \r\nWHERE object_id = OBJECT_ID(N'[RECENT].[TRG_CHECK_STATE]'))\r\nDROP TRIGGER [RECENT].[TRG_CHECK_STATE]\r\nGO\r\n\r\n-- Add insert trigger\r\nCREATE TRIGGER TRG_CHECK_STATE ON [RECENT].[TBL_ADDRESS]\r\nFOR INSERT, UPDATE AS\r\nBEGIN\r\n    IF EXISTS (select * from inserted where [STATE_TXT] &lt;&gt; 'RI')\r\n    BEGIN\r\n        DECLARE @MyMsg varchar(250) = 'All boy scout addresses have to be in the State Of Rhode Island!'\r\n        ROLLBACK TRANSACTION;\r\n        RAISERROR (@MyMsg, 15, 1);\r\n        RETURN;\r\n    END\r\nEND\r\n\r\n-- Champlin Scout Reservation \r\nINSERT INTO [RECENT].[TBL_ADDRESS]\r\n(\r\n   [STREET1_TXT],\r\n   [STREET2_TXT],\r\n   [CITY_TXT],\r\n   [STATE_TXT],\r\n   [ZIP_CODE_TXT]\r\n) VALUES\r\n( \r\n   '233 Scituate Avenue',\r\n   '',\r\n   'Cranston',\r\n   'RX',\r\n   '02921'\r\n)\r\nGO\r\n<\/span><\/pre>\n<\/p>\n<p>The key points to remember from this article is that Entity, Referential, and Domain integrity is usually implemented by adding constraints to an existing table. Triggers can be used to support the last integrity type; however, extra processing happens to add and remove data from the table.<\/p>\n<p>I will continue this business solution next time by talking about different ways to insert data into the database.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The ALTER TABLE statement is part of the Data Definition Language (DDL). The statement is used to modify a table after it has been created. Today, I would like to talk about the three integrity concerns that data modelers deal with. Integrity Concerns Entity Data Integrity &#8211; Adding primary keys to makes sure rows are unique. Referential Data Integrity &#8211; Adding foreign keys to make sure relationships are maintained. Domain Data Integrity &#8211; Creating constraints or triggers enforce the range of data values. Again, we will be working with the&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[77,79,64,31,78,80,76,12,15,75,28,29,81],"class_list":["post-690","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-add-constraint","tag-add-trigger","tag-alter-table","tag-database-developer","tag-drop-constraint","tag-drop-trigger","tag-foreign-key","tag-free-code","tag-john-f-miner-iii","tag-primary-key","tag-sql-server","tag-tsql","tag-with-check"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/690","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=690"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/690\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=690"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=690"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=690"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}