WSO2 products are supporting different types of databases by design. When considering a product like WSO2 Identity Server, you can use a database like Oracle, Microsoft SQL Server, MySQL, IBM DB2, PostgreSQL etc. according to your requirements. You can even use a combination of databases (i.e as the PRIMARY userstore you may use Oracle and as a Seconday userstore you may use MS SQL Server for storing users).
However when you are writing code that deals with databases, it is really important that your code supports whatever the underlying database used. In this post I will show when not to use NOT NULL constraint.
Here I’m creating a simple table with the name PROJECTS that has three columns ID, NAME and LOCATION. ID is the PRIMARY KEY of the table which cannot be NULL at any occasion. Additionally I define NAME and LOCATION columns also with NOT NULL constraint. Then I insert one row that has values for all the three columns.
CREATE TABLE PROJECTS
(
ID INT PRIMARY KEY,
NAME VARCHAR(30) NOT NULL,
LOCATION VARCHAR(20) NOT NULL
);
INSERT INTO PROJECTS(ID, NAME, LOCATION) VALUES (1, 'WSO2 IDENTITY SERVER', 'SRI LANKA');
|
I can see the record is successfully inserted into the table.
Now I want to insert another record where I know the values for the ID and NAME columns. Assume that the LOCATION of the project is not known.
INSERT INTO PROJECTS(ID, NAME) VALUES (2, 'WSO2 ESB');
|
In Oracle database it gives the following error.
SQL Error: ORA-01400: cannot insert NULL into ("ORCL"."PROJECTS"."LOCATION")
01400. 00000 - "cannot insert NULL into (%s)"
*Cause:
*Action:
|
Now for the LOCATION column I try to insert an empty string in the Oracle database.
INSERT INTO PROJECTS(ID, NAME, LOCATION) VALUES (2, 'WSO2 ESB', '');
|
But Oracle gives the following error.
SQL Error: ORA-01400: cannot insert NULL into ("IDHWPROD"."PROJECTS"."LOCATION")
01400. 00000 - "cannot insert NULL into (%s)"
*Cause:
*Action:
|
This is because Oracle database treats an empty string as a NULL value [1]. Since we have a NOT NULL constraint defined on the LOCATION column, in Oracle database we cannot insert a record unless we insert a dummy value to the column with the NOT NULL constraint. This dummy value can be a string that has a space (i.e ‘ ‘).
If we try the same with MySQL it works perfectly where MySQL does not treat empty strings as NULL.
Therefore when defining a table, you have to use the NOT NULL constraint only if it is really needed. If you have a column with NOT NULL constraint, and if you have to insert a record to the table where you do not have a value for that column, you will face this problem if you have the Oracle database. A simple solution would be to send a string with the value of a space but then you have to make sure that you do not trim the string before inserting to the table.
WSO2 Identity Server 5.0.0 has a similar issue (reported in [2]) with IDN_IDENTITY_USER_DATA table where the DATA_VALUE column has the NOT NULL constraint defined. With this, we cannot insert a record to the table if we do not know the value for this column when using an Oracle database. From the code it inserts a record to the table, the value is trimmed to remove unnecessary white spaces from the beginning and the end of the string. Therefore we simply cannot insert a space as a dummy value which will ultimately be converted to an empty string and to a NULL value at the database level of Oracle.
CREATE TABLE IDN_IDENTITY_USER_DATA
(
TENANT_ID INTEGER DEFAULT -1234,
USER_NAME VARCHAR(255) NOT NULL,
DATA_KEY VARCHAR(255) NOT NULL,
DATA_VALUE VARCHAR(255) NOT NULL,
PRIMARY KEY (TENANT_ID, USER_NAME, DATA_KEY)
)
|
If you have an Identity Server running where the tables in the database are already created, you have no option other than dropping the NOT NULL constraint from the table for the DATA_VALUE column as below.
ALTER TABLE IDN_IDENTITY_USER_DATA MODIFY (DATA_VALUE NULL);
|
Therefore from the next release of the Identity Server IDN_IDENTITY_USER_DATA table’s DATA_VALUE column will not have the NOT NULL constraint for supporting all the databases including Oracle.
References
Tharindu Edirisinghe
Identity Server Team
WSO2
No comments:
Post a Comment