Error when adding foreign key in SQL table

Question

Hello, I am trying to add a foreign key to an already created table and it gives the following error:

Error(s), Warning(s):

Incorrect syntax near the keyword 'Foreign'.

I tried some modifications in the syntax, but I am not able to correct the error.

That’s the code I’m using:

create table justificativa
(
    CODIGO              char(5)     not null, 
    IDSt                char(5)     not null,
    primary key(CODIGO)
);

create table situacao
(
    IDSt                char(5)     not null,   
    ORIGEM          char(5) not null,
    CODIGO              char(5)     not null,
    IDC     char(5)             not null,
    primary key(IDSt),
    foreign key (CODIGO) references justificativa
);


ALTER TABLE justificativa
ADD FOREIGN KEY (IDSt)
REFERENCES   situacao(IDSt);

Does anyone have any idea why this is happening?

I’m using rextester.com to run the code.

Answer 1

I believe that the column was missing in the creation of the table Situation or else your database does not support the syntax you are trying to use.

You can do this in Mysql:

create table situacao
(
    IDSt char(5) not null,   
    ORIGEM char(5) not null,
    CODIGO char(5) not null,
    IDC char(5) not null,
    primary key(IDSt),
    foreign key (CODIGO) references justificativa(CODIGO)
);

In other databases, the syntax may vary a bit (I tested it in SQL Server here):

create table situacao
(
    IDSt char(5) not null,   
    ORIGEM char(5) not null,
    CODIGO char(5) not null foreign key references justificativa(CODIGO),
    IDC char(5) not null,
    primary key(IDSt)
);

When it comes to foreign keys, I like to define it outside the table creation, changing the table created with alter table to avoid referencing a table that has not yet been created and result in some error:

create table situacao
(
    IDSt char(5) not null,   
    ORIGEM char(5) not null,
    CODIGO char(5) not null,
    IDC char(5) not null,
    primary key(IDSt)
);    

ALTER TABLE situacao
ADD FOREIGN KEY (IDSt)
REFERENCES justificativa(CODIGO);

---

I am considering that this example is for didactic purposes, because a table referring to generates a circular dependency.