Menu
▾
▴
#289 Patch: Add NOT NULL for pk in createSQL
When tables with are created with primary keys in CMP,
currently no column modifiers are used to create the
table. The following is sql that is used to
create "table1" which has a primary key of "id"..
CREATE TABLE table1 (id VARCHAR(255) BINARY, data2
BLOB, data2 BLOB, CONSTRAINT pk_table1 PRIMARY KEY
(id))
The current version of MySQL blows up on this because
the column "id" should not be null because it is
primary key column. The attached patch checks the
columns to see if they are a primary key column and
adds the "not null" modifier if they are.
CREATE TABLE table1 (id VARCHAR(255) BINARY NOT NULL,
data2 BLOB, data2 BLOB, CONSTRAINT pk_table1 PRIMARY
KEY (id))
I believe most db supports "not null" so this should
work universally without having to worry about DB
specific syntax and I don't think that EJBs allow null
primary keys so that shouldn't be a problem.
SQLUtils Patch
Logged In: YES
user_id=240275
To handle pk constraints on relation tables, I added a flag
so JDBCStartCommand.getRelationCreateTableSQL() can force
not null on all fields in
SQLUtil.getCreateTableColumnsClause(fields, all) { ... if
(all || field.isPrimaryKeyMember()) ...}
In getRelationCreateTableSQL(),
boolean hasPKC = cmrField.getRelationMetadata
().hasPrimaryKeyConstraint();
and invoke
SQLUtil.getCTCC(fields, hasPKC);
getCTCC(fields) just calls getCTCC(fields, false);
The equivalent of <nullable> would be nice.
Logged In: YES
user_id=251431
This patch is being rejected because it only fixes a
problem in the mysql database, and breaks most other
databases.
It is perfecly valid in most databases to have a null pk.
This is even more important when you have a pk field which
is composed of several columns. In that case is a user may
want some of the columns to be null some of the time.
You can add not null to your sql-type, if you want this for
your application.
I would like to support a nullable (TRUE or FALSE) element
in cmp-fields and a pk-not-null flag for type-mappings, but
this is not a high priority. If you would like to work on
this patch, contact me.
Logged In: YES
user_id=60525
Not to nitpick, however sql92 > entry level requires that
all pk fields be not null all the time. Unique index fields
can optionally be not null or null. I think previous sql
standards also required this, but required it to be stated
explicitly.
sql92 standard syntax implicitly appends the not null
constraint to all pk fields. There are several dbs that
have archaic syntax that require you to declare not null
explicitly (firebird/interbase and mysql come to mind)
however ANY relational database with any hopes of logical
consistency needs to require all pk fields not null.
I do not know if (but think) that some databases object if
you explicitly say not null on pks, even though they are
adding the constraint implicitly.
Logged In: YES
user_id=251431
I'm wrong. I looked it up in my SQL in a Nutshell book,
and it is required in SQL 99 that all pk columns be not
null, which should be implied. I'll apply this patch (or
something simmilar).
Logged In: YES
user_id=251431
I added an optional not-null to cmp-field and property.
This option is on by default for pk fields and for primitive
type.
I could not use the supplied patch because it assumed that a
field mapps to only one column.