/*This script create key relationships between tables in myData
	If it does not run sucessfully users should contact their admin for support
*/
--sealed roads elements
ALTER TABLE SealedElementsIRIS
ADD CONSTRAINT uc_SRE_IRIS_RoadNumber_IrisNo UNIQUE (
[roadNumber], [irisNo],[invisible])
go

ALTER TABLE SealedElementsIRIS
ADD CONSTRAINT fk_Existing_Intersection_AssetId_for_Start_Int
FOREIGN KEY (intStart)
REFERENCES IntersectionRoads(assetID)
go

ALTER TABLE SealedElementsIRIS
ADD CONSTRAINT fk_Existing_Intersection_AssetId_for_End_Int
FOREIGN KEY (intEnd)
REFERENCES IntersectionRoads(assetID)
go

--create view for foreign key index
CREATE VIEW [dbo].[SealedElementsIRISRoadNumber]
WITH SCHEMABINDING
AS
SELECT        roadNumber, COUNT_BIG(*) AS COUNT
FROM            dbo.SealedElementsIRIS
GROUP BY roadNumber
GO

CREATE UNIQUE CLUSTERED INDEX IDX_SealedElementsIRISRoadNumber 
    ON [SealedElementsIRISRoadNumber](RoadNumber);
GO

--sealed roads 

ALTER TABLE SealedRoads
ADD CONSTRAINT uc_SR_IRIS_RoadNumber_StartChainage_EndChainage_CarriagewayCode UNIQUE (
roadNumber, startChainage, EndChainage, IRIScarriagewayCode, invisible)
go

ALTER TABLE SealedRoadsIRIS
ADD CONSTRAINT uc_SRI_IRIS_RoadNumber_StartChainage_EndChainage_CarriagewayCode UNIQUE (
roadNumber, startChainage, EndChainage, IRIScarriagewayCode, invisible)
go

/*ALTER TABLE SealedElementsIRIS
ADD CONSTRAINT fk_Existing_Road_Number_in_Elements
FOREIGN KEY (RoadNumber)
REFERENCES SealedElementsIRISRoadNumber(RoadNumber)
go
*/
--intersections

ALTER TABLE IntersectionRoads
ADD CONSTRAINT Check_Numeric_AssetID_1 CHECK(ISNUMERIC(assetID) = 1) 
go