--------------------------------------------------- -- 건물/층 관리 --------------------------------------------------- --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) --;