The current definition of the bookmarks table for PostgreSQL has uid as a primary key, which means that each user is only able to have one bookmark. Not so useful. :-)

I've attached a patch that fixes the problem.

CommentFileSizeAuthor
bookmarks.pgsql.patch558 bytesSteve Simms

Comments

Froestel’s picture

Froestel commented
Title: PostgreSQL definition limits users to one bookmark each » PostgreSQL: The one bookmark problem 2 solutions
Version: master » 4.6.x-1.x-dev
Assigned: Unassigned » Froestel

Relating to the patch by smsimms:
Just access your postgres DB, alter the bookmarks table changing the uid column to be of type integer as the patch does. Don't forget to drop the primary key on that column since this is wrong! It is obviously supposed to be a foreign key relating this column to the uid column of the users table. It does no harm to leave out the foreign key constraint but it is way better style to add it.

So the appropriate SQL snippet to fix the problem within a running installation is something like

ALTER TABLE bookmarks ALTER COLUMN uid TYPE integer;
ALTER TABLE bookmarks DROP CONSTRAINT bookmarks_pkey;
ALTER TABLE bookmarks ADD FOREIGN KEY (uid) REFERENCES users(uid) ON DELETE CASCADE;

You can patch before installation:
The file bookmarks.pgsql should contain the following code:

CREATE TABLE bookmarks (
  uid INTEGER REFERENCES users(uid),
  url VARCHAR(255) NOT NULL,
  title VARCHAR(255) NOT NULL
);

instead of the given one.

Steve Simms’s picture

Steve Simms commented
Status: Needs review » Closed (fixed)

I've just committed the patch to convert the unique key on uid to a regular index.

Since the rest of the Drupal project doesn't seem to be all that interested in using foreign keys, I didn't add it to this patch (it'll probably just confuse people who aren't expecting it). If that changes, I'll be happy to add it.