cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Borut Bolčina <borut.bolc...@gmail.com>
Subject Re: Cayenne Tutorial: INSERT on table 'PAINTING' caused a violation of foreign key constraint
Date Tue, 26 Oct 2010 19:58:55 GMT
I am glad it helped, I think I tripped on this one years ago also :-)

-Borut

2010/10/26 caden whitaker <caden.whitaker@gmail.com>

> 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
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message