SQL server error creating table

0

I tried to create multiple tables in SQL but 2 of them gave the same error:

  

"Msg 1767, Level 16, State 0, Line 72   Foreign key 'FK__movimento__codem__3118447E' references invalid table "loan".

E

  

"Msg 1750, Level 16, State 0, Line 72.Could not create constraint or index. See previous errors."

These are the two tables:

create table emprestimo(
    codemprestimo smallint,
    dataemprestimo varchar(10),
    valoremprestimo float,
    taxajuro float,
    numeroemprestimo smallint,
    observacoes varchar(100),
    codagencia smallint,
        foreign key(codagencia) references agencia(codagencia)
        on update cascade
        on delete cascade,
        codconta smallint,
        foreign key(codconta) references conta(codconta)
        on update cascade
        on delete cascade,
        primary key(codemprestimo));

create table movimentoconta(
codmovconta smallint,
datamov varchar(10),
valormov float,
codtipomov smallint,
    foreign key(codtipomov) references tipomovimento(codtipomov)
    on update cascade
    on delete cascade,
    codemprestimo smallint,
    foreign key (codemprestimo) references emprestimo(codemprestimo)
    on update cascade
    on delete cascade,
    codclienteconta smallint,
    foreign key(codclienteconta) references clienteconta(codclienteconta)
    on update cascade
    on delete cascade,
    primary key(codmovconta));
    
asked by anonymous 07.05.2018 / 14:50

1 answer

1

For FKs to work in create it is necessary that the referenced table exists, that the referenced field exists and that this field is PK (primary key). Based on your create I put together a script that will help you a bit and identify which of these is not pk, if it does not exist in the table or even if the table exists. If the field is not PK, you will have to change your create script and use a field that is PK.  I hope I have helped.

    Declare @Parametros as table(id int identity(1,1),tabela varchar(100),campo varchar(100))
    Declare @tabela varchar(100)
    Declare @campo varchar(100)
    Declare @id int
    Declare @ok int

/* seus campos conforme o create */

    insert into @Parametros
    Values('agencia','codagencia'),
          ('conta','codconta'),
          ('tipomovimento','codtipomov'),
          ('emprestimo','codemprestimo'),
          ('clienteconta','codclienteconta')

    SELECT @ok = count(*) from @Parametros

    Declare @SQL VARCHAR(MAX) = '
    create table emprestimo(
        codemprestimo smallint,
        dataemprestimo varchar(10),
        valoremprestimo float,
        taxajuro float,
        numeroemprestimo smallint,
        observacoes varchar(100),
        codagencia smallint,
            foreign key(codagencia) references agencia(codagencia)
            on update cascade
            on delete cascade,
            codconta smallint,
            foreign key(codconta) references conta(codconta)
            on update cascade
            on delete cascade,
            primary key(codemprestimo));
    GO
    create table movimentoconta(
    codmovconta smallint,
    datamov varchar(10),
    valormov float,
    codtipomov smallint,
        foreign key(codtipomov) references tipomovimento(codtipomov)
        on update cascade
        on delete cascade,
        codemprestimo smallint,
        foreign key (codemprestimo) references emprestimo(codemprestimo)
        on update cascade
        on delete cascade,
        codclienteconta smallint,
        foreign key(codclienteconta) references clienteconta(codclienteconta)
        on update cascade
        on delete cascade,
        primary key(codmovconta));'



    while exists (select top 1 1 from @Parametros)
    begin

    select top 1
            @id = id 
          , @tabela = tabela
          , @campo = campo           
      from @Parametros

    if exists (select top 1 1 from sys.tables where name = @tabela )
    BEGIN
    if exists (SELECT TOP 1 1 FROM sys.sysobjects AS T (NOLOCK) 
                    INNER JOIN sys.all_columns AS C (NOLOCK) ON T.id = C.object_id AND T.XTYPE = 'U' 
                    WHERE T.NAME = @tabela AND C.NAME = @campo )
    BEGIN
    if exists (SELECT TOP 1 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                WHERE TABLE_NAME = @tabela 
                  AND COLUMN_NAME = @campo
                  AND TABLE_SCHEMA ='dbo' )
    BEGIN
    set @ok = @ok - 1
    if @ok <= 0
    begin
    SELECT 'Create OK, rode o script ==> ', @SQL as Script
    end
    END ELSE BEGIN select ('CAMPO INFORMADO ' + @campo + ' NÃO É PK') break; END
    END ELSE BEGIN select ('CAMPO '+ @campo +' NÃO EXISTE NA TABELA') break; END
    END ELSE BEGIN select ('TABELA '+ @tabela +' NÃO EXISTE')         break; END

     delete from @Parametros where id = @id 

    end
    
08.05.2018 / 22:58