Per our Schema API documentation, each column of a unique key of a table schema can be specified either as a column name or as an array of column name and length:
* - 'unique keys': An associative array of unique keys ('keyname' =>
* specification). Each specification is an array of one or more
* key column specifiers (see below) that form a unique key on the table.
* A key column specifier is either a string naming a column or an
* array of two elements, column name and length, specifying a prefix
* of the named column.
The PostreSQL database backend does not support the latter case. The relevant code is in lines 135 to 139 of \Drupal\Core\Database\Driver\pqsql\Schema::createTableSql():
if (isset($table['unique keys']) && is_array($table['unique keys'])) {
foreach ($table['unique keys'] as $key_name => $key) {
$sql_keys[] = 'CONSTRAINT ' . $this->prefixNonTable($name, $key_name, 'key') . ' UNIQUE (' . implode(', ', $key) . ')';
}
}
The $key variable in the foreach can be (per the API documentation) for example array(array('foo', 123)) in which case the implode() fails. The resulting string will then be Array and the resulting SQL will fail hard.
Comments
Comment #1
tstoecklerComment #2
berdirWell, a unique key constraint on a partial column is IMHO useless because it would then fail on longer, non-identical strings that are the same for the first N characters? That makes no sense :) Same for a primary key.
Instead of trying to fix this, which PostgreSQL might not even support, we should fix the fact that we are generating a partial unique key constraint and instead limit the column size of file.uri?
Comment #3
bzrudi71 commentedShould be mentioned that this breaks PostgreSQL installation...
Comment #4
tstoecklerSo I found this gem in the MySQL documentation:
I searched for something similar in PostreSQL and couldn't find anything, but for some notes of "exclusion constraints", but I didn't really grok what those were. It might be that they're completely unrelated - it might be that you're able to achieve a similar behavior with them in PostgreSQL. It doesn't seem like that would be trivial to implement, though.
I have no idea whatsoever if this is ANSI SQL (or if the ANSI standard is even relevant to unique keys).
In any case: This is incredibly strange behavior and is something I do not think we want to support. If I want a field to be unique, I want it to be unique, not only the first part. If - for whichever reason - I do want this behavior I personally think it's fine to have the validation only in PHP (where I need to have it anyway) and not be able to reap the benefit of a faster SQL query by using a unique key prefix which may or may not be supported by all backends anyway.
So to my mind, we should remove that part of the API wholesale, as I think it is confusing and pointless to begin with. Leaving the title for now, though, as to get more consensus on that.
To solve the actual problem with the PostgreSQL installation I opened: #2279363: file_managed.uri should not specify a prefixed unique key
Comment #5
damien tournoud commentedPostgreSQL can do this with functional indexes:
But those type of unique constraints usually don't make that much sense.
Comment #6
tstoecklerOK, being bold here. Although @Berdir, @Damien Tournoud, and @sun (in #2279363-2: file_managed.uri should not specify a prefixed unique key) - albeit tentatively - agreeing on this doesn't even seem that bold.
Let's just kill this very pointless "feature".
Re-titling, re-classifying as 'task', and postponing on #2279363: file_managed.uri should not specify a prefixed unique key: We can only remove support once we remove usages.
Other than that the following should be enough for this issue:
hook_schema()documentationComment #7
tstoecklerComment #8
stefan.r commentedComment #9
tstoecklerComment #10
andypostsuppose it makes sense to use functional indexes to prevent subject
Comment #15
rosk0Just installed fresh copy of 8.5.x in PostgreSQL without any issues. Enabled all the core modules including the ones from "Web services" section - no issues.
Should we cancel this one?
Moving to other parent just in case.
Comment #27
mondrakeI think at this point this is just a dupe of #3082239: Forbid limited length primary and unique keys, allow only in indexes.