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.

asked 29 Mar '12, 14:47

dkeisen's gravatar image

dkeisen
61123
accept rate: 0%


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.

permanent link

answered 29 Mar '12, 14:54

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270
accept rate: 40%

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

(30 Mar '12, 05:15) Dmitri
Replies hidden

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...

(30 Mar '12, 06:20) Volker Barth

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.

permanent link

answered 30 Mar '12, 03:35

Volker%20Barth's gravatar image

Volker Barth
30.6k306456662
accept rate: 32%

edited 30 Mar '12, 04:09

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×22

question asked: 29 Mar '12, 14:47

question was seen: 1,271 times

last updated: 30 Mar '12, 06:22