Foreign Key for Non-Existent Field

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.

foreign-key

asked 29 Mar '12, 14:47

dkeisen
61●1●2●3
accept rate: 0%

flat view

2 Answers:

active answers oldest answers newest answers popular answers

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 Culp
24.9k●10●141●297
accept rate: 41%

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

flat view

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 Barth
40.2k●361●550●822
accept rate: 34%

edited 30 Mar '12, 04:09

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

foreign-key ×31

question asked: 29 Mar '12, 14:47

question was seen: 2,904 times

last updated: 30 Mar '12, 06:22

SAP SQL Anywhere

Foreign Key for Non-Existent Field

Follow this question

Related questions