Question 293175 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to fix my DB model to remedy unique violations?

I know that primary key values cannot be repeated. However, I want to make it so that games can be played on more than one platform. For example "Dark Souls" on PC and PS4. How do I modify the relational design so that I can give a SpelID multiple consoles without repeating it?
This is my code:

drop table if exists Spel;
drop table if exists Plattform;
drop table if exists Företag;

create table Företag (
    FöretagID varchar(50) primary key,
    Företag text,
    Högk text
);

create table Plattform (
    PlattformID integer primary key,
    Plattform varchar (50)
);

create table Spel (
    SpelID integer primary key,
    namn varchar(50),
    genre text,
    år date,
    Åldergräns char(2),
   
    foreign key (SpelID) references Plattform (PlattformID),
    foreign key (namn) references Företag (FöretagID)
);

insert into Företag (FöretagID, Företag, Högk)
    values ('Zelda', 'Capcom', 'Japan'),
           ('Bloodborne', 'From', 'Japan');
        

insert into Plattform (PlattformID, Plattform)
    values (0001, 'Nintendo'),
           (0001, 'PS4'),
           (0002, 'PS4'),
           (0002, 'Nintendo');


insert into Spel (SpelID, namn, genre, år, Åldergräns)
    values (0001, 'Zelda', 'Äventyr', '2015-10-12', 7),
           (0002, 'Bloodborne', 'A-RPG', '2017-09-09', 18);

I have tried to add them in the Plattform table but I know that a primary key can only have one specific value. (Spel = Game)

Tags:

2

Answers


  1. 0

    First you need to drop the current CONSTRAINT:

    -- https://stackoverflow.com/questions/29075413/change-primary-key-in-postgresql-table
-- DROP THE Primary key
-- ALTER TABLE plattform DROP CONSTRAINT  plattform_pkey ;

-- not possible because of some forein keys, so drop other keys tooo:
ALTER TABLE plattform DROP CONSTRAINT  plattform_pkey CASCADE;

    Warning: Because of the CASCADE other constraints are also dropped… You will have to re-create those too! (see DBFIDDLE from more info)

    Then you als need to add the field SpellID to the table Plattform:

    -- add field SpellID
ALTER TABLE plattform ADD COLUMN spellID INTEGER;

    After that you can re-create the primary key of table Plattform:

    -- add Primary KEY
ALTER TABLE plattform ADD PRIMARY KEY(plattformID, spellID);

    see: DBFIDDLE

    Login or Signup to reply.
  2. 0

    One game can be published on many platforms, and one platform can host many games. You need a many-to-many relationship between games and platforms in your relational model. See:

    Could look like this:

    CREATE TABLE företag (
  företagid integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- !
, företag text
, högk text
);

CREATE TABLE spel (
  spelid integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- !
, företagid integer REFERENCES företag  -- !
, spel text  -- !
, genre text
, år date
, åldergräns integer  -- ?!
);

CREATE TABLE plattform (
  plattformid text PRIMARY KEY  -- !! natural key?
, plattform text
  -- more?
);

    Above modifications are minor fixes and other suggestions.
    Here comes the magic (the implementation of the m:n relationship):

    CREATE TABLE spel_plattform (
  spelid integer REFERENCES spel
, plattformid text REFERENCES plattform
  -- more?
, PRIMARY KEY (spelid, plattformid)
);

    Sample data:

    INSERT INTO företag (företagid, företag, högk) VALUES
  (1, 'Capcom', 'Japan')
, (2, 'From', 'Japan')
;

INSERT INTO spel (spelid, företagid, spel, genre, år, åldergräns) VALUES
  (1, 1, 'Zelda'     , 'Äventyr', '2015-10-12',  7)
, (2, 2, 'Bloodborne', 'A-RPG'  , '2017-09-09', 18)
, (3, 2, 'Elden Ring', 'A-RPG'  , '2022-01-01', 18)
;

INSERT INTO plattform (plattformid, plattform) VALUES
  ('NES', 'Nintendo Entertainment System')
, ('PS4', 'PlayStation 4')
;

INSERT INTO spel_plattform (spelid, plattformid) VALUES
  (1, 'NES')
, (1, 'PS4')
, (2, 'NES')
, (2, 'PS4')
, (3, 'PS4')
;

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search