cancel
Showing results for 
Search instead for 
Did you mean: 

What is wrong with foreign key constraint checks on column tables?

Former Member
0 Kudos

Here is a problem I've faced in the past:

SET SCHEMA DEV_DKH98X8NRPP3883BMMRIRSW8N;

--Create test data

CREATE COLUMN TABLE A(ACOL1 INT, ACOL2 NVARCHAR(10));

ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (ACOL1);

INSERT INTO A(ACOL1, ACOL2) VALUES(1, 'One');

INSERT INTO A(ACOL1, ACOL2) VALUES(2, 'Two');

CREATE COLUMN TABLE B(BCOL1 INT, BREF1 INT, BCOL2 NVARCHAR(10));

ALTER TABLE B ADD CONSTRAINT FK_A_B FOREIGN KEY (BREF1) REFERENCES A(ACOL1);

INSERT INTO B(BCOL1, BREF1, BCOL2) VALUES (10, 1, 'Ref to 1');

INSERT INTO B(BCOL1, BREF1, BCOL2) VALUES (11, 2, 'Ref to 2');

--Check join

SELECT ACOL1, BCOL2

FROM A

JOIN B ON A.ACOL1 = B.BREF1

--Damage the foreign key

ALTER TABLE B ALTER (BREF1 INT NOT NULL); -- why it is permitted?

--Try to remove the foreign key now

ALTER TABLE B DROP CONSTRAINT FK_A_B;

--ERROR:

--Could not execute 'ALTER TABLE B DROP CONSTRAINT FK_A_B' in 48 ms 917 µs .

--SAP DBTech JDBC: [397] (at 30): invalid object name: invalid constraint name: line 1 col 31 (at pos 30)

--Check if the foreign key still exists

SELECT TOP 1 * FROM REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_A_B';

--Yes it is still there

--DEV_DKH98X8NRPP3883BMMRIRSW8N;B;BREF1;1;FK_A_B;DEV_DKH98X8NRPP3883BMMRIRSW8N;A;ACOL1;PK_A;RESTRICT;RESTRICT

--Clean up (will delete damaged constraint)

DROP TABLE B;

DROP TABLE A;

So, the questions are:

1) Why changes to colums of foreign key references are permitted on column talbes? I think this is a bug, because the problem will not reproduce on row tables.

2) Is there any other way to get rid of damaged constraints instead of dropping the table?

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

Hmm... technically I don't see why setting a NOT NULL constraint to a foreign key should be considered "breaking the foreign key reference".

You're not touching the data here, but only the constraint definition.

About the error message with the DROP CONTRAINT command: looks like a bug to me as well.

I recommend to have a support incident opened for that.

- Lars

Answers (0)