cancel
Showing results for 
Search instead for 
Did you mean: 

Alter Table Error

Former Member
0 Kudos

When I try to run the following alter command

ALTER TABLE ITEM_LOCATION ADD CONSTRAINT IL_LOC_TO_LOCATION_FK FOREIGN KEY (LOCATION_KEY) REFERENCES LOCATION (LOCATION_KEY) ;

I get this error?

Cause: com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [7]: feature not supported: more than 32 triggers for single table is not yet supported: line 1 col 78 (at pos 77)

Can someone help me?

Thanks,

Geetha

Accepted Solutions (0)

Answers (2)

Answers (2)

rama_shankar3
Active Contributor
0 Kudos

did you try, making a backup of these tables data and try? If not, try that and re-inser the data. Make sure sufficient backup of data is made before taking this approach!

Good luck!

lbreddemann
Active Contributor
0 Kudos

Hi Geetha,

sounds as if there are already a lot of triggers and constraints defined on the table.

Would you mind to check this via these statements?

select count(*) as contraints_cnt from "PUBLIC"."REFERENTIAL_CONSTRAINTS" where table_name ='<table_name>';

and

select count(*) as trigger_cnt  from "PUBLIC"."TRIGGERS"  where subject_table_name ='<table_name>';

Cheers,

Lars

Former Member
0 Kudos

Hi Lars,

I work with Geetha, and am currently looking at this issue. Here are the results I get from the queries you suggested:

select count(*) as contraints_cnt from "PUBLIC"."REFERENTIAL_CONSTRAINTS" where table_name ='ITEM_LOCATION';

CONSTRAINTS_CNT = 98

select count(*) as trigger_cnt  from "PUBLIC"."TRIGGERS"  where subject_table_name ='ITEM_LOCATION';

TRIGGER_CNT = 196

However, this doesn't seem like quite the right query. Each user gets their own sandbox/schema to develop/test against. So, if I do

select count(*) as contraints_cnt from "PUBLIC"."REFERENTIAL_CONSTRAINTS" where SCHEMA_NAME = '<schema>' and  table_name ='ITEM_LOCATION';

I get a CONSTRAINTS_CNT count of only 14, or, 1/7th because we have 7 developers using the HANA instance thus far. 14 constraints is well below the 32 triggers.

select count(*) as trigger_cnt  from "PUBLIC"."TRIGGERS"  where SCHEMA_NAME = '<schema>' and  subject_table_name ='ITEM_LOCATION';

For this query, I get TRIGGER_CNT=28, which again is below 32 triggers.

Do you have any more insight into what may be the issue?

lbreddemann
Active Contributor
0 Kudos

Hi Kevin,

right - I forgot to include the schema_name where it should have been.

So, good thinking yourself there!

As far as I can see, currently the ref. constraint implementation will create at least two triggers for each ref. constraint. So, that would count up to 30 in your example.

That is, if you looked at the right table ...

- Lars

Former Member
0 Kudos

Lars,

So, if I am currently at 28 triggers and I am altering the table to create one more foreign key constraint, shouldn't that bring my total to 30, not 32? Or, when you said that at least 2 triggers are created by a referential constraints, does that mean there may be cases where more than 2 triggers are created?

-- Kevin

lbreddemann
Active Contributor
0 Kudos

Kevin,

your math is right (just as I wrote it).

I haven't tried out all variants of ref. contraints yet - so I can't answer that.

All in all, right now, in it's current implementation state in SAP HANA, it's likely not too satisfactory to make heavy use ref. constraints. Let's face it: the current implementation of constraints and also triggers have been done to suit some very specific, rather narrow use cases.

There's definitively quite some room for further development and feature completeness (or at least "functional enough" completeness).

Besides that: 14 or more referential constraints on a single table really smells like a "fact table".

If this is the case, then it might not be the best choice to go for ref. constraints in the first place.

... just sayin' ...

- Lars