T SQL - Basic Inheritance

Post date: Oct 12, 2010 7:51:49 PM

use masterdrop database InheritanceTest create database InheritanceTest use InheritanceTest create table ItemType( ItemTypeID int identity(1, 1) PRIMARY KEY, ItemType varchar(50) not null);create table Item( ItemID int identity(1,1), ItemTypeID int references ItemType(ItemTypeID) NOT NULL, Name varchar(500) not null, Created date not null default(getdate()) , primary key(ItemID) , constraint Item_AltPK unique(ItemID, ItemTypeID));create table Product( ProductID int not null, ItemTypeID as 1 persisted, Cost decimal(10, 2) not null, primary key(ProductID) , foreign key(ProductID, ItemTypeID) references Item(ItemID, ItemTypeID));create table Article( ArticleID int not null, ItemTypeID as 2 persisted, primary key(ArticleID) , foreign key(ArticleID, ItemTypeID) references Item(ItemID, ItemTypeID));create table Service( ServiceID int not null, ItemTypeID as 3 persisted, primary key(ServiceID) , foreign key(ServiceID, ItemTypeID) references Item(ItemID, ItemTypeID));insert ItemType(ItemType) values('Product')insert ItemType(ItemType) values('Article')insert ItemType(ItemType) values('Service')Insert Item(Name, ItemTypeID) select 'Dogfood', 1insert Product(ProductID, Cost) select SCOPE_IDENTITY(), 25.5insert Item(Name, ItemTypeID) select 'SQL Inheritance abc', 2insert Article(ArticleID) select SCOPE_IDENTITY()select * from Item,Product where Item.ItemTypeID = 1select * from Item,Article where Item.ItemTypeID = 2-- test the FKupdate Item set ItemTypeID = 2