-- пересоздаём таблицы
drop table TCSpecItemParams
drop table TCSpecItems
drop table TCSpecs
GO
CREATE TABLE TCSpecs
(
	SpecID			int NOT NULL,
        SpecNumber		nvarchar(50) NOT NULL,
	IssueDate		datetime,
	ExpDate		 	datetime,
	ExecutorPosition	nvarchar(100),
	ExecutorName		nvarchar(100),
	ExecutorContactInfo	nvarchar(100),
        AdditionalInfo		nvarchar(1024),
	PRIMARY KEY (SpecID)	
)
GO
CREATE TABLE TCSpecItems
(
	SpecID		int NOT NULL,
	ItemNum		int NOT NULL,
	WorkTypeID	int NOT NULL,
	Details		nvarchar(200),
	ForExecutor	bit NULL,	
	PRIMARY KEY (SpecID, ItemNum)
)
GO
CREATE TABLE TCSpecItemParams
(
	SpecID		int NOT NULL,
	ItemNum		int NOT NULL,
	WorkTypeID	int NOT NULL,
	ParamNum	int NOT NULL,
	ParamValue	nvarchar(100) NOT NULL,
	PRIMARY KEY(SpecID, ItemNum, WorkTypeID, ParamNum)
)
GO
---- пересоздаём связи
ALTER TABLE TCSpecs
	ADD FOREIGN KEY (SpecID)
	REFERENCES TCApplications(TCApplicationID)
	ON DELETE CASCADE
        ON UPDATE CASCADE
ALTER TABLE TCSpecItems
	ADD FOREIGN KEY (SpecID)
	REFERENCES TCSpecs(SpecID)
	ON DELETE CASCADE
        ON UPDATE CASCADE

ALTER TABLE TCSpecItems
	ADD FOREIGN KEY (WorkTypeID)
	REFERENCES TCWorkTypes(WorkTypeID)	
ALTER TABLE TCSpecItemParams
	ADD FOREIGN KEY (SpecID, ItemNum)
	REFERENCES TCSpecItems(SpecID, ItemNum)
	ON DELETE CASCADE

ALTER TABLE TCSpecItemParams
	ADD FOREIGN KEY (WorkTypeID, ParamNum)
	REFERENCES TCWorkTypeParams(WorkTypeID, ParamNum)		



