Reputation: 147
I'm having trouble modelling this disjoint in Postgresql. Error message shows:
ERROR: syntax error at or near "or" LINE 2: add constraint group_collection or user_collection Check (g... ^ What could I do to fix this issue?
create table collections(
collectionId serial,
title TitleValue,
description text,
key integer not null,
group_coll_id integer,
user_coll_id integer,
primary key (collectionId),
foreign key (key) references photos(photoid)
----photos is from a previous table--- );
create table group_collection(
group_coll_id integer,
owns integer not null,
primary key (group_coll_id),
foreign key (group_coll_id) references collections(collectionId),
foreign key (owns) references groups(groupid)
);
create table user_collection(
user_coll_id integer,
owns integer not null,
primary key (user_coll_id),
foreign key (user_coll_id) references collections(collectionId),
foreign key (owns) references users(userid)
);
alter table collections add constraint fk_collections_group foreign key (group_coll_id) references group_collection(group_coll_id);
alter table collections add constraint fk_collections_user foreign key (user_coll_id) references user_collection(user_coll_id);
alter table only collections
add constraint group_collection or user_collection Check (group_coll_id is null or user_coll_id is null)
Upvotes: 3
Views: 2338
Reputation: 19693
Welcome to SO. You're naming your constraint group_collection or user_collection
, which isn't a valid label :)
The ADD CONSTRAINT
syntax for a table CREATE TABLE t (x INT, y INT);
should be
ALTER TABLE t ADD CONSTRAINT any_label CHECK (x IS NULL OR y IS NULL)
This should work in your case:
alter table collections
add constraint mycheck check (group_coll_id is null or user_coll_id is null)
Demo: db<>fiddle
Upvotes: 1