Home>
I want to know the table creation syntax

Composite keys for tables with composite keys
I want to use it as a foreign key to another table, but I can't write it well.

Error message
ERROR: there is no unique constraint matching given keys for referenced table "reservable_room"
Applicable source code
create table reservation (
  reservation_id serial primary key,
  start_time time not null,
  end_time time not null,
  reserved_date date not null,-
  room_id int references meeting_rooom (room_id),
  user_id varchar (255) references usr (user_id),
  FOREIGN KEY (reserved_date) references reservable_room (reserved_date)-Is this not working?
);
// The last three tables have been created
create table reservable_room (
  reserved_date date,
  room_id int references meeting_rooom (room_id),
  primary key (reserved_date, room_id)
);
create table usr (
  user_id varchar (255) primary key,
  first_name varchar (255) not null,
  last_name varchar (255) not null,
  pass varchar (255) not null,
  role_name varchar (255) not null
);
create table meeting_rooom (
  room_id serial primary key,
  room_name varchar (255) not null
);
Tried Supplemental information (FW/tool version etc.)

Please provide more detailed information here.

  • Answer # 1

    The following article (QA) may be helpful.

    I can't create foreign keys successfully. why?

      If you receive a PostgreSQL error like

    ERROR: there is no unique constraint matching given keys for referenced table"xxxx" ;, it is due to a foreign key creation failure.
      There are a few points for users to remember in order to declare foreign keys in PGSQL:
      1. FOREIGN KEY constraints must reference PRIMARY KEY or UNIQUE constraints.
      2. The two key fields must be compatible data types.
      3. You must have REFERENCES privilege on both the referencing table and the referenced table.

    I think I'm stuck on the first of these.
    It seems that the column to which the foreign key is pasted does not have PRIMARY KEY or UNIQUE.
    You can't decide which data to refer to unless you are assured that it is unique.

  • Answer # 2

    Foreign key constraints must match the primary key.
    In addition, it is necessary to consider dependencies when creating or deleting. * Create order
    You can create it as follows.

    drop table if exists usr cascade;
    drop table if exists meeting_rooom cascade;
    drop table if exists reservable_room cascade;
    drop table if exists reservation;
    create table usr (
      user_id varchar (255) primary key,
      first_name varchar (255) not null,
      last_name varchar (255) not null,
      pass varchar (255) not null,
      role_name varchar (255) not null
    );
    create table meeting_rooom (
      room_id serial primary key,
      room_name varchar (255) not null
    );
    create table reservable_room (
      reserved_date date,
      room_id int references meeting_rooom (room_id),
      primary key (reserved_date, room_id)
    );
    create table reservation (
      reservation_id serial primary key,
      start_time time not null,
      end_time time not null,
      reserved_date date not null,
      room_id int references meeting_rooom (room_id),
      user_id varchar (255) references usr (user_id),
      FOREIGN KEY (reserved_date, room_id) references reservable_room (reserved_date, room_id)
    );