---
# System prepended metadata

title: '#2389 Database Schema review, FKs, null/not null columns etc...'

---

# #2389 Database Schema review, FKs, null/not null columns etc...
```
USE alkemio;
SELECT CONCAT('FK_', SUBSTRING(MD5(RAND()), 1, 27));
-- Change a few columns to NOT NULLABLE 
ALTER TABLE challenge CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE hub CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE hub CHANGE visibility visibility VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE opportunity CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE location CHANGE city city VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE location CHANGE country country VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE activity CHANGE triggeredBy triggeredBy CHAR(36) NOT NULL;
ALTER TABLE activity CHANGE resourceId resourceId CHAR(36) NOT NULL;
ALTER TABLE activity CHANGE collaborationId collaborationId CHAR(36) NOT NULL;

-- Change a few column types:
ALTER TABLE aspect CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE project CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE canvas CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE organization CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE user CHANGE nameID nameID VARCHAR(36) NOT NULL;
ALTER TABLE aspect CHANGE displayName displayName VARCHAR(255) NOT NULL;
ALTER TABLE aspect CHANGE type type varchar(255) NOT NULL;
ALTER TABLE aspect_template CHANGE type type text NOT NULL;
ALTER TABLE preference_definition CHANGE displayName displayName varchar(128) NOT NULL;
ALTER TABLE preference_definition CHANGE definitionSet definitionSet varchar(128) NOT NULL;

-- Moderate danger:
-- Add some missing FKs
-- PreferenceSet
ALTER TABLE challenge DROP CONSTRAINT FK_c07b5b4c96fa89cb80215827668;

ALTER TABLE challenge ADD CONSTRAINT FK_c890de5a08d363719a41703a638 FOREIGN KEY (preferenceSetId) REFERENCES preference_set(id) ON DELETE SET NULL;
ALTER TABLE hub ADD CONSTRAINT FK_6bf7adf4308991457fdb04624e2 FOREIGN KEY (preferenceSetId) REFERENCES preference_set(id) ON DELETE SET NULL;
ALTER TABLE organization ADD CONSTRAINT FK_c07b5b4c96fa89cb80215827668 FOREIGN KEY (preferenceSetId) REFERENCES preference_set(id) ON DELETE SET NULL;
ALTER TABLE user ADD CONSTRAINT FK_5ea996d22fbd9d522a59a39b74e FOREIGN KEY (preferenceSetId) REFERENCES preference_set(id) ON DELETE SET NULL;
ALTER TABLE canvas ADD CONSTRAINT FK_c7b34f838919f526f829295cf86 FOREIGN KEY (previewId) REFERENCES visual(id) ON DELETE SET NULL;

-- AuthorizationId
ALTER TABLE aspect ADD CONSTRAINT FK_00a8c330495ef844bfc6975ec89 FOREIGN KEY (authorizationId) REFERENCES authorization_policy(id) ON DELETE SET NULL;


-- Dangerous:
-- Change length of a few column types: 
ALTER TABLE canvas CHANGE displayName displayName VARCHAR(255) NOT NULL;
ALTER TABLE user CHANGE serviceProfile serviceProfile BOOLEAN NOT NULL;

-- Better not do this:
-- ALTER TABLE callout CHANGE nameID nameID VARCHAR(36) NOT NULL;
-- ALTER TABLE activity CHANGE description description VARCHAR(128) NOT NULL;
-- ALTER TABLE activity CHANGE type type VARCHAR(16) NOT NULL;



```

Inconsistency: striked through a comment about changing id columns to varchar - most of the id columns are are varchar, but I think they all should be changed to binary or to char anyway


- [x] Agent
- [x] Credential
- [x] Collaboration
- [x] Challenge
    * ~~<Check manually> Challenge childChallenges Missing FK on table challenge column ChallengeId to challenge.id~~ (ParentChallengeId is ok)
    * Missing FK on column preferenceSetId to preference_set.id
- [x] Hub
    * ~~<Check manually> Hub challenges Missing FK on table challenge column HubId to hub.id~~ (ParentHubId is ok)
    * Missing FK on column preferenceSetId to preference_set.id
- [x] Agreement
- [x] CardProfile
    * ~~Column id is char(36) but is expected to be: varchar(36)~~ 
- [ ] Aspect
    * Column nameID is char(36) but is expected to be: varchar(36)
    * Column displayName is char(255) but is expected to be: varchar(255)
    * ~~Entity:Aspect table:aspect property:type column:type Expected type:text but has type:char~~ I think it should be just varchar and remove this param from the decorator **CONFIRM!!**
    * Missing FK on column authorizationId to authorization_policy.id
- [ ] Callout
    * ~~NameID column doesn't match in callout [
      { prop: 'character_maximum_length', value: 255, expected: 36 },
      {
        prop: 'column_type',
        value: 'varchar(255)',
        expected: 'varchar(36)'
      }
    ]~~ **better don't change this**
- [x] Opportunity
    * NameID column doesn't match in opportunity [
      { prop: 'column_default', value: 'NULL', expected: null },
      { prop: 'is_nullable', value: 'YES', expected: 'NO' }
    ]
- [x] Project
    * NameID column doesn't match in project [
      { prop: 'column_default', value: 'NULL', expected: null },
      { prop: 'is_nullable', value: 'YES', expected: 'NO' }
    ]
- [x] Relation
- [x] AuthorizationPolicy
- [x] CanvasCheckout
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Canvas
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
    displayName column doesn't match in canvas [
      { prop: 'data_type', value: 'text', expected: 'varchar' },
      { prop: 'character_maximum_length', value: 65535, expected: 255 },
      { prop: 'column_type', value: 'text', expected: 'varchar(255)' }
    ]
    * Column nameID is char(36) but is expected to be: varchar(36)
    * Missing FK on column previewId to visual.id
- [x] Lifecycle
- [ ] Location
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
    * **CONFIRM THE NEXT TWO ONES!!**
    * Entity:Location table:location property:city column:city Expected Nullable:true but has:NO
    *  Entity:Location table:location property:country column:country Expected Nullable:true but has:NO
- [x] NVP
- [x] Preference
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
- [ ] PreferenceDefinition
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
    * displayName column doesn't match in preference_definition [
      { prop: 'character_maximum_length', value: 128, expected: 255 },
      {
        prop: 'column_type',
        value: 'varchar(128)',
        expected: 'varchar(255)'
      }
    ] - **Wrong in the entity**
    * Entity:PreferenceDefinition table:preference_definition property:definitionSet column:definitionSet Expected Length:128 but has:32
    * prop preference should be of type array if the relation is OneToMany **CONFIRM, CODE IN THE ENTITY IS WRONG??**
- [x] PreferenceSet
    ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Reference
- [x] Tagset
- [x] Visual
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Comments
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Communication
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
- [ ] Discussion
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
    * Entity:Discussion table:discussion property:createdBy column:createdBy Expected type:varchar but has type:char **CONFIRM: This entity has 'varchar' type in typeorm but I think it should be char, and it should have createdByUserId or some Id suffix if it's a char(36) or a varchar(36)**
- [x] Updates
    ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Application
    ~~Unknown related entities Application questions~~ Many to Many checker not well implemented
    ~~<Check manually> ManyToMany relation {
      entityName: 'Application',
      manyToManyDecorator: {
        name: 'ManyToMany',
        documentation: '',
        type: 'any',
        parameters: [ [Object], [Object], [Object], [Object] ],
        rawValue: 'ManyToMany(() => NVP, nvp => nvp.id, { eager: true, cascade: true })'
      }
    }~~ Checked, all good
- [x] CommunityPolicy
- [x] Community
    ~~Unknown related entities Community applications IApplication is not a valid entity~~ OK Checked manually
- [x] OrganizationVerification
    ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Profile
- [x] Organization
    * NameID column doesn't match in organization [
      { prop: 'column_default', value: 'NULL', expected: null },
      { prop: 'is_nullable', value: 'YES', expected: 'NO' }
    ]
    * Missing FK on column preferenceSetId to preference_set.id
- [x] User
    * NameID column doesn't match in user [
      { prop: 'column_default', value: 'NULL', expected: null },
      { prop: 'is_nullable', value: 'YES', expected: 'NO' }
    ]
    * Column type mismatch: serviceProfile (boolean) Column serviceProfile (tinyint(4))
    * Missing FK on column preferenceSetId to preference_set.id
- [x] UserGroup
- [x] Actor
- [x] ActorGroup
- [x] Context
- [x] EcosystemModel
- [x] TemplateInfo
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] AspectTemplate
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
    * templatesSet OnDelete policies don't match NO ACTION CASCADE **CONFIRM: This is wrong in the typescript code**
    * Entity:AspectTemplate table:aspect_template property:type column:type Expected type:text but has type:char
- [x] CanvasTemplate
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
    * templatesSet OnDelete policies don't match NO ACTION CASCADE **CONFIRM: This is wrong in the typescript code ??**
- [x] LifecycleTemplate
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
    * templatesSet OnDelete policies don't match NO ACTION CASCADE **CONFIRM: This is wrong in the typescript code ??**
- [x] TemplatesSet
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] Library
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
- [x] InnovationPack
    * ~~Column id is char(36) but is expected to be: varchar(36)~~
- [ ] Activity
      * collaborationID needs to be renamed to collaborationId **Code is wrong in typescript**
      * Entity:Activity table:activity property:triggeredBy column:triggeredBy Expected Nullable:false but has:YES
      * Entity:Activity table:activity property:resourceID column:resourceID Expected Nullable:false but has:YES
      * Entity:Activity table:activity property:collaborationID column:collaborationID Expected Nullable:false but has:YES
      * Entity:Activity table:activity property:description column:description Expected Length:128 but has:255
      Entity:Activity table:activity property:type column:type Expected Length:16 but has:128
    
    
    
**Done checking**
query-result-cache not used by typeorm?
application_questions not used by typeorm?
migrations_typeorm not used by typeorm?
**Done**

