UNIQUE constraint failed SQL-CodePudding

I want to create two Tables, but it does not work because of "UNIQUE constraint failed".

Can someone explain to me what is wrong with my querees?

CREATE TABLE A (
ka INT PRIMARY KEY,
a2 VARCHAR(1)
);

INSERT INTO A VALUES (1,'s');
INSERT INTO A VALUES (2,'s');
INSERT INTO A VALUES (3,'s');
INSERT INTO A VALUES (4,'m');
INSERT INTO A VALUES (5,'m');
INSERT INTO A VALUES (6,'b');
INSERT INTO A VALUES (7,'b');
INSERT INTO A VALUES (8,'b');
INSERT INTO A VALUES (9,'b');

CREATE TABLE B (
a2 VARCHAR(1),
b2 INT,
PRIMARY KEY (a2),
FOREIGN KEY (a2) REFERENCES A(a2)
);

INSERT INTO B VALUES ('s',12);
INSERT INTO B VALUES ('s',23);
INSERT INTO B VALUES ('s',34);
INSERT INTO B VALUES ('m',45);
INSERT INTO B VALUES ('m',56);
INSERT INTO B VALUES ('b',67);
INSERT INTO B VALUES ('b',78);
INSERT INTO B VALUES ('b',89);
INSERT INTO B VALUES ('b',90);

CodePudding user response：

(Edited: there is more than one issue here.)

A primary key for table B must be unique. In your case, it needs to be something other than a2. If you need a primary key (usually you do just as a part of good design), you likely need a separate dedicated column.

Something like:

CREATE TABLE B (
    kb INT,
    a2 VARCHAR(1),
    b2 INT,
    PRIMARY KEY (kb),
    --FOREIGN KEY (a2) REFERENCES A(a2) -- This is also an issue
);

INSERT INTO B VALUES (101, 's',12);
INSERT INTO B VALUES (102, 's',23);
INSERT INTO B VALUES (103, 's',34);
INSERT INTO B VALUES (104, 'm',45);
INSERT INTO B VALUES (105, 'm',56);
INSERT INTO B VALUES (106, 'b',67);
INSERT INTO B VALUES (107, 'b',78);
INSERT INTO B VALUES (108, 'b',89);
INSERT INTO B VALUES (109, 'b',90);

EDIT:

The second issue is that your foreign key from table B is referencing a non-unique column in table A. Foreign keys define a many-to-one relationship, but your data indicates a potentially many-to-many relationship. The first three "s" rows in table B each match all three "s" rows in table A. While you can define a JOIN in a query that matches ON B.a2 = A.a2, you can't use a foreign key to define this relationship.