19587bb7f9f0019f0e76ee0db63ec6a9570d4f59.svn-base 2.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
  1. ---------------------------------------------------
  2. -- 건물/층 관리
  3. ---------------------------------------------------
  4. --CREATE TABLE InbuBuilding
  5. CREATE TABLE CmBuilding
  6. (
  7. SiteId int NOT NULL , -- 현장 고유번호
  8. BuildingId int identity(1,1) NOT NULL , -- 빌딩 고유번호
  9. Name nvarchar(50) NOT NULL , -- 빌딩 이름
  10. PRIMARY KEY (SiteId ASC , BuildingId ASC)
  11. );
  12. DBCC CHECKIDENT( CmBuilding, RESEED, 1 )
  13. CREATE TABLE CmFloor
  14. (
  15. SiteId int NOT NULL , -- 현장 고유번호
  16. BuildingId int NOT NULL , -- 빌딩 고유번호
  17. FloorId int identity(1,1) NOT NULL , -- 층 고유번호
  18. Name nvarchar(50) NOT NULL , -- 층 이름
  19. PRIMARY KEY (SiteId ASC , BuildingId ASC , FloorId ASC)
  20. );
  21. DBCC CHECKIDENT( CmFloor, RESEED, 1 )
  22. CREATE TABLE CmZone
  23. (
  24. SiteId int NOT NULL , -- 현장 고유번호
  25. BuildingId int NOT NULL , -- 빌딩 고유번호
  26. FloorId int NOT NULL , -- 층 고유번호
  27. ZoneId int identity(1,1) NOT NULL , -- 존 고유번호
  28. Name nvarchar(50) NOT NULL , -- 존 이름
  29. PRIMARY KEY (SiteId ASC , BuildingId ASC , FloorId ASC , ZoneId ASC)
  30. );
  31. DBCC CHECKIDENT( CmZone, RESEED, 1 )
  32. ALTER TABLE CmBuilding
  33. ADD CONSTRAINT FK__CmBuilding__CmSite FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
  34. ;
  35. ALTER TABLE CmFloor
  36. ADD CONSTRAINT FK__CmFloor_CmBuilding FOREIGN KEY (SiteId , BuildingId) REFERENCES CmBuilding(SiteId , BuildingId)
  37. ;
  38. ALTER TABLE CmZone
  39. ADD CONSTRAINT FK__CmZone__CmBuilding FOREIGN KEY (SiteId , BuildingId) REFERENCES CmBuilding(SiteId , BuildingId)
  40. ;
  41. ALTER TABLE CmZone
  42. ADD CONSTRAINT FK__CmZone__CmFloor FOREIGN KEY (SiteId , BuildingId , FloorId) REFERENCES CmFloor(SiteId , BuildingId , FloorId)
  43. ;
  44. --ALTER TABLE CmBuilding
  45. -- ADD CONSTRAINT FK__CmBuilding__CmFile FOREIGN KEY (SiteId,FileId) REFERENCES CmFile(SiteId,FileId)
  46. --;
  47. --ALTER TABLE CmFloor
  48. -- ADD CONSTRAINT FK__CmFloor__CmFile FOREIGN KEY (SiteId,FileId) REFERENCES CmFile(SiteId,FileId)
  49. --;