SQL server error to create table

Asked

Viewed 187 times

0

I tried to create several 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 "emprestimo".

And

"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));
sql server table
    1. The table agencia already exists? The field codagencia the table agencia, is primary key? 2. You need to create the field codemprestimo, on the table movimentoconta so that it is referenced, and primary key. All this if you really want to put the foreign key

  • the type table exists ? What is its PK ? I don’t know the syllabus but also make columns and indices separately

1 answer

2

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 built a script that will help you a little 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’ve 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

Browser other questions tagged sql server table

You are not signed in. Login or sign up in order to post.