Comments (3)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 bryantsai commented Permalink

I think in Oracle, the following are legal for a unique key of 2 columns:

 
(NULL, 1)
(NULL, 2)
 
but the following are not:
 
(NULL, 1)
(NULL, 1)

2 SergeRielau commented Permalink

You are correct. This was a typo on my end.

3 ibmforumvin commented Permalink

Hi,

 
What if the table already has a GENERATED ALWAYS AS IDENTITY and no Primary Key ? We will not be able to introduce a Abstract Primary Key. In that case, can we just go ahead use the already available GENERATED ALWAYS AS IDENTITY ?
 
Let's assume we have no primary key and one GENERATED ALWAYS AS IDENTITY in this example:
 
CREATE TABLE T(c1 VARCHAR2(10),c2 INTEGER GENERATED BY DEFAULT
AS IDENTITY (
START WITH 1 ,
INCREMENT BY 1 ,
MINVALUE 1 ,
MAXVALUE 2147483647 ,
NO CACHE )
);
 
CREATE UNIQUE INDEX idx ON T(c1);
 
In DB2 would you translate that as such ??
 
CREATE TABLE T(c1 VARCHAR(10),
idx INT GENERATED ALWAYS AS (CASE WHEN c1 IS NULL THEN c2 ELSE NULL END));
 
CREATE UNIQUE INDEX idx ON T(c1, idx);