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 – Adding primary keys to makes sure rows are unique.
- Referential Data Integrity – Adding foreign keys to make sure relationships are maintained.
- Domain Data Integrity – Creating constraints or triggers enforce the range of data values.
Again, we will be working with the Boy Scouts of America (BSA) hypothetical database.
The code snipets below add PRIMARY KEYS 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 |
<span style="color: #008000;">-- * -- * Address table 1 -- * -- Which database to use. USE [BSA] GO -- Delete existing primary key IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[RECENT].[TBL_ADDRESS]') AND name = N'PK_TBL_ADDRESS') ALTER TABLE [RECENT].[TBL_ADDRESS] DROP CONSTRAINT [PK_TBL_ADDRESS] GO -- Add new primary key ALTER TABLE [RECENT].[TBL_ADDRESS] ADD CONSTRAINT [PK_TBL_ADDRESS] PRIMARY KEY CLUSTERED ([ADDRESS_ID] ASC) WITH (FILLFACTOR = 75, PAD_INDEX = ON) ON [PRIMARY]; -- * -- * Merit badge table 2 -- * -- Which database to use. USE [BSA] GO -- Delete existing primary key IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[RECENT].[TBL_MERIT_BADGE]') AND name = N'PK_TBL_MERIT_BADGE') ALTER TABLE [RECENT].[TBL_MERIT_BADGE] DROP CONSTRAINT [PK_TBL_MERIT_BADGE] GO -- Add new primary key ALTER TABLE [RECENT].[TBL_MERIT_BADGE] ADD CONSTRAINT [PK_TBL_MERIT_BADGE] PRIMARY KEY CLUSTERED ([BADGE_ID] ASC) WITH (FILLFACTOR = 75, PAD_INDEX = ON) ON [PRIMARY]; GO -- * -- * Rank table 3 -- * -- Which database to use. USE [BSA] GO -- Delete existing primary key IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[RECENT].[TBL_RANK]') AND name = N'PK_TBL_RANK') ALTER TABLE [RECENT].[TBL_RANK] DROP CONSTRAINT [PK_TBL_RANK] GO -- Add new primary key ALTER TABLE [RECENT].[TBL_RANK] ADD CONSTRAINT [PK_TBL_RANK] PRIMARY KEY CLUSTERED ([RANK_ID] ASC) WITH (FILLFACTOR = 75, PAD_INDEX = ON) ON [PRIMARY]; GO -- * -- * Scout table 4 -- * -- Which database to use. USE [BSA] GO -- Delete existing primary key IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT]') AND name = N'PK_TBL_SCOUT') ALTER TABLE [RECENT].[TBL_SCOUT] DROP CONSTRAINT [PK_TBL_SCOUT] GO -- Add new primary key ALTER TABLE [RECENT].[TBL_SCOUT] ADD CONSTRAINT [PK_TBL_SCOUT] PRIMARY KEY CLUSTERED ([SCOUT_ID] ASC) WITH (FILLFACTOR = 75, PAD_INDEX = ON) ON [PRIMARY]; GO -- * -- * Scout earns merit badge table 5 -- * -- Which database to use. USE [BSA] GO -- Delete existing primary key IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT_EARNS_BADGE]') AND name = N'PK_TBL_SCOUT_EARNS_BADGE') ALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] DROP CONSTRAINT [PK_TBL_SCOUT_EARNS_BADGE] GO -- Add new primary key ALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] ADD CONSTRAINT [PK_TBL_SCOUT_EARNS_BADGE] PRIMARY KEY CLUSTERED ( [SCOUT_ID] ASC, [BADGE_ID] ASC ) WITH (FILLFACTOR = 75, PAD_INDEX = ON) ON [PRIMARY]; GO</span> |
The code snipets below adds FORIEGN KEYS 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
<span style="color: #008000;">-- * -- * Scout table -- * -- ** Foreign Key 1 ** -- Which database to use. USE [BSA] GO -- Delete existing foreign key 1 IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[RECENT].[FK_TBL_SCOUT1]') AND parent_object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT]')) ALTER TABLE [RECENT].[TBL_SCOUT] DROP CONSTRAINT [FK_TBL_SCOUT1] GO -- Add new foreign key 1 ALTER TABLE [RECENT].[TBL_SCOUT] WITH CHECK ADD CONSTRAINT [FK_TBL_SCOUT1] FOREIGN KEY([ADDRESS_ID]) REFERENCES [RECENT].[TBL_ADDRESS] ([ADDRESS_ID]) GO ALTER TABLE [RECENT].[TBL_SCOUT] CHECK CONSTRAINT [FK_TBL_SCOUT1] GO -- ** Foreign Key 2 ** -- Which database to use. USE [BSA] GO -- Delete existing foreign key 2 IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[RECENT].[FK_TBL_SCOUT2]') AND parent_object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT]')) ALTER TABLE [RECENT].[TBL_SCOUT] DROP CONSTRAINT [FK_TBL_SCOUT2] GO -- Add new foreign key 2 ALTER TABLE [RECENT].[TBL_SCOUT] WITH CHECK ADD CONSTRAINT [FK_TBL_SCOUT2] FOREIGN KEY([RANK_ID]) REFERENCES [RECENT].[TBL_RANK] ([RANK_ID]) GO ALTER TABLE [RECENT].[TBL_SCOUT] CHECK CONSTRAINT [FK_TBL_SCOUT2] GO -- * -- * Scout earns merit badge table -- * -- ** Foreign Key 3 ** -- Which database to use. USE [BSA] GO -- Delete existing foreign key 3 IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[RECENT].[FK_TBL_SEB1]') AND parent_object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT_EARNS_BADGE]')) ALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] DROP CONSTRAINT [FK_TBL_SEB1] GO -- Add new foreign key 3 ALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] WITH CHECK ADD CONSTRAINT [FK_TBL_SEB1] FOREIGN KEY([SCOUT_ID]) REFERENCES [RECENT].[TBL_SCOUT] ([SCOUT_ID]) GO ALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] CHECK CONSTRAINT [FK_TBL_SEB1] GO -- ** Foreign Key 4 ** -- Which database to use. USE [BSA] GO -- Delete existing foreign key 4 IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[RECENT].[FK_TBL_SEB2]') AND parent_object_id = OBJECT_ID(N'[RECENT].[TBL_SCOUT_EARNS_BADGE]')) ALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] DROP CONSTRAINT [FK_TBL_SEB2] GO -- Add new foreign key 4 ALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] WITH CHECK ADD CONSTRAINT [FK_TBL_SEB2] FOREIGN KEY([BADGE_ID]) REFERENCES [RECENT].[TBL_MERIT_BADGE] ([BADGE_ID]) GO ALTER TABLE [RECENT].[TBL_SCOUT_EARNS_BADGE] CHECK CONSTRAINT [FK_TBL_SEB2] GO</span> |
There are two ways to enforce domain data integrity. The first way to enforce this integrity is by adding a CHECK CONSTRAINT. For our BSA database, will will only allow records that have a state value of RI since that is where the organization resides.
The code snipet below does the following.
- Drop existing constraint
- Create the new constraint
- Insert one record in the table
- Update that record, this will fail
- Remove the record for the next demo
The main point to observe is that data is never entered into the table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
<span style="color: #008000;">-- ** Check constraint ** -- Which database to use. USE [BSA] GO -- Delete check constraint IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[RECENT].[CHK_TBL_ADDRESS1]') AND parent_object_id = OBJECT_ID(N'[RECENT].[TBL_ADDRESS]')) ALTER TABLE [RECENT].[TBL_ADDRESS] DROP CONSTRAINT [CHK_TBL_ADDRESS1] GO -- Add check constraint ALTER TABLE [RECENT].[TBL_ADDRESS] ADD CONSTRAINT CHK_TBL_ADDRESS1 CHECK (STATE_TXT = 'RI'); GO -- Champlin Scout Reservation INSERT INTO [RECENT].[TBL_ADDRESS] ( [STREET1_TXT], [STREET2_TXT], [CITY_TXT], [STATE_TXT], [ZIP_CODE_TXT] ) VALUES ( '233 Scituate Avenue', '', 'Cranston', 'RI', '02921' ) GO -- Update data in the table UPDATE [RECENT].[TBL_ADDRESS] SET [STATE_TXT] = 'RX'; GO -- Remove records from table DELETE FROM [RECENT].[TBL_ADDRESS]; GO</span> |
The second way to enforce this integrity is by adding using the CREATE TRIGGER statement to
monitor 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.
The code snipet below does the following.
- Drop existing trigger
- Create the new trigger
- Insert one record in the table, this will fail
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
<span style="color: #008000;">-- ** Insert Trigger ** -- Delete existing trigger IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[RECENT].[TRG_CHECK_STATE]')) DROP TRIGGER [RECENT].[TRG_CHECK_STATE] GO -- Add insert trigger CREATE TRIGGER TRG_CHECK_STATE ON [RECENT].[TBL_ADDRESS] FOR INSERT, UPDATE AS BEGIN IF EXISTS (select * from inserted where [STATE_TXT] <> 'RI') BEGIN DECLARE @MyMsg varchar(250) = 'All boy scout addresses have to be in the State Of Rhode Island!' ROLLBACK TRANSACTION; RAISERROR (@MyMsg, 15, 1); RETURN; END END -- Champlin Scout Reservation INSERT INTO [RECENT].[TBL_ADDRESS] ( [STREET1_TXT], [STREET2_TXT], [CITY_TXT], [STATE_TXT], [ZIP_CODE_TXT] ) VALUES ( '233 Scituate Avenue', '', 'Cranston', 'RX', '02921' ) GO </span> |
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.
I will continue this business solution next time by talking about different ways to insert data into the database.
You are my aspiration , I own few web logs and sometimes run out from to post .