Syntax Not Working: "ALTER TABLE <TableName> DROP <ColumnName> CHECK;"

Database
Win
Teradata Employee

Syntax Not Working: "ALTER TABLE <TableName> DROP <ColumnName> CHECK;"

The following SQL statement using this syntax failed after query execution:

ALTER TABLE CHILD_TABLE DROP COLUMN1 CHECK;

Is there something missing in this statement? Please note that the following syntax was taken from Teradata Documentation, "SQL Data Definition Language - Detailed Topics (Release 14.0); page 68".

ALTER TABLE table_name DROP column_name CHECK;

Below is the error message received:

ALTER TABLE Failed. 3913: The specified check does not exist.

Background Information:

The CHILD_TABLE was created using the following definition:

CT CHILD_TABLE
(
COLUMN1 SMALLINT NOT NULL
,COLUMN2 CHAR(1) NOT NULL
,COLUMN3 CHAR(1) NOT NULL
,ChildColumn CHAR(1) NOT NULL
);

The CHECK constraint was created using the following SQL statement:

ALTER TABLE CHILD_TABLE ADD CHECK(COLUMN1 > 0);

New Table Definition:

SHOW TABLE CHILD_TABLE;

CREATE SET TABLE CHILD_TABLE ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
COLUMN1 SMALLINT NOT NULL,
COLUMN2 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
COLUMN3 CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ChildColumn CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
CHECK ( COLUMN1 > 0 ))
PRIMARY INDEX ( COLUMN1 );

The following query is then submitted but returned an error as explained above:

ALTER TABLE CHILD_TABLE DROP COLUMN1 CHECK;

Thank you in advance for any answers or information that will be given.

4 REPLIES
Win
Teradata Employee

Re: Syntax Not Working: "ALTER TABLE <TableName> DROP <ColumnName> CHECK;"

For the DDL statement after "New Table Definition", please ignore the following:

<em><strong>....</strong></em>)

The correct statement should just be: CHECK ( COLUMN1 >  0  )

Thank you.

Junior Supporter

Re: Syntax Not Working: "ALTER TABLE <TableName> DROP <ColumnName> CHECK;"

Hi.

For unnamed check constraints you can only drop them ALL with:

ALTER TABLE... DROP CHECK;

HTH.

Cheers.

Carlos.

Teradata Employee

Re: Syntax Not Working: "ALTER TABLE <TableName> DROP <ColumnName> CHECK;"

Note that that ALTER added a table-level constraint. For a column-level constraint:

 

 

ALTER TABLE CHILD_TABLE ADD COLUMN1 CHECK(COLUMN1 > 0);


Win
Teradata Employee

Re: Syntax Not Working: "ALTER TABLE <TableName> DROP <ColumnName> CHECK;"

Hi Carlos and Fred,

Thank you for your responses. They have answered my question.

Best regards,

Win