public function buildColumnSql(SchemaInterface $schema, DeclareColumn $column)
{
$name = $column->name;
$isa = $column->isa ?: 'str';
if (!$column->type && $isa == 'str') {
$column->type = 'text';
}
$args = new ArgumentArray();
$sql = $column->buildDefinitionSql($this->driver, $args);
/**
BUILD COLUMN REFERENCE
track(
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
artist_id INTEGER REFERENCES artist
)
MySQL Syntax:
reference_definition:
REFERENCES tbl_name (index_col_name,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
A reference example:
PRIMARY KEY (`idEmployee`) ,
CONSTRAINT `fkEmployee_Addresses`
FOREIGN KEY `fkEmployee_Addresses` (`idAddresses`)
REFERENCES `schema`.`Addresses` (`idAddresses`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
*/
foreach ($schema->relations as $rel) {
switch ($rel['type']) {
case Relationship::BELONGS_TO:
case Relationship::HAS_MANY:
case Relationship::HAS_ONE:
if ($name != 'id' && $rel['self_column'] == $name) {
$fSchema = new $rel['foreign_schema']();
$fColumn = $rel['foreign_column'];
$fc = $fSchema->columns[$fColumn];
$sql .= ' REFERENCES ' . $fSchema->getTable() . '(' . $fColumn . ')';
}
break;
}
}
return $sql;
}
public function buildColumnSql(SchemaInterface $schema, DeclareColumn $column)
{
$name = $column->name;
$isa = $column->isa ?: 'str';
if (!$column->type && $isa == 'str') {
$column->type = 'text';
}
$args = new ArgumentArray();
$sql = $column->buildDefinitionSql($this->driver, $args);
return $sql;
}
public function buildColumnSql(SchemaInterface $schema, DeclareColumn $column)
{
$name = $column->name;
$isa = $column->isa ?: 'str';
$type = $column->type;
if (!$type && $isa == 'str') {
$type = 'text';
}
// Note that sqlite doesn't support unsigned integer primary key column
if ($column->autoIncrement) {
$column->unsigned = false;
}
$args = new ArgumentArray();
$sql = $column->buildDefinitionSql($this->driver, $args);
/*
* build sqlite reference
* create table track(
* trackartist INTEGER,
* FOREIGN KEY(trackartist) REFERENCES artist(artistid)
* )
* @see http://www.sqlite.org/foreignkeys.html
*
* CREATE TABLE album(
* albumartist TEXT,
* albumname TEXT,
* albumcover BINARY,
* PRIMARY KEY(albumartist, albumname)
* );
*
* CREATE TABLE song(
* songid INTEGER,
* songartist TEXT,
* songalbum TEXT,
* songname TEXT,
* FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)
* );
*/
foreach ($schema->relations as $rel) {
switch ($rel['type']) {
case Relationship::BELONGS_TO:
case Relationship::HAS_MANY:
case Relationship::HAS_ONE:
if ($name != 'id' && $rel['self_column'] == $name) {
$fSchema = new $rel['foreign_schema']();
$fColumn = $rel['foreign_column'];
$sql .= ' REFERENCES ' . $fSchema->getTable() . '(' . $fColumn . ')';
}
break;
}
}
return $sql;
}
public function buildColumnSql(SchemaInterface $schema, DeclareColumn $column)
{
$name = $column->name;
$isa = $column->isa ?: 'str';
if (!$column->type && $isa == 'str') {
$column->type = 'text';
}
// Note that pgsql doesn't support unsigned integer primary key column
if ($column->autoIncrement) {
$column->unsigned = false;
}
$args = new ArgumentArray();
$sql = $column->buildDefinitionSql($this->driver, $args);
return $sql;
}
/**
* Override buildColumnSql to support inline reference.
*
* MySQL Syntax:
*
* reference_definition:
* REFERENCES tbl_name (index_col_name,...)
* [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
* [ON DELETE reference_option]
* [ON UPDATE reference_option]
* reference_option:
* RESTRICT | CASCADE | SET NULL | NO ACTION
* A reference example:
*
* PRIMARY KEY (`idEmployee`) ,
* CONSTRAINT `fkEmployee_Addresses`
* FOREIGN KEY `fkEmployee_Addresses` (`idAddresses`)
* REFERENCES `schema`.`Addresses` (`idAddresses`)
* ON DELETE NO ACTION
* ON UPDATE NO ACTION
*
* FOREIGN KEY (`order_uuid`) REFERENCES orders(`uuid`)
*/
public function buildColumnSql(SchemaInterface $schema, DeclareColumn $column)
{
$name = $column->name;
$isa = $column->isa ?: 'str';
if (!$column->type && $isa == 'str') {
$column->type = 'text';
}
$args = new ArgumentArray();
$sql = $column->buildDefinitionSql($this->driver, $args);
/*
BUILD COLUMN REFERENCE
track(
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
artist_id INTEGER REFERENCES artist
)
And here is the important part:
Furthermore, MySQL parses but ignores “inline REFERENCES
specifications” (as defined in the SQL standard) where the references
are defined as part of the column specification.
MySQL accepts REFERENCES clauses only when specified as part of a
separate FOREIGN KEY specification. For storage engines that do not
support foreign keys (such as MyISAM), MySQL Server parses and ignores
foreign key specifications.
A column with foreign key should not be nullable.
@see http://stackoverflow.com/questions/10028214/add-foreign-key-to-existing-table
*/
foreach ($schema->relations as $rel) {
switch ($rel['type']) {
case Relationship::BELONGS_TO:
if ($name != 'id' && $rel['self_column'] == $name) {
$fSchema = new $rel['foreign_schema']();
$fColumn = $rel['foreign_column'];
}
break;
}
}
return $sql;
}