Just Code‎ > ‎

T SQL - Basic Inheritance

posted Oct 12, 2010, 12:51 PM by Peter Henell
use master
drop 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', 1
insert Product(ProductID, Cost) select SCOPE_IDENTITY(), 25.5


insert Item(Name, ItemTypeID) select 'SQL Inheritance abc', 2
insert Article(ArticleID) select SCOPE_IDENTITY()



select * from Item,Product where Item.ItemTypeID = 1
select * from Item,Article where Item.ItemTypeID = 2

-- test the FK
update Item set ItemTypeID = 2
Comments