How to combine two columns as one primary key sql

Question

I have a database contain a table of id cards like this query

CREATE TABLE ID_CARD 
(
    N_CARD          VARCHAR(20) NOT NULL,
    ISSUE_DATE      DATE NOT NULL,
    ID_TYPE         VARCHAR(2) NOT NULL,
    CONSTRAINT PKEY_ID_CARD PRIMARY KEY(N_CARD, ISSUE_DATE)
);

but an id card can have the same N_CARD but with different ISSUE_DATE and also an id card can have the same ISSUE_DATE but diffrent N_CARD so two id cards can't have the same N_CARD and ISSUE_DATE at the same time.

With SQL Workbench/j the SQL source for this table is

    DROP TABLE ID_CARD;

CREATE CACHED TABLE ID_CARD
(
   N_CARD      VARCHAR(20)   NOT NULL,
   ISSUE_DATE  DATE          NOT NULL,
   ID_TYPE     VARCHAR(2)    NOT NULL
);

ALTER TABLE ID_CARD
   ADD CONSTRAINT PKEY_ID_CARD
   PRIMARY KEY (N_CARD, ISSUE_DATE);

CREATE UNIQUE INDEX FKEY_N_CARD_CLIENT_INDEX_8
   ON ID_CARD (N_CARD ASC);

CREATE UNIQUE INDEX FKEY_ISSUE_DATE_CLIENT_INDEX_8
   ON ID_CARD (ISSUE_DATE ASC);

When I execute this query

INSERT INTO ID_CARD (N_CARD, ISSUE_DATE, ID_TYPE)
VALUES ('101215', DATE '2019-11-11', 'DL');
commit;
INSERT INTO ID_CARD(N_CARD,ISSUE_DATE,ID_TYPE)VALUES('101215',DATE '2019-11-12','DL');
commit;

this error

Unique index or primary key violation: "FKEY_N_CARD_CLIENT_INDEX_8 ON 

PUBLIC.ID_CARD(N_CARD) VALUES ('101215', 9)"; SQL statement:
INSERT INTO ID_CARD(N_CARD,ISSUE_DATE,ID_TYPE)VALUES('101215',DATE '2019-11-11','DL') [23505-193] [SQL State=23505, DB Errorcode=23505]

the question is how to represent thid situation in the database ?

What is wrong with your code? That seems to be the right way to do what you want. — Gordon Linoff, Mar 22 '17 at 13:51
the error whe i insert two id cards with the same N_CARD and diffrent ISSUE_DATE — A. Ilyes, Mar 22 '17 at 14:08
Unique index or primary key violation: "FKEY_N_CARD_CLIENT_INDEX_8 ON PUBLIC.ID_CARD(N_CARD) VALUES ('101215', 9)"; SQL statement: INSERT INTO ID_CARD(N_CARD,ISSUE_DATE,ID_TYPE)VALUES('101215',DATE '2019-11-11','DL') [23505-193] [SQL State=23505, DB Errorcode=23505] — A. Ilyes, Mar 22 '17 at 14:19

score 1 · Answer 1 · answered Mar 22 '17 at 14:22

1

Instead of two unique indexes as

CREATE UNIQUE INDEX FKEY_N_CARD_CLIENT_INDEX_8
    ON ID_CARD (N_CARD ASC);

CREATE UNIQUE INDEX FKEY_ISSUE_DATE_CLIENT_INDEX_8
    ON ID_CARD (ISSUE_DATE ASC);

you need a single unique index as

CREATE UNIQUE INDEX FKEY_N_DT_CLIENT_INDEX_8
    ON ID_CARD (N_CARD ASC, ISSUE_DATE ASC);

answered Mar 22 '17 at 14:22

Mark Adelsberger

42,148
4
35
52

what the use of ASC in this case – A. Ilyes Mar 22 '17 at 14:30
the same problem the unique index of N_CARD and of ISSUE_DATE remain @Mark Adelsberger – A. Ilyes Mar 22 '17 at 14:39
@A.Ilyes - I don't presume to know why the index columns were defined as they were; I'm addressing the problem without unnecessary changes to the code – Mark Adelsberger Mar 22 '17 at 15:06
@A.Ilyes - If the problem remains, I would conclude that you did not remove the individual unique indexes. – Mark Adelsberger Mar 22 '17 at 15:07
the individual unique index it created by default with db managment system but when i add a new column contain integer auto_increment and add N_CARD and ISSUE_DATE ass a unique key the problem solved thank you – A. Ilyes Mar 22 '17 at 19:18

score 0 · Answer 2 · answered Mar 23 '17 at 01:34

Your first create table statement is correct for what you want:

CREATE TABLE ID_CARD (
    N_CARD          VARCHAR(20) NOT NULL,
    ISSUE_DATE      DATE NOT NULL,
    ID_TYPE         VARCHAR(2) NOT NULL,
    CONSTRAINT PKEY_ID_CARD PRIMARY KEY(N_CARD, ISSUE_DATE)
);

However, the unique indexes are incorrect:

CREATE UNIQUE INDEX FKEY_N_CARD_CLIENT_INDEX_8 ON ID_CARD (N_CARD ASC);

CREATE UNIQUE INDEX FKEY_ISSUE_DATE_CLIENT_INDEX_8 ON ID_CARD (ISSUE_DATE ASC);

I don't know why these are being created, but you should drop them.

score 0 · Answer 3 · answered Mar 26 '17 at 14:53

hi i solve my problem with this query

CREATE TABLE ID_CARD(
ID              INTEGER NOT NULL AUTO_INCREMENT,
N_CARD          VARCHAR(20) NOT NULL,
ISSUE_DATE      DATE NOT NULL,
ID_TYPE         VARCHAR(2) NOT NULL,
CONSTRAINT PKEY_ID_CARD PRIMARY KEY(ID),
CONSTRAINT UI_ID_CARD UNIQUE INDEX(N_CARD,ISSUE_DATE)
);

i don't know if this is a good solution but my problem is solved any optimization i'm here

How to combine two columns as one primary key sql

3 Answers3