-- constraints

ALTER TABLE USERS
ADD CONSTRAINT username_unique UNIQUE (USERNAME);


-- defaults

-- oracle can not use user defined functions (gen_uuid()) as default values :c

ALTER TABLE USERS
MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
, '\1-\2-\3-\4-\5');

ALTER TABLE BIDS
MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
, '\1-\2-\3-\4-\5');

ALTER TABLE CHARGES
MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
, '\1-\2-\3-\4-\5');

ALTER TABLE ITEMS
MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
, '\1-\2-\3-\4-\5');

ALTER TABLE TRANSACTIONS
MODIFY UUID DEFAULT regexp_replace(rawtohex(sys_guid())
, '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})'
, '\1-\2-\3-\4-\5');

COMMIT;
