#221 NOT NULL and CHECK constraints

[Doug Lawry]

Rob Manning advised me to post this here. We are working on an Informix plugin, and he says this request is beyond the scope of the existing framework and therefore needs to be a new feature request.

In CREATE TABLE statements generated by right-clicking on a table and choosing Scripts / Create Table Script, PRIMARY/FOREIGN KEY constraints are fine but NOT NULL and CHECK constraints are missing. As you have NULLABLE in the Objects / Columns tab, it should be possible to append NOT NULL as necessary to each column definition in your CREATE TABLE statement. I have sent Rob SQL code to generate CHECK CONSTRAINT code for Informix databases.

Rob says:

"Currently the SQLScripts plugin (which provides the create table script feature among others) doesn't use the dialect framework to generate SQL. It relies on straight JDBC to generate standard SQL. So there are limits to how close it can get to the native SQL supported by any particular database."

[Rob Manning]

Logged In: YES
user_id=1287991
Originator: NO

While the "NOT NULL" qualifier seems to be suppported universally in create table column defintions, some DBs (Sybase) assume it and require "NULL" otherwise. I'm not sure at the moment that "NULL" is acceptable for column definitions in create table scripts for all databases - I have to confirm this. I'll also need to look into how different databases support check constraints. My intuition tells me that there is more variation among databases in this area. If there is no universally accepted syntax for both of these items (nullable and check constraints), the best option would be to use the dialect framework which can provide SQL which is specific to the database for that session.

Rob

[Doug Lawry]

Logged In: YES
user_id=1649069
Originator: YES

SQL Server (and I'm told Oracle) allow both "NULL" (the default) and "NOT NULL", whereas Informix (all versions) unfortunately rejects "NULL".

Check constraints are as follows (I'm not sure about Oracle):

SQL Server:
ALTER TABLE table ADD CONSTRAINT name CHECK (condition)
Informix:
ALTER TABLE table ADD CONSTRAINT CHECK (condition) CONSTRAINT name