Bug 3569 - Add support for: ALTER TABLE [schema1.]tablename ADD CONSTRAINT [tbl_constr_name] CHECK (logical_expression)
Summary: Add support for: ALTER TABLE [schema1.]tablename ADD CONSTRAINT [tbl_constr_n...
Status: NEW
Alias: None
Product: SQL
Classification: Unclassified
Component: all (show other bugs)
Version: -- development
Hardware: x86_64 (amd64/em64t) Linux
: Normal enhancement
Assignee: SQL devs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2014-09-12 18:50 CEST by Martin van Dinther
Modified: 2020-05-25 09:17 CEST (History)
2 users (show)



Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Martin van Dinther cwiconfidential 2014-09-12 18:50:27 CEST
User-Agent:       Mozilla/5.0 (X11; Linux x86_64; rv:32.0) Gecko/20100101 Firefox/32.0
Build Identifier: 

SQL syntax ALTER TABLE [schema1.]tablename ADD CONSTRAINT [tbl_constr_name] CHECK (logical_expression) is currently not supported.

The creation of a CHECK constraint is supported as part of a CREATE TABLE. It should also be possible to add CHECK constraints after a table is created.

When the ADD CONSTRAINT .. CHECK () is implemented, please also consider to implement ALTER TABLE .. DROP CONSTRAINT CHECK_constraint_name.

Reproducible: Always

Steps to Reproduce:
1. Start MonetDB v11.17.21 (Jan2014-SP3)
2. Start mclient
3. Execute SQLs:
CREATE TABLE testcheck (nr1 int not null PRIMARY KEY, nr2 int not null);
ALTER TABLE testcheck ADD CONSTRAINT chk1 CHECK (nr1 > 10);
ALTER TABLE testcheck ADD CONSTRAINT chk2 CHECK (nr2 > nr1);
DROP TABLE testcheck;

CREATE TABLE testcheck (nr1 int not null PRIMARY KEY CHECK (nr1 > 10), nr2 int not null CONSTRAINT chk2 CHECK (nr2 > nr1) );
INSERT INTO testcheck VALUES (1, 2);
INSERT INTO testcheck VALUES (1, 2);
SELECT * FROM testcheck;
DROP TABLE testcheck;
Actual Results:  
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.17.21 (Jan2014-SP3), 'demo'
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>CREATE TABLE testcheck (nr1 int not null PRIMARY KEY, nr2 int not null);
operation successful (7.806ms)
sql>ALTER TABLE testcheck ADD CONSTRAINT chk1 CHECK (nr1 > 10);
syntax error, unexpected CHECK, expecting FOREIGN or PRIMARY or UNIQUE in: "alter table testcheck add constraint chk1 check"
sql>ALTER TABLE testcheck ADD CONSTRAINT chk2 CHECK (nr2 > nr1);
syntax error, unexpected CHECK, expecting FOREIGN or PRIMARY or UNIQUE in: "alter table testcheck add constraint chk2 check"
sql>DROP TABLE testcheck;
operation successful (2.359ms)

sql>CREATE TABLE testcheck (nr1 int not null PRIMARY KEY CHECK (nr1 > 10), nr2 int not null CONSTRAINT chk2 CHECK (nr2 > nr1) );
operation successful (16.522ms)
sql>INSERT INTO testcheck VALUES (1, 2);
1 affected rows (4.138ms)
sql>INSERT INTO testcheck VALUES (1, 2);
INSERT INTO: PRIMARY KEY constraint 'testcheck.testcheck_nr1_pkey' violated
sql>SELECT * FROM testcheck;
+------+------+
| nr1  | nr2  |
+======+======+
|    1 |    2 |
+------+------+
1 tuple (1.073ms)
sql>DROP TABLE testcheck;
operation successful (15.496ms)
sql>

Expected Results:  
The ALTER TABLE commands should be accepted and supported.
The INSERT command should not be accepted as it violates the CHECK constraints in the second CREATE TABLE commands (see also bug 3568)