14

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 NULLs 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
)
1
  • Why not use a proper relational design with an optional foreign key to an address table? Commented Apr 3, 2021 at 12:08

2 Answers 2

13

The correct syntax for a check constraint for a composite type would look like this:

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
);

Let's check:

melkij=> create table test_address_domain (a address_domain);
CREATE TABLE
melkij=> insert into test_address_domain values (('foo', 'bar', 11));
INSERT 0 1
melkij=> insert into test_address_domain values (('foo', 'bar', null)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"
melkij=> insert into test_address_domain values (('foo', null, 11)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"
melkij=> insert into test_address_domain values ((null, 'bar', 11)); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"
melkij=> insert into test_address_domain values (null); -- fails
ERROR:  value for domain address_domain violates check constraint "address_domain_check"

But pay attention to the last line. It seems that this is not what you want. Try allow explicitly:

melkij=*> create domain address_domain2 as address 
check (
  value is null or (
  (value).city is not null and 
  (value).address_line is not null and
  (value).zip_code is not null
));
CREATE DOMAIN
melkij=*> create table test_address_domain2 (a address_domain2);
CREATE TABLE
melkij=*> insert into test_address_domain2 values (null); -- works now
INSERT 0 1
1
  • 2
    Is it possible to create a domain and composite type in a single expression? It seems right now you have to first CREATE TYPE then do CREATE DOMAIN? Commented Dec 12, 2022 at 9:32
1

I don't know if you can create such domain constraints for composite types, but if you use regular types like below, you can add a constraint like:

create table companies 
( company_id serial primary key
, company_name text not null
, headquaters_city text
, headquaters_address_line text
, headquaters_zipcode text
,    constraint all_or_nothing_address 
         check ((case when headquaters_city is not null then 1 else 0 end +
                 case when headquaters_address_line is not null then 1 else 0 end +
                 case when headquaters_zipcode is not null then 1 else 0 end) in (0,3))
);

for more complicated scenarios you can add a unique weight to each attribute and check for certain combinations:

check ((case when headquaters_city is not null then 1 else 0 end +
                 case when headquaters_address_line is not null then 2 else 0 end +
                 case when headquaters_zipcode is not null then 4 else 0 end) in (1,2,7))

That said, you may want to normalize your design to something like:

create table companies 
( company_id serial primary key
, company_name text not null
);

create table company_adresses
( company_id int not null primary key
      references companies (company_id)
, headquaters_city text not null
, headquaters_address_line text not null
, ...
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.