How to add CHECK constraints using multiple columns in PostgreSQL

Question

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)

Jim Jones · Accepted Answer · 2021-03-24 07:57:59Z

1

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

answered Mar 24, 2021 at 7:57

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add CHECK constraints using multiple columns in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related