-- borro claves y relaciones----------------------------------------------------------------------------------

ALTER TABLE [dbo].[Bancos_Ctas] DROP CONSTRAINT [FK_BCTA_Bancos]
GO

ALTER TABLE [dbo].[Contratos] DROP CONSTRAINT [FK_Cont_Bancos]
GO

DROP INDEX [IX_Cont_PorBco] ON [dbo].[Contratos]
GO

ALTER TABLE [dbo].[Clientes] DROP CONSTRAINT [FK_CLI_Bancos]
GO

DROP INDEX [IX_CLI_PorBco] ON [dbo].[Clientes]
GO

ALTER TABLE [dbo].[Valores] DROP CONSTRAINT [FK_VAL_Bancos]
GO

DROP INDEX [IX_VAL_PorBanco_Id] ON [dbo].[Valores]
GO

DROP INDEX [IX_BCTA_PorBanco_Id] ON [dbo].[Bancos_Ctas]
GO

ALTER TABLE [dbo].[Bancos] DROP CONSTRAINT [PK_BAN_PorId]
GO

DROP INDEX [IX_BCTA_PorTipoCta] ON [dbo].[Bancos_Ctas]
GO


--alter tablas ------------------------------------------------------------------------------------------------------------------

ALTER TABLE Bancos ALTER COLUMN Id_Banco Int NOT NULL
GO

ALTER TABLE Clientes ALTER COLUMN Id_Banco Int
GO

ALTER TABLE Contratos ALTER COLUMN Id_Banco Int
GO

ALTER TABLE Valores ALTER COLUMN Id_Banco Int
GO

ALTER TABLE Bancos_Ctas ALTER COLUMN Id_Banco Int  
GO


--crear claves y relaciones

ALTER TABLE [dbo].[Bancos] ADD  CONSTRAINT [PK_BAN_PorId] PRIMARY KEY 
(
	[Id_Banco] 
)
GO

ALTER TABLE [dbo].[Clientes]  ADD  CONSTRAINT [FK_CLI_Bancos] FOREIGN KEY([Id_Banco])
REFERENCES [dbo].[Bancos] ([Id_Banco])
GO

ALTER TABLE [dbo].[Contratos]   ADD  CONSTRAINT [FK_Cont_Bancos] FOREIGN KEY([Id_Banco])
REFERENCES [dbo].[Bancos] ([Id_Banco])
GO

ALTER TABLE [dbo].[Bancos_Ctas]  ADD  CONSTRAINT [FK_BCTA_Bancos] FOREIGN KEY([Id_Banco])
REFERENCES [dbo].[Bancos] ([Id_Banco])
GO


CREATE UNIQUE NONCLUSTERED INDEX [IX_Cont_PorBco] ON [dbo].[Contratos]
(
	[Id_Banco] ASC,
	[Id_Contrato] ASC
)
GO

CREATE NONCLUSTERED INDEX [IX_CLI_PorBco] ON [dbo].[Clientes]
(
	[Id_Banco] ASC,
	[Razon_Social] ASC,
	[Id_Cliente] ASC
)
GO

CREATE NONCLUSTERED INDEX [IX_VAL_PorBanco_Id] ON [dbo].[Valores]
(
	[Id_Banco] ASC,
	[Id_Valor] ASC
)
GO

CREATE NONCLUSTERED INDEX [IX_BCTA_PorBanco_Id] ON [dbo].[Bancos_Ctas]
(
	[Id_Banco] ASC,
	[Id_Cta_Banco] ASC
)
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_BCTA_PorTipoCta] ON [dbo].[Bancos_Ctas]
(
	[Id_Bco_Cta_Tipo] ASC,
	[Id_Banco] ASC,
	[Id_Cta_Banco] ASC
)
GO

ALTER TABLE [dbo].[Valores] ADD  CONSTRAINT [FK_VAL_Bancos] FOREIGN KEY([Id_Banco])
REFERENCES [dbo].[Bancos] ([Id_Banco])
GO


