SQL table with primary key over two columns and unique values in both columns-CodePudding

How to create a table in SQL with the following attributes?

The table has two columns A and B.
The primary key of the table is (A, B).
All values in A are unique. Pseudo code: (Count(A) == COUNT(SELECT DISTINCT A)).
All values in B are also unique.

CREATE TABLE IF NOT EXISTS myTable(
    A VARCHAR(32) PRIMARY KEY NOT NULL, -- A HAS DISTINCT VALUES
    B VARCHAR(32) NOT NULL              -- B HAS DISTINCT VALUES
);

INSERT INTO myTable VALUES ('A1', 'B1') --> Add value
INSERT INTO myTable VALUES ('A1', 'B2') --> Do not add value
INSERT INTO myTable VALUES ('A2', 'B2') --> Add value
INSERT INTO myTable VALUES ('A3', 'B3') --> Add value
INSERT INTO myTable VALUES ('A4', 'B3') --> Do not add value
INSERT INTO myTable VALUES ('A4', 'B4') --> Add value
INSERT INTO myTable VALUES ('A5', 'B6') --> Add value

CodePudding user response：

To define a compound PRIMARY KEY:

CREATE TABLE myTable
(
    A VARCHAR(32) NOT NULL,
    B VARCHAR(32) NOT NULL,

    CONSTRAINT PK_AB primary key (A,B),
    CONSTRAINT UQ_A UNIQUE(A),
    CONSTRAINT UQ_B UNIQUE(B)
);

Please note: a table with just 2 columns with both columns in the primary key smells funny.