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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 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
To prove that we’re ready to go, that each of these objects really has been created, we’ll query the dynamic management views for each one. If we get an OBJECT_ID, the object exists. If the OBJECT_ID is null, then the object does not exist.
Transact-SQL
SELECT OBJECT_ID('PK_ItLives') AS 'ObjectID' UNION ALL SELECT OBJECT_ID('FK_AliveID_ItLivesID') UNION ALL SELECT OBJECT_ID('DoNotTouchThisTable') UNION ALL SELECT OBJECT_ID('ViewTheLiving') UNION ALL SELECT OBJECT_ID('DoesNotExist') ;
1
2
3
4
5
SELECT OBJECT_ID('PK_ItLives') AS 'ObjectID' UNION ALL
SELECT OBJECT_ID('FK_AliveID_ItLivesID') UNION ALL
SELECT OBJECT_ID('DoNotTouchThisTable') UNION ALL
SELECT OBJECT_ID('ViewTheLiving') UNION ALL
SELECT OBJECT_ID('DoesNotExist') ;
The result set from this query is: [table “3” not found /]
The objects we created today – the primary keys, foreign keys, indexes, triggers, and views – all rely on the table ItLives. What would happen to our innocent objects if this table were to disappear?
Transact-SQL
DROP TABLE dbo.ItLives ;
1
DROP TABLE dbo.ItLives ;
We then run the same query for OBJECT_ID on our objects to see which of them survived the damage.
The result set is now: [table “4” not found /]
The DROP TABLE command implicitly drops from the database any objects that would be meaningless without that table. However, it does not drop views. I also tested a view that was composed only of the table that gets dropped, and this view also stays around. Just in case there’s more than one table in a view, SSMS leaves views alone unless they are dropped explicitly.
Keep this in mind when you’re playing with the life of your objects!