How to specify not null contraints for the fields of composite types in postgres
14
votes
3
answers
4371
views
Let's say I would like to have a composite type for address, like:
create type address as (
city text,
address_line text,
zip_code int
);
And to make data integrity better, I don't want to allow NULL
s to be members of city
, address_line
, or zip_code
. So I would like to have a not null
constraint for those fields.
Creating domain checks isn't working for me. So this code produces error:
create domain address_domain as address
check (
value.city is not null and
value.address_line is not null and
value.zip_code is not null
);
You might say: "Well, why won't you store address as three columns, and add contraints to the fields?". And I will answer with that I would like to have ability to make address itself nullable, but if address is present, all of it's fields should be present as well. Something like this:
create table companies (
id serial primary key,
name text not null,
headquaters address -- this one can be null tho
)
Asked by Link0
(243 rep)
Apr 3, 2021, 11:40 AM
Last activity: Oct 8, 2024, 03:50 AM
Last activity: Oct 8, 2024, 03:50 AM