Problem with UNIQUE constraint on two columns

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem with UNIQUE constraint on two columns

org.hsqldb
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Problem with UNIQUE constraint on two columns

Fred Toussi-2
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, [hidden email] 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
> [hidden email]
> 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
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|

Re: Problem with UNIQUE constraint on two columns

org.hsqldb
On Sun, May 24, 2015 6:17 pm, Fred Toussi wrote:
> 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.

... Oops.

Thank you, I've no idea how I managed to miss that.




------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user