#10 Foreign Key check error - Ambiguous column name

[Anonymous]

Hi,

One of the foreign key check constraints returned an error as below:
java.lang.RuntimeException: Error in query: select UserId from Users referer
left join Contacts as referee on referer.ContactId = referee.ContactId
where referer.ContactId is not null
and referee.ContactId is null
at org.databene.jdbacl.QueryIterator.createSource(QueryIterator.java:52)
at org.databene.jdbacl.QueryIterator.(QueryIterator.java:41)
at org.databene.jdbacl.ArrayResultSetIterator.(ArrayResultSetIterator.java:39)
at org.databene.dbsanity.model.query.DefectQueryStrategy.perform(DefectQueryStrategy.java:57)
at org.databene.dbsanity.model.ForeignKeyStrategy.perform(ForeignKeyStrategy.java:77)
at org.databene.dbsanity.model.StrategyBasedCheck.perform(StrategyBasedCheck.java:68)
at org.databene.dbsanity.model.CheckHolderSupport.performCheck(CheckHolderSupport.java:149)
at org.databene.dbsanity.model.CheckHolderSupport.perform(CheckHolderSupport.java:81)
at org.databene.dbsanity.model.SuiteAndCheckHolder.perform(SuiteAndCheckHolder.java:63)
at org.databene.dbsanity.DbSanity.execute(DbSanity.java:351)
at org.databene.dbsanity.DbSanity.execute(DbSanity.java:310)
at org.databene.dbsanity.Main.main(Main.java:50)
Caused by: java.sql.SQLException: Ambiguous column name 'UserId'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1304)
at org.databene.jdbacl.proxy.LoggingStatementHandler.executeQuery(LoggingStatementHandler.java:161)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.databene.commons.BeanUtil.invoke(BeanUtil.java:687)
at org.databene.commons.BeanUtil.invoke(BeanUtil.java:646)
at org.databene.jdbacl.proxy.LoggingStatementHandler.invoke(LoggingStatementHandler.java:104)
at $Proxy1.executeQuery(Unknown Source)
at org.databene.jdbacl.QueryIterator.createSource(QueryIterator.java:49)
... 11 more

Original check configuration:
<check name="contact-user-fk" table="Users" defectType="referential_integrity"
description="Verify that relationship between Users and Contacts exists."
requirement="user.relation.01"
tags="schema,foreign_key,user,contact" >
<foreignKey refererColumns="ContactId" refereeTable="Contacts" refereeColumns="ContactId" />
</check>

NOTE on DB schema: The "Contacts" table does have a UserId column as well. "Users" table basically would contain only login details and any additional contact information for user would be stored in "Contacts" table. Contact does not have to be associated with user and it would then just act as contact information that is used all around in other tables. I'm guessing that reason behind adding UserId column to "Contacts" table might have been dictated by need to avoid going through "Users" table when wanting to retrieve contact details and UserId is known.

System information:
DB Sanity version 0.9.3
Java version: SE 7 u3
SQL Driver: JTDS v1.2.5 - net.sourceforge.jtds.jdbc.Driver

Best regards,
Ryandell

[Nobody/Anonymous]

Whats up i am kavin, its my first occasion to commenting anywhere, when i read this paragraph i thought i could also create comment due to this brilliant piece of writing.
<a href="http://www.quibblo.com/user/laura1event" title="printed">printed</a>