Thread: Implementing standard SQL's DOMAIN constraint

Happy New Year all,

My readings taught me that standard SQL has a domain constraint that checks
for the same valid characters in a column common to multiple tables.
Example:

CREATE DOMAIN state_code AS char(2)
DEFAULT '??'
CONSTRAINT valid_state_code
CHECK (value IN ('AL', 'AK', 'AZ', ...));

This applies to all tables each having a column named state_code.

I see the value of this feature when multiple tables have start_date and
end_date columns with a constraint that ensures the start_date is <= to the
end date.

Reading the release 10 manual I find many constraints and I want to learn
which one will implement this feature. A pointer is needed.

Rich

CREATE DOMAIN state_code AS char(2)
DEFAULT '??'
CONSTRAINT valid_state_code
CHECK (value IN ('AL', 'AK', 'AZ', ...));

This applies to all tables each having a column named state_code.

On Wed, 2 Jan 2019, David G. Johnston wrote:

> There is no magic name logic involved. A domain is just a type with
> inherent constraints that are user definable. You make use of it like any
> other type.
>
> Create table tbl (
> column_name state_code not null
> )
>
> Values stored in column_name are now of type state_code and constrained to
> be one of the check constraint values.

David,

   I'm not following you. I have two tables each with a column,
state_code char(2) NOT NULL.

   Do you mean that I need to write the column constraint for each table? If
not, I don't see from your response how to implement the multi-table
constraint for this column.

Regards,

Rich

On Wed, 2 Jan 2019, David G. Johnston wrote:

There is no magic name logic involved. A domain is just a type with
inherent constraints that are user definable. You make use of it like any
other type.

Create table tbl (
column_name state_code not null
)

Values stored in column_name are now of type state_code and constrained to
be one of the check constraint values.

David,

  I'm not following you. I have two tables each with a column,
state_code char(2) NOT NULL.

  Do you mean that I need to write the column constraint for each table? If
not, I don't see from your response how to implement the multi-table
constraint for this column.

On Wed, 2 Jan 2019, David G. Johnston wrote:

>>   I'm not following you. I have two tables each with a column,
>> state_code char(2) NOT NULL.

> That is a char(2) column for which ‘??’ is a valid value.  The fact that it
> is named state_code is immaterial; the domain that you created doesn’t get
> used.  There is no magic linking just by virtue of using the same name.
>
> Change char(2) to state_code if you wish to apply the domain on the column.

David,

   I think I'm now on your page. In the schema I change the column data type
to state_code, then I add the SQL code creating the domain at the top of the
.sql file. Yes?

Thanks,

Rich

On Wed, 2 Jan 2019, David G. Johnston wrote:

  I'm not following you. I have two tables each with a column,
state_code char(2) NOT NULL.

That is a char(2) column for which ‘??’ is a valid value.  The fact that it
is named state_code is immaterial; the domain that you created doesn’t get
used.  There is no magic linking just by virtue of using the same name.

Change char(2) to state_code if you wish to apply the domain on the column.

David,

  I think I'm now on your page. In the schema I change the column data type
to state_code, then I add the SQL code creating the domain at the top of the
.sql file. Yes?

On Wed, 2 Jan 2019, David G. Johnston wrote:

> You add the create domain command once before any objects that make use of
> it.

David,

   This is the answer I sought: postgres supports the create domain command.
I did not see this in your first response.

Thanks very much,

Rich

On Wed, 2 Jan 2019, David G. Johnston wrote:

You add the create domain command once before any objects that make use of
it.

David,

  This is the answer I sought: postgres supports the create domain command.
I did not see this in your first response.

On Wed, 2 Jan 2019, Ron wrote:

> Note that a CHECK constraint with 50 items is a *Very Bad Idea*, since
> changing such a constraint is very painful. Use a FK constraint instead.

Ron,

   It's even longer with Canadian provinces included. I gratefully accept
your advice and will use a table and foreign key instead.

Regards,

Rich