0

I have created a table 'DayPass':

CREATE TABLE DayPass (
    memberNo INT PRIMARY KEY, FOREIGN KEY (memberNo) REFERENCES DayPass(memberNo),
    startDate Date,
    numberDays INT,
    price VARCHAR(30),
    check(numberDays > 0)
);

I am trying to insert these values:

INSERT INTO DayPass (memberNo, startDate, numberDays, price)
VALUES (3, '2022-01-01', '5', '£9.99');
INSERT INTO DayPass
VALUES (3, '2022-02-01', '5', '£9.99');
INSERT INTO DayPass
VALUES (3, '2022-03-01', '£5', '£9.99');
SELECT * FROM DayPass;

but Postgres gives me an error:

ERROR: duplicate key value violates unique constraint "daypass_pkey"

if am unsure where i am going wrong

Henry Woody
  • 14,024
  • 7
  • 39
  • 56
Davis Fawole
  • 31
  • 1
  • 3
    Can you provide the table definition? – JustAnotherCoder Mar 27 '22 at 14:37
  • Please provide the table's DDL. How is the constraint "daypass_pkey"defined? And why price is a varchar-column ? – Sergey Mar 27 '22 at 14:37
  • CREATE TABLE DayPass ( memberNo INT PRIMARY KEY, FOREIGN KEY (memberNo) REFERENCES DayPass(memberNo), startDate Date, numberDays INT, price VARCHAR(30), check(numberDays >0) ); – Davis Fawole Mar 27 '22 at 14:38
  • I was unsure of how to add the '£' to the price section as it was a must that I have the currency values on my prices – Davis Fawole Mar 27 '22 at 14:39
  • ALTER TABLE DayPass ADD CONSTRAINT PK_DayPass PRIMARY KEY (startDate); – Davis Fawole Mar 27 '22 at 14:40
  • i needed to make startdate a PK also so I created this constraint – Davis Fawole Mar 27 '22 at 14:41
  • 1
    The currency should be another column and of course, not a symbol of the currency,but its ISO-code – Sergey Mar 27 '22 at 14:41
  • How would i go about doing that please? – Davis Fawole Mar 27 '22 at 14:47
  • Hello, im sure the problem is your primary key is not a sequence, please take a look to this question/answer https://stackoverflow.com/q/4448340/9053821 – darioxlz Mar 27 '22 at 15:02
  • 1
    A `PRIMARY KEY` is a column or group of columns that have values that uniquely identify a record. Neither `memberNo` or `startDate` on their own meet that criteria. You could create a PK over both `(memberNo, startDate)` or create a separate column that uses a sequence to generate unique values. See `GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY` here [Create Table](https://www.postgresql.org/docs/current/sql-createtable.html). – Adrian Klaver Mar 27 '22 at 15:51

0 Answers0