Foreign Key for Non-Existent Field

[Former Member]

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?

Thanks.

Dave.

[MarkCulp]

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.

[former_SQLA_member1694880]

TBH I can't imagine real use for such a behaviour...

[VolkerBarth]

To cite Glenn from the comment noted in my answer:

Adding the column on-the-fly with CREATE or ALTER is a long-standing SQL feature of SQL Anywhere that, frankly, I wish had not been implemented.[...]

Personally, I consider this one of the - not so many - nasty pitfalls with SQL Anywhere I now and then stumble over...

[VolkerBarth]

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):

Using the following statement, you create a foreign key without specifying columns for either the primary or foreign table:

ALTER TABLE Table2 ADD FOREIGN KEY fk3 REFERENCES Table1;

Since you did not specify referencing columns, the database server looks for columns in the foreign table (Table2) with the same name as columns in the primary table (Table1). If they exist, it ensures that the data types match and then creates the foreign key using those columns. If columns do not exist, they are created in Table2. In this example, Table2 does NOT have columns called a and b so they are created with the same data types as Table1.a and Table1.b. These automatically-created columns cannot become part of the primary key of the foreign table.

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.