An ALTER TABLE statement creating a foreign key using a non-existent column creates that column automatically. I would expect an error to be returned. The behavior we see is error prone in the presence of typos.
To be more concrete, I refer to the following SQL:
CREATE TABLE master( id INT, v VARCHAR(40), PRIMARY KEY (id) );
CREATE TABLE detail( id INT, fk INT, PRIMARY KEY (id) );
ALTER TABLE detail ADD FOREIGN KEY detail_fk(fkbadcolumn) REFERENCES master(id);
This creates a new column "fkbadcolumn" in the detail table.
Is this expected? Is there some way to disable this behavior?
asked 29 Mar '12, 14:47
The behaviour that you are seeing is expected and exists in the product for historical backward compatibility. AFAIK you cannot disable this behaviour. (This behaviour should be mentioned in the docs... but I could not find it. If I find the reference I will update this answer).
Having said that, I agree with you that the behaviour is unexpected.
answered 29 Mar '12, 14:54
To add to Mark's response:
It's documented (somewhat "hidden", I would claim), here in the v12.0.1. docs (emphasis added by me):
And it has been "ranted" about several times in past, too, cf. the comments on this question...
EDIT: I have to correct myself: The cited reference documents a similar, but different situation, i.e. leaving out the column name in the FK definition, whereas the question here deals with specifying a non-existing column. Nevertheless, the "auto-generation" of the "missing column" seems identical.