123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960 |
- CREATE TABLE CmBuilding
- (
- SiteId int NOT NULL ,
- BuildingId int identity(1,1) NOT NULL ,
- Name nvarchar(50) NOT NULL ,
-
- PRIMARY KEY (SiteId ASC , BuildingId ASC)
- );
- DBCC CHECKIDENT( CmBuilding, RESEED, 1 )
- CREATE TABLE CmFloor
- (
- SiteId int NOT NULL ,
- BuildingId int NOT NULL ,
- FloorId int identity(1,1) NOT NULL ,
- Name nvarchar(50) NOT NULL ,
- PRIMARY KEY (SiteId ASC , BuildingId ASC , FloorId ASC)
- );
- DBCC CHECKIDENT( CmFloor, RESEED, 1 )
- CREATE TABLE CmZone
- (
- SiteId int NOT NULL ,
- BuildingId int NOT NULL ,
- FloorId int NOT NULL ,
- ZoneId int identity(1,1) NOT NULL ,
- Name nvarchar(50) NOT NULL ,
-
- PRIMARY KEY (SiteId ASC , BuildingId ASC , FloorId ASC , ZoneId ASC)
- );
- DBCC CHECKIDENT( CmZone, RESEED, 1 )
- ALTER TABLE CmBuilding
- ADD CONSTRAINT FK__CmBuilding__CmSite FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
- ;
- ALTER TABLE CmFloor
- ADD CONSTRAINT FK__CmFloor_CmBuilding FOREIGN KEY (SiteId , BuildingId) REFERENCES CmBuilding(SiteId , BuildingId)
- ;
- ALTER TABLE CmZone
- ADD CONSTRAINT FK__CmZone__CmBuilding FOREIGN KEY (SiteId , BuildingId) REFERENCES CmBuilding(SiteId , BuildingId)
- ;
- ALTER TABLE CmZone
- ADD CONSTRAINT FK__CmZone__CmFloor FOREIGN KEY (SiteId , BuildingId , FloorId) REFERENCES CmFloor(SiteId , BuildingId , FloorId)
- ;
|