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
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.
© 2024 OneMinuteCode. All rights reserved.