How to alter a domain

[Former Member]

I am trying to change the check constraint of a domain and not having any luck. Is there are a way to do this in Sybase?

When I am in Sybase Central and go to Domains and click on the domain I am trying to change I can see the constraint in the check constraints tab but not edit it.

I've also tried altering the domain using Interactive SQL but I just get a syntax error not matter what I try. For instance I tried; ALTER DOMAIN domain_name CHECK [new check here]

I also tried to drop this domain using CASCADE so table columns using it would revert to underlying data type but again just getting a syntax error. What I tried was DROP DOMAIN domain_name [CASCADE]

Any ideas appreciated.

[Breck_Carter]

It is a good and legitimate question, but one without a truly satisfying answer.

Here's the short answer: Forget the DOMAIN, it cannot help or hurt you any longer. Instead, bite the bullet and run ALTER statements to modify the CHECK constraints on each and every column you want changed.

For example...

ALTER TABLE office ALTER address_2 
   CHECK ( LENGTH ( TRIM ( COALESCE ( @col, '' ) ) ) > 10 );

Long answer...

In SQL Anywhere, domains are not true domains, they are merely a coding shorthand for creating new columns. In particular, you cannot ALTER a domain except to change its name and even that doesn't work too well. You cannot DROP a domain without dropping or altering all the columns that use it... and there is no "cascade" facility whatsoever.

So, there is nothing you can do to the DOMAIN to change the CHECK, either in the DOMAIN or in any of the referencing tables.

HOWEVER, domains are so feeble that you can override any of the domain properties when creating a table; here's an example from my book:

CREATE DOMAIN address AS VARCHAR ( 100 )
                         NOT NULL
                         DEFAULT ''
                         CHECK ( LENGTH ( TRIM ( @col ) ) > 0 );

CREATE TABLE office (
   office_code INTEGER PRIMARY KEY,
   address_1   address,
   address_2   address CHECK ( address_2 IS NOT NULL ),
   address_3   address NULL DEFAULT ( NULL ) );

Here's a test:

INSERT office VALUES ( 1, 'x', 'y', NULL );

SELECT * FROM office ORDER BY office_code;

office_code,address_1,address_2,address_3
1,'x','y',(NULL)

You can look at the result via sa_get_table_definition() which was given to us by The Great Bruce Hay:

SELECT sa_get_table_definition ( 'dba', 'office' );

CREATE TABLE "DBA"."office" (
    "office_code"                    int NOT NULL
   ,"address_1"                      "address" NOT NULL
   ,"address_2"                      "address" NOT NULL check(address_2 is not null)
   ,"address_3"                      "address" NULL DEFAULT (null)
   ,PRIMARY KEY ("office_code") 
)
;

That means there is nothing stopping you from using ALTER to change the CHECK on an actual columns:

ALTER TABLE office ALTER address_2 
   CHECK ( LENGTH ( TRIM ( COALESCE ( @col, '' ) ) ) > 10 );

SELECT sa_get_table_definition ( 'dba', 'office' );

CREATE TABLE "DBA"."office" (
    "office_code"                    int NOT NULL
   ,"address_1"                      "address" NOT NULL
   ,"address_2"                      "address" NOT NULL INLINE 100 PREFIX 8 check(LENGTH(TRIM(COALESCE(@col,''))) > 10)
   ,"address_3"                      "address" NULL DEFAULT (null)
   ,PRIMARY KEY ("office_code") 
)
;

Here's another test, to prove that the new CHECK is in force (don't ask me where the INLINE 100 PREFIX 8 came from):

INSERT office VALUES ( 2, 'x', 'y', NULL );

Could not execute statement.
Constraint 'ASA107' violated: Invalid value for column 'address_2' in table 'office'
SQLCODE=-209, ODBC 3 State="23000"
Line 1, column 1

And in conclusion...

Domains are pretty much useless, which explains why very few people use them, and those that do use them often wish they didn't bother.

You can't even give the CHECK a CONSTRAINT name...

CREATE DOMAIN non_empty_address AS VARCHAR ( 100 )
                                NOT NULL
                                DEFAULT ''
                                CONSTRAINT "The address is too short" CHECK ( LENGTH ( TRIM ( @col ) ) > 0 );

Could not execute statement.
Syntax error near 'CONSTRAINT' on line 4
SQLCODE=-131, ODBC 3 State="42000"

even though constraint names are helpful when debugging an error...

CREATE TABLE t ( c VARCHAR ( 100 )
                   NOT NULL
                   DEFAULT ''
                   CONSTRAINT "The address is too short" CHECK ( LENGTH ( TRIM ( @col ) ) > 0 ) );

INSERT t VALUES ( '' );

Could not execute statement.
Constraint 'The address is too short' violated: Invalid value for column 'c' in table 't'
SQLCODE=-209, ODBC 3 State="23000"