I have a table with the structure
(content_md5 UUID, content TEXT), where I'd like to use
content_md5 (which is the value of
md5(content)) as the primary key, and use it as a foreign key in other tables.
This is a "static" table, where the content (some largish documents) would be referred to by their
md5 value for simplicity, and to prevent duplication in the table (which wouldn't be a given with a simple
content can be
NULL, which is different from an empty value to declare a non-existing content field in the referencing table.
NULL is not allowed in a primary key constraint, I'd like to have a way of having
md5(NULL) return all zeros instead of
-- setup CREATE TABLE example (content_md5 UUID PRIMARY KEY, content TEXT); CREATE TABLE test (id SERIAL PRIMARY KEY, tags TEXT, content_md5 UUID REFERENCES example(content_md5) ON DELETE RESTRICT); INSERT INTO example VALUES ('00000000000000000000000000000000'::uuid, NULL); -- usage INSERT INTO example VALUES (md5('some text')::uuid, 'some text'); INSERT INTO test (tags, content_md5) VALUES ('some content defining tags', md5('some text')::uuid); SELECT tags, content FROM test LEFT JOIN example USING (content_md5); -- QUESTION: Having an md5-like function to return zero-filled "md5"/uuid? INSERT INTO example VALUES (md5(NULL)::uuid, NULL); -- ignored, because already existing record INSERT INTO test (tags, content_md5) VALUES ('non-existing-document', md5(NULL)::uuid);
Is it possible to somehow cast the returned value to a zero-filled string, create a custom function based on
md5() which replaces
00000000000000000000000000000000, or some other way to achieve this result?
/edit: Or perhaps I don't need any
NULL values in this table, and can just set the referencing foreign key column to
NULL to achieve the same result?