CODEWITHSUNDEEP
sqlpostgresqlsyntax-error
SQL Learner 1
SQL Learner 1

Reputation: 147

How to add CHECK constraints using multiple columns in PostgreSQL

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

Answers (1)

Jim Jones
Jim Jones

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

Related Questions