How to Configure Foreign Keys in Columns Used by the Composite Principal Key in PostgreSQL

I'd like to create a table with foreign key constraints in the column that is set to the compound main key in PostgreSQL 9.4. What should I do?

Currently, I am using SQL as follows

 --attend
create table attend(
  room_id bigint not null,
  FOREIGN KEY(room_id)REFERENCES rooms(room_id)ON DELETE CASCADE,
  user_id bigint not null,
  FOREIGN KEY(user_id)REFERENCES users(user_id)ON DELETE SET NULL,
  profile_img varchar(255) not null, -- redundant but performance thinking

  PRIMARY KEY (room_id, user_id)
);

The running environment is Playframework 2.3.8 with SQL error when performing play-flyway migration

postgresql

2022-09-29 22:54

1 Answers

The version is a little different, but it seems to work in 9.3.

operation samples:
http://sqlfiddle.com/#!15/256d8/1

create table rooms (room_id bigint primary key);
create table users(user_id bigint primary key);
create table attend(
  room_id bigint not null,
  FOREIGN KEY(room_id)REFERENCES rooms(room_id)ON DELETE CASCADE,
  user_id bigint not null,
  FOREIGN KEY(user_id)REFERENCES users(user_id)ON DELETE SET NULL,
  profile_img varchar(255) not null,
  PRIMARY KEY (room_id, user_id)
);

insert into rooms values(1);
insert into users values(1);
insert into attend values (1, 1, ');

select * from attach;

^{This post is Comments etc., posted as Community Wiki.}

^{This post is Comments etc., posted as community wiki.}

2022-09-29 22:54

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656