Re: Problem with UNIQUE constraint on two columns

You have a primary key on the user_id column of the user_public_keys
table. Therefore you cannot insert more than one row containing 'u0'.

I think your intention is to have multiple user_public_key rows per
user. In this case, remove the PRIMARY KEY constraint from this table.

Fred

On Sun, May 24, 2015, at 15:25, [email protected] wrote:
> Hello.
> 
> I'm having a strange problem with a UNIQUE constraint on this set
> of tables.
> 
> CREATE TABLE keys_public (
>   key_row         INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY
>   KEY,
>   key_id          VARCHAR(4)                           NOT NULL,
>   key_fingerprint VARCHAR(4)                           NOT NULL,
>   key_data        VARCHAR(4)                           NOT NULL
> );
> 
> CREATE TABLE user_ids (
>   user_id VARCHAR(4) NOT NULL PRIMARY KEY
> );
> 
> CREATE TABLE user_public_keys (
>   user_id VARCHAR(4) NOT NULL PRIMARY KEY,
>   key_row INTEGER    NOT NULL,
> 
>   UNIQUE (user_id, key_row),
>   FOREIGN KEY (user_id) REFERENCES user_ids    (user_id),
>   FOREIGN KEY (key_row) REFERENCES keys_public (key_row)
> );
> 
> INSERT INTO keys_public VALUES (DEFAULT, 'kid0', 'kfp0', '');
> INSERT INTO user_ids VALUES ('u0');
> INSERT INTO user_public_keys VALUES ('u0', 0);
> 
> SELECT * FROM keys_public;
> SELECT * FROM user_ids;
> SELECT * FROM user_public_keys;
> 
> At this point, everything is fine. However...
> 
> INSERT INTO keys_public VALUES (DEFAULT, 'kid1', 'kfp1', '');
> SELECT * FROM keys_public;
> INSERT INTO user_public_keys VALUES ('u0', 1);
> -- integrity constraint violation: unique constraint or index violation;
> SYS_PK_10124 table: USER_PUBLIC_KEYS
> 
> The keys_public table does contain a key_row with value 1. The
> user_public_keys table contains one row ('u0', 0). There is no foreign
> key or unique constraint violation that I can see.  What's going
> on here?
> 
> 
> 
> ------------------------------------------------------------------------------
> One dashboard for servers and applications across Physical-Virtual-Cloud 
> Widest out-of-the-box monitoring support with 50+ applications
> Performance metrics, stats and reports that give you Actionable Insights
> Deep dive visibility with transaction tracing using APM Insight.
> http://ad.doubleclick.net/ddm/clk/290420510;117567292;y
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
One dashboard for servers and applications across Physical-Virtual-Cloud 
Widest out-of-the-box monitoring support with 50+ applications
Performance metrics, stats and reports that give you Actionable Insights
Deep dive visibility with transaction tracing using APM Insight.
http://ad.doubleclick.net/ddm/clk/290420510;117567292;y