Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You can not add Tables inside tables, what you do is add a foreign key reference, which is basically the ID (or whatever the PRIMARY KEY is) of the other table and make the other table separately.</p> <pre><code>CREATE TABLE Node ( Id INT( 3 ), Position VARCHAR( 12 ), FoodTax INT( 2 ), HasTreasureMap TINYINT( 1 ), CurrentPlayer INT( 3 ), PRIMARY KEY( Id ), INDEX ( CurrentPlayer ) ); CREATE TABLE Player ( Id INT( 3 ), Name VARCHAR( 64 ), PRIMARY KEY ( Id ) ); </code></pre> <p>Note that <code>INDEX( CurrentPlayer )</code> in the first table is not necessary unless you are going to join the tables, or use the <code>CurrentPlayer</code> field to search or sort on.</p> <p>If you are using the <strong>InnoDB</strong> database engine, then you can directly specify the nature of the relationship between the tables within the definition for the <code>Node</code> table as follows.</p> <pre><code>CREATE TABLE Node ( Id INT( 3 ), Position VARCHAR( 12 ), FoodTax INT( 2 ), HasTreasureMap TINYINT( 1 ), CurrentPlayer INT( 3 ), PRIMARY KEY( Id ), INDEX ( CurrentPlayer ), FOREIGN KEY ( CurrentPlayer ) REFERENCES Player( Id ) ON UPDATE CASCADE ON DELETE SET NULL ); </code></pre> <p>The <code>FOREIGN KEY</code> addition causes a few effects, it adds a check in so that you can not set a value for <code>CurrentPlayer</code> in the <code>Node</code> table that does not <em>already</em> exist in the <code>Player</code> table, any <code>INSERT/UPDATE</code> attempting this would fail. Also, the two options <code>ON UPDATE</code> and <code>ON DELETE</code> tell the database that if a record from <code>Player</code> is updated or deleted, then perform an action automatically via a <em>trigger</em> on the <code>Node</code> table. <code>ON UPDATE CASCADE</code> means that if the <code>Id</code> of a <code>Player</code> record was updated to something else, it would <em>cascade</em> in to the <code>Node</code> table, changing that value too. <code>ON DELETE SET NULL</code> states that if a record is deleted from the <code>Player</code> table, then corresponding <code>CurrentPlayer</code> values in <code>Node</code> should be set to <code>NULL</code>.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to create a table with a column which is another table?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USOrbling
    1. USOrbling
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to create a table with a column which is another table?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHi Orbling, thanks for your reply. I have 400 nodes but I have only 4 players. So does it make sense to add Player as a foreign key? This is a game map, I want to check whether there is a player in a current node. I'm thinking to keep it in my java code, but just want to give it a try with mysql.
      singulars
      1. PO
      1. USChan
    2. COYou need a basic course on SQL and DB design. Some practical info can be found here: http://www.kawoolutions.com/SQL_Database_Design
      singulars
      1. PO
      1. USKawu
    3. CO@Chan If you only have 4 players, then whether or not you need a `Player` table largely depends on whether or not you need to store changeable information for those players. If not, just store the player ID and forget the secondary table. If you have the table anyway, then yes, keep the key.
      singulars
      1. PO
      1. USOrbling
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload