cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Kienenberger <mkien...@gmail.com>
Subject Re: Cayenne Tutorial: INSERT on table 'PAINTING' caused a violation of foreign key constraint
Date Tue, 26 Oct 2010 21:01:04 GMT
Can you open an issue on this so we don't forget about it and can
correct the problem?  Thanks!

https://issues.apache.org/jira/secure/CreateIssue!default.jspa

project Cayenne

On Tue, Oct 26, 2010 at 3:27 PM, caden whitaker
<caden.whitaker@gmail.com> wrote:
> Hah! Okay yeah now it makes sense, I had to think about it from a database
> perspective. First I made my ID names more descriptive and then I could
> totally see the problem.
>
> Here's where I got mixed up in the tutorials It shows in the picture that it
> is mapping to an ArtistID column, but it never tells you to make the
> ArtistID column in the PAINTING table. Well duh, yeah it needs it. So
> initially my tables looked like this
>
> CREATE TABLE ARTIST (ArtistID BIGINT NOT NULL, Name VARCHAR (255), PRIMARY
> KEY (ArtistID))
> CREATE TABLE PAINTING (PaintingID BIGINT NOT NULL,  Name VARCHAR (255),
> PRIMARY KEY (PaintingID))
> ALTER TABLE PAINTING ADD FOREIGN KEY (PaintingID) REFERENCES ARTIST
> (ArtistID)
>
> I was assuming those relationships between the tables were built behind the
> scenes or something. And now it makes sense why the FK is bombing. What it
> needed was an ArtistID column in the PAINTING table, like this:
>
> CREATE TABLE ARTIST (ArtistID BIGINT NOT NULL, Name VARCHAR (255), PRIMARY
> KEY (ArtistID))
> CREATE TABLE PAINTING ( PaintingID BIGINT NOT NULL, ArtistID BIGINT, Name
> VARCHAR (255), PRIMARY KEY (PaintingID))
> ALTER TABLE PAINTING ADD FOREIGN KEY (ArtistID) REFERENCES ARTIST (ArtistID)
>
> And then I needed to make the FK reference from Artist.ArtistID to
> Painting.ArtistID not Artist.ArtistID to Painting.PaintingID (duuuuh). Now
> it makes complete sense and it works.
>
> Even though I should have picked that up just from a pure database
> perspective I think it would benefit the tutorial docs to point that out
> that an ARTIST_ID column must be created on the PAINTING table. Unless I'm
> missing something.
>
> In either case, it works now, thank you all for your help!!
>
> On Tue, Oct 26, 2010 at 1:47 PM, Borut Bolčina <borut.bolcina@gmail.com>wrote:
>
>> I see ALTER TABLE PAINTING ADD FOREIGN KEY (ID) REFERENCES ARTIST (ID). Do
>> you have artist_id attribute (column) which is artist FK in PAINTING table?
>> It seems you have id of PAINTING which is PK also as FK. Please check that.
>>
>> Cheers,
>> Borut
>>
>> 2010/10/26 caden whitaker <caden.whitaker@gmail.com>
>>
>> > Hey Mike,
>> >
>> > I was thinking the same thing, so I removed the code, now it looks like
>> > this:
>> >
>> >            ObjectContext context = DataContext.createDataContext();
>> >            Artist picasso = context.newObject(Artist.class);
>> >            picasso.setName("Pablo Picasso");
>> >
>> >
>> >            Painting girl = context.newObject(Painting.class);
>> >            girl.setName("Girl Reading at a Table");
>> >            girl.setArtist(picasso);
>> >
>> >            Painting stein = context.newObject(Painting.class);
>> >            stein.setName("Gertrude Stein");
>> >            stein.setArtist(picasso);
>> >
>> >
>> >             context.commitChanges();
>> >
>> > And I get the same error:
>> >
>> > Begin Test
>> > Oct 26, 2010 12:26:30 PM org.apache.cayenne.conf.RuntimeLoadDelegate
>> > startedLoading
>> > INFO: started configuration loading.
>> > Oct 26, 2010 12:26:30 PM org.apache.cayenne.conf.RuntimeLoadDelegate
>> > shouldLoadDataDomain
>> > INFO: loaded domain: HelloWorld1
>> > Oct 26, 2010 12:26:30 PM org.apache.cayenne.conf.RuntimeLoadDelegate
>> > loadDataMap
>> > INFO: loaded <map name='HelloWorld1Map'
>> location='HelloWorld1Map.map.xml'>.
>> > Oct 26, 2010 12:26:30 PM org.apache.cayenne.conf.RuntimeLoadDelegate
>> > shouldLoadDataNode
>> > INFO: loading <node name='HelloWorld1Node'
>> > datasource='HelloWorld1Node.driver.xml'
>> > factory='org.apache.cayenne.conf.DriverDataSourceFactory'
>> >
>> >
>> schema-update-strategy='org.apache.cayenne.access.dbsync.CreateIfNoSchemaStrategy'>.
>> > Oct 26, 2010 12:26:30 PM org.apache.cayenne.conf.RuntimeLoadDelegate
>> > shouldLoadDataNode
>> > INFO: using factory: org.apache.cayenne.conf.DriverDataSourceFactory
>> > Oct 26, 2010 12:26:30 PM org.apache.cayenne.conf.DriverDataSourceFactory
>> > load
>> > INFO: loading driver information from 'HelloWorld1Node.driver.xml'.
>> > Oct 26, 2010 12:26:30 PM
>> > org.apache.cayenne.conf.DriverDataSourceFactory$DriverHandler init
>> > INFO: loading driver org.apache.derby.jdbc.EmbeddedDriver
>> > Oct 26, 2010 12:26:30 PM
>> > org.apache.cayenne.conf.DriverDataSourceFactory$LoginHandler init
>> > INFO: loading user name and password.
>> > Oct 26, 2010 12:26:30 PM org.apache.cayenne.access.QueryLogger
>> > logPoolCreated
>> > INFO: Created connection pool: jdbc:derby:memory:testdb;create=true
>> >    Driver class: org.apache.derby.jdbc.EmbeddedDriver
>> >    Min. connections in the pool: 1
>> >    Max. connections in the pool: 1
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.conf.RuntimeLoadDelegate
>> > shouldLoadDataNode
>> > INFO: loaded datasource.
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.conf.RuntimeLoadDelegate
>> > initAdapter
>> > INFO: no adapter set, using automatic adapter.
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.conf.RuntimeLoadDelegate
>> > shouldLinkDataMap
>> > INFO: loaded map-ref: HelloWorld1Map.
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.conf.RuntimeLoadDelegate
>> > finishedLoading
>> > INFO: finished configuration loading in 312 ms.
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logConnect
>> > INFO: Opening connection: jdbc:derby:memory:testdb;create=true
>> >    Login: null
>> >    Password: *******
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger
>> > logConnectSuccess
>> > INFO: +++ Connecting: SUCCESS.
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger
>> > logBeginTransaction
>> > INFO: --- transaction started.
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger log
>> > INFO: Detected and installed adapter:
>> > org.apache.cayenne.dba.derby.DerbyAdapter
>> > Oct 26, 2010 12:26:31 PM
>> > org.apache.cayenne.access.dbsync.CreateIfNoSchemaStrategy
>> > processSchemaUpdate
>> > INFO: No schema detected, will create mapped tables
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery
>> > INFO: CREATE TABLE ARTIST (ID BIGINT NOT NULL, Name VARCHAR (255),
>> PRIMARY
>> > KEY (ID))
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery
>> > INFO: CREATE TABLE PAINTING (ID BIGINT NOT NULL, Name VARCHAR (255),
>> > PRIMARY
>> > KEY (ID))
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery
>> > INFO: ALTER TABLE PAINTING ADD FOREIGN KEY (ID) REFERENCES ARTIST (ID)
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery
>> > INFO: CREATE TABLE AUTO_PK_SUPPORT (  TABLE_NAME CHAR(100) NOT NULL,
>> > NEXT_ID BIGINT NOT NULL,  PRIMARY KEY(TABLE_NAME))
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery
>> > INFO: DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('ARTIST',
>> > 'PAINTING')
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery
>> > INFO: INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES ('ARTIST',
>> > 200)
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery
>> > INFO: INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES
>> ('PAINTING',
>> > 200)
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger log
>> > INFO: Detected and installed adapter:
>> > org.apache.cayenne.dba.derby.DerbyAdapter
>> > Oct 26, 2010 12:26:31 PM org.apache.cayenne.access.QueryLogger logQuery
>> > INFO: SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE TABLE_NAME = ? FOR UPDATE
>> > [bind: 1:'ARTIST']
>> > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger logQuery
>> > INFO: SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE TABLE_NAME = ? FOR UPDATE
>> > [bind: 1:'PAINTING']
>> > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger
>> > logQueryStart
>> > INFO: --- will run 2 queries.
>> > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger logQuery
>> > INFO: INSERT INTO ARTIST (ID, Name) VALUES (?, ?)
>> > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger
>> > logQueryParameters
>> > INFO: [batch bind: 1->ID:200, 2->Name:'Pablo Picasso']
>> > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger
>> > logUpdateCount
>> > INFO: === updated 1 row.
>> > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger logQuery
>> > INFO: INSERT INTO PAINTING (ID, Name) VALUES (?, ?)
>> > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger
>> > logQueryParameters
>> > INFO: [batch bind: 1->ID:200, 2->Name:'Gertrude Stein']
>> > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger
>> > logQueryParameters
>> > INFO: [batch bind: 1->ID:201, 2->Name:'Girl Reading at a Table']
>> > Oct 26, 2010 12:26:32 PM org.apache.cayenne.access.QueryLogger
>> > logQueryError
>> > INFO: *** error.
>> > java.sql.SQLIntegrityConstraintViolationException: INSERT on table
>> > 'PAINTING' caused a violation of foreign key constraint
>> > 'SQL101026122631940'
>> > for key (201).  The statement has been rolled back.
>> >    at
>> > org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
>> > Source)
>> >    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
>> > Source)
>> >
>> > On Tue, Oct 26, 2010 at 12:23 PM, Mike Kienenberger <mkienenb@gmail.com
>> > >wrote:
>> >
>> > > In fact, this could be the problem.
>> > >
>> > > By calling it twice, you will get two of each object in each
>> > relationship.
>> > > This might be causing your foreign key constraint error.
>> > >
>> > >
>> > > On Tue, Oct 26, 2010 at 1:02 PM, Borut Bolčina <
>> borut.bolcina@gmail.com>
>> > > wrote:
>> > > > Hi,
>> > > >
>> > > > how did you create your database (show us the create statements)?
>> Which
>> > > > database are you using? Foreign key constraints are optional, but
you
>> > > need
>> > > > them if you want to reverse engineer the database, so that
>> > relationships
>> > > in
>> > > > the modeler are created.
>> > > >
>> > > > Also, in your unit test, you are setting
>> > > >
>> > > >           picasso.addToPaintings(girl);
>> > > >           picasso.addToPaintings(stein);
>> > > >
>> > > > but this is not needed. Cayenne automatically sets the other side
of
>> > the
>> > > > relationship for you, unlike Hibernate.
>> > > >
>> > > > -Borut
>> > > >
>> > > > 2010/10/26 caden whitaker <caden.whitaker@gmail.com>
>> > > >
>> > > >> Hey all,
>> > > >>
>> > > >> Running through the tutorials, I know what that error means, but
I
>> > don't
>> > > >> think I've done anything wrong. Can someone take a quick look
at
>> this
>> > > >> xml/object set and tell me what I did wrong? Any help would be
>> greatly
>> > > >> appreciated.
>> > > >>
>> > > >> Mapping.xml
>> > > >> <?xml version="1.0" encoding="utf-8"?>
>> > > >> <data-map xmlns="http://cayenne.apache.org/schema/3.0/modelMap"
>> > > >>  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>> > > >>  xsi:schemaLocation="http://cayenne.apache.org/schema/3.0/modelMap
>> > > >> http://cayenne.apache.org/schema/3.0/modelMap.xsd"
>> > > >>  project-version="3.0.0.1">
>> > > >>    <property name="defaultPackage"
>> > > value="org.example.cayenne.persistent"/>
>> > > >>    <db-entity name="ARTIST">
>> > > >>        <db-attribute name="ID" type="BIGINT" isPrimaryKey="true"
>> > > >> isMandatory="true"/>
>> > > >>        <db-attribute name="Name" type="VARCHAR" length="255"/>
>> > > >>    </db-entity>
>> > > >>    <db-entity name="PAINTING">
>> > > >>        <db-attribute name="ID" type="BIGINT" isPrimaryKey="true"
>> > > >> isMandatory="true"/>
>> > > >>        <db-attribute name="Name" type="VARCHAR" length="255"/>
>> > > >>    </db-entity>
>> > > >>    <obj-entity name="Artist"
>> > > >> className="main.java.org.example.cayenne.persistent.Artist"
>> > > >> dbEntityName="ARTIST">
>> > > >>        <obj-attribute name="name" type="java.lang.String"
>> > > >> db-attribute-path="Name"/>
>> > > >>    </obj-entity>
>> > > >>    <obj-entity name="Painting"
>> > > >> className="main.java.org.example.cayenne.persistent.Painting"
>> > > >> dbEntityName="PAINTING">
>> > > >>        <obj-attribute name="name" type="java.lang.String"
>> > > >> db-attribute-path="Name"/>
>> > > >>    </obj-entity>
>> > > >>    <db-relationship name="paintings" source="ARTIST"
>> target="PAINTING"
>> > > >> toMany="true">
>> > > >>        <db-attribute-pair source="ID" target="ID"/>
>> > > >>    </db-relationship>
>> > > >>    <db-relationship name="artist" source="PAINTING" target="ARTIST"
>> > > >> toMany="false">
>> > > >>        <db-attribute-pair source="ID" target="ID"/>
>> > > >>    </db-relationship>
>> > > >>    <obj-relationship name="paintings" source="Artist"
>> > target="Painting"
>> > > >> deleteRule="Deny" db-relationship-path="paintings"/>
>> > > >>    <obj-relationship name="artist" source="Painting" target="Artist"
>> > > >> deleteRule="Deny" db-relationship-path="artist"/>
>> > > >> </data-map>
>> > > >>
>> > > >> _Artist.java
>> > > >> public abstract class _Artist extends CayenneDataObject {
>> > > >>
>> > > >>    public static final String NAME_PROPERTY = "name";
>> > > >>    public static final String PAINTINGS_PROPERTY = "paintings";
>> > > >>
>> > > >>    public static final String ID_PK_COLUMN = "ID";
>> > > >>
>> > > >>    public void setName(String name) {
>> > > >>        writeProperty("name", name);
>> > > >>    }
>> > > >>    public String getName() {
>> > > >>        return (String)readProperty("name");
>> > > >>    }
>> > > >>
>> > > >>    public void addToPaintings(Painting obj) {
>> > > >>        addToManyTarget("paintings", obj, true);
>> > > >>    }
>> > > >>    public void removeFromPaintings(Painting obj) {
>> > > >>        removeToManyTarget("paintings", obj, true);
>> > > >>    }
>> > > >>    @SuppressWarnings("unchecked")
>> > > >>    public List<Painting> getPaintings() {
>> > > >>        return (List<Painting>)readProperty("paintings");
>> > > >>    }
>> > > >>
>> > > >>
>> > > >> }
>> > > >>
>> > > >> _Painting.java
>> > > >> public abstract class _Painting extends CayenneDataObject {
>> > > >>
>> > > >>    public static final String NAME_PROPERTY = "name";
>> > > >>    public static final String ARTIST_PROPERTY = "artist";
>> > > >>
>> > > >>    public static final String ID_PK_COLUMN = "ID";
>> > > >>
>> > > >>    public void setName(String name) {
>> > > >>        writeProperty("name", name);
>> > > >>    }
>> > > >>    public String getName() {
>> > > >>        return (String)readProperty("name");
>> > > >>    }
>> > > >>
>> > > >>    public void setArtist(Artist artist) {
>> > > >>        setToOneTarget("artist", artist, true);
>> > > >>    }
>> > > >>
>> > > >>    public Artist getArtist() {
>> > > >>        return (Artist)readProperty("artist");
>> > > >>    }
>> > > >>
>> > > >>
>> > > >> }
>> > > >>
>> > > >>
>> > > >> JUnit test case:
>> > > >>    // JUnit
>> > > >>    public void testBuild()
>> > > >>        throws Exception
>> > > >>    {
>> > > >>        System.out.println("Begin Test");
>> > > >>        try {
>> > > >>            ObjectContext context = DataContext.createDataContext();
>> > > >>            Artist picasso = context.newObject(Artist.class);
>> > > >>            picasso.setName("Pablo Picasso");
>> > > >>
>> > > >>
>> > > >>            Painting girl = context.newObject(Painting.class);
>> > > >>            girl.setName("Girl Reading at a Table");
>> > > >>            girl.setArtist(picasso);
>> > > >>
>> > > >>            Painting stein = context.newObject(Painting.class);
>> > > >>            stein.setName("Gertrude Stein");
>> > > >>            stein.setArtist(picasso);
>> > > >>
>> > > >>            picasso.addToPaintings(girl);
>> > > >>            picasso.addToPaintings(stein);
>> > > >>
>> > > >>            context.commitChanges();
>> > > >>
>> > > >>        } catch (Exception e) {
>> > > >>            e.printStackTrace();
>> > > >>        }
>> > > >>        System.out.println("End Test");
>> > > >>    }
>> > > >>
>> > > >
>> > >
>> >
>>
>

Mime
View raw message