I do not see any limitation on https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html.

So just use **otherdb.othertable** and you will be good.

It&#39;s possible :  [Link to do it][1]


  [1]: http://forums.mysql.com/read.php?22,150829,200251#msg-200251

Example (Table1 is in database1 and HelloTable is in database2) :

    ALTER TABLE Table1 
    ADD foreign key FK_table1(ColumnNameFromTable1)
    REFERENCES db2.HelloTable(ColumnNameFromHelloTable)

Below is how to add a foreign key on table t2, reference from table db1.historial(codh):

    alter table t2
    add foreign key FK_t2(micod2)
        references db1.historial(codh)
        on delete cascade
        on update cascade;

    ALTER TABLE `tablename1`
    ADD CONSTRAINT `tablename1_student_id_foreign` 
    FOREIGN KEY (`tablename1`.`id`) 
    REFERENCES `db2`.`tablename2`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE;

if we have table calling answers in db1 and student in db2 calling ramiyusu_offline

we must type as below
    
    ALTER TABLE `answers`
    ADD CONSTRAINT `answers_student_id_foreign` 
    FOREIGN KEY (`id`)
    REFERENCES `ramiyusu_offline`.`student`(`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE;

I have customer files uploaded to Amazon S3, and I would like to add a feature to count the size of those files for each customer. Is there a way to &quot;peek&quot; into the file size without downloading them? I know you can view from the Amazon control panel but I need to do it pro grammatically.

 

Check file size on S3 without downloading?

I am currently designing the API for an existing PHP application, and to this end am investigating REST as a sensible architectural approach.

I believe I have a reasonable grasp of the key concepts, but I&#39;m struggling to find anybody that has tackled object hierarchies and REST.

Here&#39;s the problem...

In the [application] business object hierarchy we have:

	Users 
	 L which have one-to-many Channel objects
	 L which have one-to-many Member objects
	 
In the application itself we use a lazy load approach to populate the User object with arrays of these objects as required.  I believe in OO terms this is object aggregation, but I have seen various naming inconsistencies and do not care to start a war about the precise naming convention &lt;/flame war&gt;.  

For now, consider I have some loosely coupled objects that I may / may not populate depending on application need.

From a REST perspective, I am trying to ascertain what the approach should be.  Here is my current thinking (considering GET only for the time being):

**Option 1 - fully populate the objects:**

	GET api.example.com/user/{user_id}

Read the User object (resource) and return the User object with all possible Channel and Member objects pre-loaded and encoded (JSON or XML).
	
PROS: reduces the number of objects, no traversal of object hierarchies required  
CONS: objects must be fully populated (expensive)

**Option 2 - populate the primary object and include links to the other object resources:**

	GET api.example.com/user/{user_id}

Read the User object (resource) and return the User object User data populated and two lists.
	
Each list references the appropriate (sub) resource i.e.
	
	api.example.com/channel/{channel_id}
	api.example.com/member/{member_id}
		
I think this is close to (or exactly) the implications of hypermedia - the client can get the other resources if it wants (as long as I tag them sensibly).
	
PROS: client can choose to load the subordinates or otherwise, better separation of the objects as REST resources  
CONS: further trip required to get the secondary resources
	
**Option 3 - enable recursive retrieves**

	GET api.example.com/user/{user_id}
	
Read the User object and include links to lists of the sub-objects i.e.
	
	api.example.com/user/{user_id}/channels
	api.example.com/user/{user_id}/members
	
the /channels call would return a list of channel resources in the form (as above):
	
	api.example.com/channel/{channel_id}
		
PROS: primary resources expose where to go to get the subordinates but not what they are (more RESTful?), no requirement to get the subordinates up front, the subordinate list generators (/channels and /members) provide interfaces (method like) making the response more service like.  
CONS: three calls now required to fully populate the object
	
**Option 4 - (re)consider the object design for REST**
		
I am re-using the [existing] application object hierarchy and trying to apply it to REST - or perhaps more directly, provide an API interface to it.
	
Perhaps the REST object hierarchy should be different, or perhaps the new RESTful thinking is exposing limitations of the existing object design.

Any thoughts on the above welcomed.

How should I deal with object hierarchies in a RESTful API?

I would like to know if it&#39;s possible in InnoDB in `MySQL` to have a `table` with **foreign key** that references another table in a different `database` ? 

And if so, how this can be done ?

MySQL InnoDB foreign key between different databases

I would like to know if it's possible in InnoDB in <code>MySQL</code> to have a <code>table</code> with foreign key that references another table in a different <code>database</code> ?
And if so, how this can be done ?

I&#39;m trying to convert from a SQL Server database backup file (`.bak`) to MySQL. [This question][1] and answers have been very useful, and I have successfully imported the database, but am now stuck on exporting to MySQL.

The *MySQL Migration Toolkit* was suggested, but seems to have been replaced by the *MySQL Workbench*. Is it possible to use the MySQL Workbench to migrate from SQL Server in the same way that the migration tool worked?

Or is the Migration Toolkit still available somewhere?

  [1]: https://stackoverflow.com/questions/156279/how-to-import-a-sql-server-bak-file-into-mysql

How to export SQL Server database to MySQL?

Is it possible to clean a mysql innodb storage engine so it is not storing data from deleted tables?

Or do I have to rebuild a fresh database every time?

Howto: Clean a mysql InnoDB storage engine?

Does anyone know why latin1_swedish is the default for MySQL. It would seem to me that UTF-8 would be more compatible right? 

Defaults are usually chosen because they are the best universal choice, but in this case it does not seem thats what they did.

Why does MySQL use latin1_swedish_ci as the default?

Can anyone tell me if a MySQL `SELECT` query is case sensitive or case insensitive by default? And if not, what query would I have to send so that I can do something like:

    SELECT * FROM `table` WHERE `Value` = &quot;iaresavage&quot;

Where in actuality, the real value of `Value` is `IAreSavage`.


MySQL: is a SELECT statement case sensitive?

I have the following table structure

    +  id  +  word  +
    +------+--------+

The table gets filled with the words in lower cas of a given text, so the text

Hello bye hello

would result in

    +  id  +  word  +
    +------+--------+
    +   1  + hello  +
    +------+--------+
    +   2  + bye    +
    +------+--------+
    +   3  + hello  +
    +------+--------+

I want to make a SELECT query that will return the number of words that get repeated at least two times in the table (like hello)

    SELECT COUNT(id) FROM words WHERE (SELECT COUNT(words.word))&gt;1

which of course is so wrong and super overloading when table is big. Any idea on how to achieve such purpose? In the given example inhere-above, I would expect 1

MySQL count occurrences greater than 2

I&#39;m trying to evaluate content repositories ([JSR283][1]) like [Jackrabbit][2] and [ModeShape][3] but I must confess that I don&#39;t understand what problem resolves in first place and even if it is a good choice for the project. Which cases do you think is the best solution to apply? Is not the same thing as relational databases, except for the size? Why? Extra points for pointing real world examples.

Thanks in advance.


  [1]: http://www.jcp.org/en/jsr/detail?id=283
  [2]: http://jackrabbit.apache.org/
  [3]: http://www.jboss.org/modeshape

When to use JCR (content repository) over other options?

I have a one-to-many relation between Parent and Child table.  In the parent object I have a 

    List&lt;Child&gt; setChildren(List&lt;Child&gt; childs)

I also have a foreign key in the Child table. This foreign key is an ID that references a Parent row in database.  So in my database configuration this foreign key can not be NULL.
Also this foreign key is the primary key in the Parent table.

So my question is how I can automatically save the children objects by doing something like this:

    session.save(parent);

I tried the above but I&#39;m getting a database error complaining that the foreign key field  in the Child table can not be NULL. Is there a way to tell JPA to automatically set this foreign key into the Child object so it can automatically save children objects?

Thanks in advance.

Save child objects automatically using JPA Hibernate

I was thinking of making a new, light-weight database population framework. I absolutely hate dbunit. Before I do, I want to know if someone already did it.

Things i dislike about dbunit:

1) The simplest format to write and get started is deprecated. They want you to use formats that are bloated. Some even require xml schemas. Yeah, whatever.

2) They populate rows not in the order you write them, but in the order tables are defined in the xml file. This is really bad because you can&#39;t order your data in such a way that foreign key constraints won&#39;t cause problems. This just forces you to go through the hassle of turning them off altogether. 

This also wastes time and bloats up your junit base classes to include code to disable the foreign key constraints. You will probably have to test for the database type (hsqldb, etc.) and disable them in database-specific ways. This is way bad.

It could be better if dbunit helped in disabling foreign key constraints as part of their framework automatically, but they don&#39;t do this. They do keep track of dialects... so why not use them for this? Ultimately, all of this does is force the programmer to waste time and not get up and testing quickly.

3) XML is a pain to write. I don&#39;t need to say more about this. They also offer so many ways to do it, that I think it just complicates matters. Just offer one really solid way and be done with it.

4) When your data gets large, keeping track of the ids and their consistent/correct relationships is a royal pain. 

Also, if you don&#39;t work on a project for a month, how are you to remember that user_id 1 was an admin, user_id 2 was a business user, user_id 3 was an engineer and user_id 4 was something else? Going back to check this is wasting more time. There should be a meaningful way to retrieve it other than an arbitrary number.

5) It&#39;s slow. I&#39;ve found that unless hsqldb is used, it is painfully slow. It doesn&#39;t have to be. There are also numerous ways to mess up its configuration as it is not easy to do &quot;out of the box&quot;. There is a hump that you must go through to get it working right. All this does is encourage people to not use it, or be pissed of when they do start to use it.

6) Some values tend to repeat a lot, likes dates. It&#39;d be nice to specify defaults, or even have the framework put defaults in automatically, even without you telling it to put defaults in there. That way you can create objects just with the values you want, and leave the rest off. This sure beats specifying every nook and cranny of a column if it&#39;s not required.

7) Probably the most annoying thing is that the first entry must include ALL the values - even null placeholders - or future rows won&#39;t pick the columns that you actually specified.

DBunit doesn&#39;t have a sensible default for translating [NULL] to a real null value either. You have to manually add it. Tell me, who hasn&#39;t done this with dbunit? Everyone has. It shouldn&#39;t be like this!

What this means is that if you have a polymorphic object, you must declare all the foreign keys to the joining tables of each subclass in the first row, even though they are null. If you do a table for all subclasses pattern, you still have to specify all the fields on the first row. This is just awful.

Anything out there to satisfy me, or should I become the next framework developer of a much better database testing framework?

Is there a dbunit-like framework that doesn&#39;t suck for java/scala?

How do I rename a column in table `xyz`? The columns are:

    Manufacurerid, name, status, AI, PK, int

I want to rename to `manufacturerid`

I tried using PHPMyAdmin panel, but I get this error:

    MySQL said: Documentation
    #1025 - Error on rename of &#39;.\shopping\#sql-c98_26&#39; to &#39;.\shopping\tblmanufacturer&#39; (errno: 150)

 





Error renaming a column in MySQL

Is there a rake command to wipe out the data in the database tables?

How do I create a db:seed script to pre-fill data to my tables?

Reset the database (purge all), then seed a database

I have tables that I&#39;ve tried setting PK FK relationships on but I want to verify this. How can I show the PK/FK restraints? I saw [this][1] manual page, but it does not show examples and my google search was fruitless also. My database is `credentialing1` and my constrained tables are `practices` and `cred_insurances`.


  [1]: http://dev.mysql.com/doc/refman/5.5/en/table-constraints-table.html

Show constraints on tables command

I&#39;m doing some maintenance work on a database application and I&#39;ve discovered that, joy of joys, even though values from one table are being used in the style of foreign keys, there&#39;s no foreign key constraints on the tables.

I&#39;m trying to add FK constraints on these columns, but I&#39;m finding that, because there&#39;s already a whole load of bad data in the tables from previous errors which have been naively corrected, I need to find the rows which don&#39;t match up to the other table and then delete them.

I&#39;ve found some examples of this kind of query on the web, but they all seem to provide examples rather than explanations, and I don&#39;t understand why they work.

Can someone explain to me how to construct a query which returns all the rows with no matches in another table, and what it&#39;s doing, so that I can make these queries myself, rather than coming running to SO for every table in this *mess* that has no FK constraints?

How to select rows with no matching entry in another table?

I have set up ssh key pairs between my desktop and two servers, and from the servers to my desktop, but after reinstalling the OS on my desktop, I can&#39;t re-establish the key-pair going into my desktop by this: 

```shell
mkdir ~/.ssh
chmod 700 ~/.ssh
ssh-keygen -t 
ssh-copy-id username@server
```

I get the following error:

(names in *italics* changed to protect the innocent My desktop is Ubuntu, and I can&#39;t find the answer [ here][1])

   

&gt;  @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
&gt;     @    WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!     @
&gt;     @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
&gt;     IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
&gt;     Someone could be eavesdropping on you right now (man-in-the-middle
&gt; attack)!
&gt;     It is also possible that the RSA host key has just been changed.
&gt;     The fingerprint for the RSA key sent by the remote host is
&gt;     *ab:cd:ef:gh*
&gt;     Please contact your system administrator.
&gt;     Add correct host key in /home/*user*/.ssh/known_hosts to get
&gt; rid of this message.
&gt;     Offending key in /home/*user*/.ssh/known_hosts:1
&gt;     RSA host key for *user.server* has changed and you have requested strict
&gt; checking.
&gt;     Host key verification failed.

  [1]: https://help.ubuntu.com/community/SSH/OpenSSH/Keys


How to establish ssh key pair when &quot;Host key verification failed&quot;

I have two tables in two different databases. In table1 (in database1) there is a column called column1 and it is a primary key. Now in table2 (in database2) there is a column called column2 and I want to add it as a foreign key.

I tried to add it and it gave me the following error:

&gt; Msg 1763, Level 16, State 0, Line 1  
&gt; Cross-database foreign key references are not supported. Foreign key Database2.table2.
&gt; 
&gt; Msg 1750, Level 16, State 0, Line 1  
&gt; Could not create constraint. See previous errors.

How do I do that since the tables are in different databases.

Add Foreign Key relationship between two Databases

I need to make some changes to a SQL Server 2008 database. 

This requires the creation of a new table, and inserting a foreign key in the new table that references the Primary key of an already existing table. So I want to set up a relationship between my new tblTwo, which references the primary key of tblOne. 

However when I tried to do this (through SQL Server Management Studio) I got the following error:

&gt; The columns in table &#39;tblOne&#39; do not
&gt; match an existing primary key or
&gt; UNIQUE constraint

I&#39;m not really sure what this means, and I was wondering if there was any way around it?

SQL Server 2008: The columns in table do not match an existing primary key or unique constraint

So I am trying to add a primary key to one of the tables in my database. Right now it has a primary key like this:

    PRIMARY KEY (user_id, round_number)

Where user_id is a foreign key.

I am trying to change it to this:

    PRIMARY KEY (user_id, round_number, created_at)

I am doing this in phpmyadmin by clicking on the primary key icon in the table structure view.

This is the error I get:

    #1025 - Error on rename of &#39;./database/#sql-2e0f_1254ba7&#39; to &#39;./database/table&#39; (errno: 150)

It is a MySQL database with InnoDB table engine.

#1025 - Error on rename of &#39;./database/#sql-2e0f_1254ba7&#39; to &#39;./database/table&#39; (errno: 150)

I looked around and found some of the MySQL engines are innodb and MyISAM. Perhaps there are few more. My question is what are these database engines?

What are the differences between different MySQL engines? And more importantly, How do I decide which one to use?

What are MySQL database engines?

I am using mysql and mysql workbench. I created 5 tables with innodb engine. I checked their engine and it was innodb before I insert data into them. I inserted data from 5 MyISAM tables and now my innodb tables are MyISAM. I can&#39;t change them. I used the alter table engine=innodb but it doesn&#39;t work.


Changing Table Engine in MySQL

In MySQL, there is no way to specify a storage engine for a certain database, only for single tables.  However, you can specify a storage engine to be used during one session with:

    SET storage_engine=InnoDB;

So you don&#39;t have to specify it for each table.

**How do I confirm, if indeed all the tables are using InnoDB?**

Content Type	Original Author	Original Content on Stackoverflow
Question	Alaa	View Question on Stackoverflow
Solution 1 - Mysql	BarsMonster	View Answer on Stackoverflow
Solution 2 - Mysql	Spilarix	View Answer on Stackoverflow
Solution 3 - Mysql	Salim	View Answer on Stackoverflow
Solution 4 - Mysql	Hosam Elzagh	View Answer on Stackoverflow

MySQL InnoDB foreign key between different databases

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

How should I deal with object hierarchies in a RESTful API?

Check file size on S3 without downloading?

Attributions