Db questions

(David Schoen) #1

I noticed that on sq_ast_lnk there is a uniqueness constraint over majorid, minorid, link_type and value. That would make perfect sense except that in postgres (maybe other sqls, I haven’t tried/checked) it’s not possible to be unique with a null value.


I probably haven’t explained that well so here’s an example, with the current db setup

    INSERT into sq_ast_lnk (linkid,majorid,minorid,link_type,value,sort_order,is_dependant,is_exclusive,updated,updated_user

d)

VALUES (NEXTVAL(‘sq_ast_lnk_seq’),‘20007’,‘20008’,‘2’,’’,‘1’,‘1’,‘1’,‘2008-01-31 11:47:08’,‘12’);

INSERT into sq_ast_lnk (linkid,majorid,minorid,link_type,value,sort_order,is_dependant,is_exclusive,updated,updated_user

d)

VALUES (NEXTVAL(‘sq_ast_lnk_seq’),‘20007’,‘20008’,‘2’,’’,‘1’,‘1’,‘1’,‘2008-01-31 11:47:08’,‘12’);
will complain about the uniqueness constraint where as 
    INSERT into sq_ast_lnk (linkid,majorid,minorid,link_type,value,sort_order,is_dependant,is_exclusive,updated,updated_user

d)

VALUES (NEXTVAL(‘sq_ast_lnk_seq’),‘20007’,‘20008’,‘2’,null,‘1’,‘1’,‘1’,‘2008-01-31 11:47:08’,‘12’);

INSERT into sq_ast_lnk (linkid,majorid,minorid,link_type,value,sort_order,is_dependant,is_exclusive,updated,updated_user

d)

VALUES (NEXTVAL(‘sq_ast_lnk_seq’),‘20007’,‘20008’,‘2’,null,‘1’,‘1’,‘1’,‘2008-01-31 11:47:08’,‘12’);
most of the time this probably isn’t that big a deal, but it is possible to have 1 div existing multiple times under a bodycopy (although probably only by hacking at the database directly, which would be bad wouldn’t it…).



I was just wondering if you guys might turn the nullable option off for value in the table in future versions?



We’re also wondering here why asset ids are all stored in varchars, surely it’s quicker to store data that only ever seems to be integer in some form of int?



Just wondering…

Cheers,

Dave.
Manually adding links in the Postgres database
(Greg Sherwood) #2

We store "shadow assetids" for LDAP users. This id is a long string that uses the unique DN from LDAP for the user or group. To stop the DB having to cast during joins, all asset ID columns are varchar, even if they don't currently store shadow assetids (like sq_ast).

(Greg Sherwood) #3

We could certainly remove it, but I don't think it's too much of a problem. Thanks for pointing it out. I'll add it to the todo list for a future version.

(Basil Shkara) #4

Oracle treats empty strings as NULL so if a 'NOT NULL' constraint was added to the 'value' column, Oracle would not be able to cope, even though PostgreSQL would.


This is likely the reason why this constraint was never added for this column.