123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960 |
- ---------------------------------------------------
- -- 건물/층 관리
- ---------------------------------------------------
- --CREATE TABLE InbuBuilding
- 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)
- ;
- --ALTER TABLE CmBuilding
- -- ADD CONSTRAINT FK__CmBuilding__CmFile FOREIGN KEY (SiteId,FileId) REFERENCES CmFile(SiteId,FileId)
- --;
- --ALTER TABLE CmFloor
- -- ADD CONSTRAINT FK__CmFloor__CmFile FOREIGN KEY (SiteId,FileId) REFERENCES CmFile(SiteId,FileId)
- --;
|