Object Dependency and Dropped Tables

Here’s what is going to happen today: we’re going to bring a bunch of objects to life, and then we’re going to kill them.

Woah, why all the violence?  In our databases, we have lots of objects.  New ones are created all the time, then changed, and sometimes deleted.  These objects are connected to one another!  What happens when an object is removed from the database, but other objects depend on it?  Let’s see.

A basic object is a table.  Create two to use as a foundation of the experiment. Here, each table is created with a child object: a key.

Transact-SQL

CREATE TABLE dbo.ItLives ( ID INT IDENTITY , Value NVARCHAR(50) CONSTRAINT [PK_ItLives] PRIMARY KEY (ID ASC) ) ; CREATE TABLE dbo.Alive ( ID INT , Value NVARCHAR(50) CONSTRAINT [PK_Alive] PRIMARY KEY (ID ASC) ) ; GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

CREATE TABLE dbo.ItLives

(

ID INT IDENTITY ,

Value NVARCHAR(50)

CONSTRAINT [PK_ItLives] PRIMARY KEY

(ID ASC)

) ;

CREATE TABLE dbo.Alive

(

ID INT ,

Value NVARCHAR(50)

CONSTRAINT [PK_Alive] PRIMARY KEY

(ID ASC)

) ;

GO

Next up, create a few more objects that are based on these two tables.

We’ll start with a foreign key to connect the ID columns of both the tables.

Then there’s a trigger to alert a user that maybe it would be best not to change the data in one of our brand-new tables. Finally, a view that displays rows from both the tables together.

Transact-SQL

-- Create a foreign key ALTER TABLE dbo.ItLives ADD CONSTRAINT FK_AliveID_ItLivesID FOREIGN KEY (ID) REFERENCES dbo.Alive (ID) ; GO -- Create a trigger CREATE TRIGGER DoNotTouchThisTable ON dbo.ItLives AFTER INSERT, UPDATE, DELETE AS PRINT 'Please do not touch this table' ; GO -- Create a view CREATE VIEW dbo.ViewTheLiving AS SELECT TOP (10) ItLives.ID FROM dbo.ItLives INNER JOIN dbo.Alive ON ItLives.ID = Alive.ID WHERE ItLives.ID < 10 ; GO