These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Invalid data type in foreign key

First post
Author
Previous Topic Next Topic
Freyers
ICE is Coming to EVE
Goonswarm Federation
#1 - 2015-07-28 16:08:47 UTC  |  Edited by: Freyers
Hi folks!

I've downloaded the latest SDE kit from https://developers.eveonline.com/resource/static-data-export and wanted to create the database with Microsoft SQL Server Management Studio 2012.

The thing is that the creation of the database is okay, but when I put the constraint (foreign key and so on) I have several errors like:
Quote:
Msg 1778, Level 16, State 0, Line 7
Column 'invNames.itemID' is not the same data type as referencing column 'agtAgents.agentID' in foreign key 'agtAgents_FK_agent'.


And well, it looks legit: integer is not a big integer
CREATE TABLE dbo.agtAgents
(
  agentID        int,
  divisionID     tinyint,
  corporationID  int,
  locationID     int,
  [level]        tinyint,
  quality        smallint,
  agentTypeID    int,
  isLocator      bit,

  CONSTRAINT agtAgents_PK PRIMARY KEY CLUSTERED (agentID)
)

CREATE TABLE dbo.invNames
(
    itemID     bigint         NOT NULL,
    itemName   nvarchar(200)  NOT NULL,
    
    CONSTRAINT invNames_PK PRIMARY KEY CLUSTERED (itemID)
)



Is there somewhere a good export of the database, or should I change manually those types...?
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#2 - 2015-07-28 22:39:42 UTC
Ignore the constraints.

CCP does. (when you have data which is purely for lookup, they add no benefit)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
SJ Astralana
Syncore
#3 - 2015-07-28 22:54:01 UTC
You're better off simply restoring their backup anyway.

Hyperdrive your production business: Eve Production Manager

Freyers
ICE is Coming to EVE
Goonswarm Federation
#4 - 2015-07-29 07:23:08 UTC  |  Edited by: Freyers
Well, I'm try to add the constraint in order to migrate the MS SQL Server to MySQL and keep the SQL Server's constraints in the MySQL with InnoDB.
Some tool such as MySQL Workbench keeps the constraint and translate them for MySQL which is interesting to make sure that the relational integrity is OK but not as mandatory as I would liked to.

Restoring their .bak file gives the same result: no constraint and different types.

Thanks for you replies, I will drop off these constraints.
Desmont McCallock
#5 - 2015-07-29 07:47:30 UTC
Then why don't you use Steve's MySQL conversions ?
Freyers
ICE is Coming to EVE
Goonswarm Federation
#6 - 2015-07-29 07:54:19 UTC  |  Edited by: Freyers
Will do, just wanted to know if I could keep those constraints easily.
Looks it won't and won't be worth the time.

Was just curious about "how to do this" at least one time :).
Golden Gnu
Lobach Inc.
#7 - 2015-07-29 09:40:23 UTC
I used to use MSSQL with CCPs SDE when creating my data for jEveAssets, but, setting it up is a huge hassle, compared with XAMPP and Fuzzwork™ MySQL exports. As an added bonus Steve's SDE thread usually contain post for any abnormalities in the SDE, detected by other 3rd party devs.

tl:dr
Steve's SDE is win:win

Creator of jEveAssets - the asset manager

"Download is the meaning of life, upload is the meaning of intelligent life"
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#8 - 2015-07-29 12:24:37 UTC
Has one other benefit: I take care of loading all the other bits in.

I mostly get it right Blink

Can be worth checking things, when something new has changed (like typeids.yaml this time)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter