IF EXISTS(select sysobjects.id from sysconstraints inner join sysobjects on sysobjects.id = constid where sysobjects.name = 'FKe_STOK_GRUP_STOKGRUP_KODU' and xtype='F')
	ALTER TABLE dbo.STOK
		DROP CONSTRAINT FKe_STOK_GRUP_STOKGRUP_KODU
GO

IF EXISTS(select sysobjects.id from sysconstraints inner join sysobjects on sysobjects.id = constid where sysobjects.name = 'FK_STOK_STOKGRUP_STOKGRUP_KODU' and xtype='F')
	ALTER TABLE dbo.STOK
		DROP CONSTRAINT FK_STOK_STOKGRUP_STOKGRUP_KODU
GO

IF EXISTS(select sysobjects.id from sysconstraints inner join sysobjects on sysobjects.id = constid where sysobjects.name = 'FK_STOK_STOKGRUP' and xtype='F')
	ALTER TABLE dbo.STOK
		DROP CONSTRAINT FK_STOK_STOKGRUP
GO

IF EXISTS(select sysobjects.id from sysconstraints inner join sysobjects on sysobjects.id = constid where sysobjects.name = 'FK_STOKGRUPSUBE_STOKGRUP' and xtype='F')
BEGIN
	DECLARE 
		@CMD nvarchar(500)
	SET @CMD = N'ALTER TABLE dbo.STOKGRUPSUBE
			DROP CONSTRAINT FK_STOKGRUPSUBE_STOKGRUP'
	PRINT 'FK_STOKGRUPSUBE_STOKGRUP siliniyor...'
	EXEC (@CMD)
END		
GO

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[TBL_TALEPNET_BUTCEDETAY]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tbl_TALEPNET_ButceDetay_STOKGRUP]') AND parent_object_id = OBJECT_ID(N'[dbo].[tbl_TALEPNET_BUTCEDETAY]'))
ALTER TABLE [dbo].[tbl_TALEPNET_BUTCEDETAY] DROP CONSTRAINT [FK_tbl_TALEPNET_ButceDetay_STOKGRUP]
GO

DECLARE 
	@PRIMARYKEYCONST nvarchar(100),
	@CMD nvarchar(500)

select @PRIMARYKEYCONST = constobject.name
               from sysconstraints inner join 
                    sysobjects as constobject on constobject.id = constid inner join 
                    sysobjects as tableobject on tableobject.id = sysconstraints.id  
                    where constobject.xtype='PK' and tableobject.name = 'STOKGRUP'
                    	
IF @PRIMARYKEYCONST IS NOT NULL
BEGIN
SET @CMD = N'ALTER TABLE dbo.STOKGRUP
	DROP CONSTRAINT ' + @PRIMARYKEYCONST
	PRINT @PRIMARYKEYCONST + ' siliniyor...'
	EXEC (@CMD)
END


ALTER TABLE dbo.STOKGRUP ALTER COLUMN
	KODU nvarchar(20) NOT NULL
GO
ALTER TABLE dbo.STOKGRUP ADD CONSTRAINT
	STOKGRUP_pk PRIMARY KEY CLUSTERED 
	(
	KODU
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'IX_STOK_KODU_TURU_KKALDIR_GRUP')
   DROP INDEX STOK.IX_STOK_KODU_TURU_KKALDIR_GRUP
GO
IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'IX_STOK_GRUP')
   DROP INDEX STOK.IX_STOK_GRUP
GO
IF EXISTS (SELECT name FROM sysindexes 
      WHERE name = 'IXF_STOK')
   DROP INDEX STOK.IXF_STOK
GO

ALTER TABLE dbo.STOK ALTER COLUMN
	GRUP nvarchar(20) NULL
GO

CREATE NONCLUSTERED INDEX IX_STOK_KODU_TURU_KKALDIR_GRUP ON dbo.STOK
	(
	KODU,
	TURU,
	KKALDIR,
	GRUP
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_STOK_GRUP ON dbo.STOK
	(
	GRUP
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

UPDATE STOK SET GRUP = NULL WHERE GRUP IS NOT NULL AND NOT EXISTS (SELECT KODU FROM STOKGRUP WHERE KODU = STOK.GRUP)
GO

ALTER TABLE dbo.STOK ADD CONSTRAINT
	FK_STOK_STOKGRUP FOREIGN KEY
	(
	GRUP
	) REFERENCES dbo.STOKGRUP
	(
	KODU
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
GO

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[TBL_TALEPNET_BUTCEDETAY]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
ALTER TABLE [dbo].[tbl_TALEPNET_BUTCEDETAY]  WITH CHECK ADD  CONSTRAINT [FK_tbl_TALEPNET_ButceDetay_STOKGRUP] FOREIGN KEY([StokGrupId])
REFERENCES [dbo].[STOKGRUP] ([KODU])
GO

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[TBL_TALEPNET_BUTCEDETAY]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
ALTER TABLE [dbo].[tbl_TALEPNET_BUTCEDETAY] CHECK CONSTRAINT [FK_tbl_TALEPNET_ButceDetay_STOKGRUP]
GO

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[STOKGRUPSUBE]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_STOKGRUPSUBE_STOKGRUP]') AND parent_object_id = OBJECT_ID(N'[dbo].[STOKGRUPSUBE]'))
ALTER TABLE [dbo].[STOKGRUPSUBE]  WITH CHECK ADD  CONSTRAINT [FK_STOKGRUPSUBE_STOKGRUP] FOREIGN KEY([STOKGRUPKODU])
REFERENCES [dbo].[STOKGRUP] ([KODU])
GO

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[DBO].[STOKGRUPSUBE]') AND OBJECTPROPERTY(ID, N'ISUSERTABLE') = 1)
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_STOKGRUPSUBE_STOKGRUP]') AND parent_object_id = OBJECT_ID(N'[dbo].[STOKGRUPSUBE]'))
ALTER TABLE [dbo].[STOKGRUPSUBE] CHECK CONSTRAINT [FK_STOKGRUPSUBE_STOKGRUP]
GO

