CODEWITHSUNDEEP
sqldatabaseliquibase
Alper Arslan
Alper Arslan

Reputation: 11

Liquibase CONSTRAINT for NotNull values

ALTER TABLE apple_member ADD CONSTRAINT uc_banana_member UNIQUE (banana_id, apple_id) WHERE banana_id IS NOT NULL

I want to add unique constraint only for not null values but i get syntax error:

Reason: liquibase.exception.DatabaseException: Incorrect syntax near the keyword 'WHERE'. [Failed SQL: (156) ALTER TABLE apple_member ADD CONSTRAINT uc_banana_member UNIQUE (banana_id, apple_id) WHERE banana_id IS NOT NULL]

I really don't know what else I can do.

Purpose: add the cosntratint for not null values

Upvotes: 0

Views: 101

Answers (1)

The Impaler
The Impaler

Reputation: 48875

Unfortunately the SQL Standard does not allow you to add a formal constraint for a subset of the rows. However, you can use an index to impose this restriction.

For example:

create unique index ix1 on apple_member (banana_id, apple_id)
where banana_id is not null

Upvotes: 0

Related Questions