Insert null foreign key fields in postgres #2130

New Issue

xorduna · 2022-04-05T20:30:29Z

xorduna commented

2022-04-05 20:30:29 +00:00

Hi,

I am new to XORM, and for the moment it looks great. I am having some problems (maybe because of my inexperience in go and xorm) with relationships between tables.

This is my model definition:

// Asset represents the structure we need for moving data.
type Asset struct {
	ID           string    `xorm:"pk not null 'id'" json:"id"`
	AccountID    string    `xorm:"account_id" json:"account_id"`
	DeviceID     *string   `xorm:"device_id  default null"  json:"device_id"`
	CreatedAt    time.Time `xorm:"created" json:"-"`
	UpdatedAt    time.Time `xorm:"updated" json:"-"`
}

// Device represents the structure we need for moving data.
type Device struct {
	ID           string  `xorm:"pk not null 'id'" json:"id"`
	Name         string  `xorm:"name" json:"name"`
	AccountID    string  `xorm:"account_id" json:"account_id"`
	AssetID      *string `xorm:"asset_id default null" json:"asset_id"`
	CreatedAt time.Time              `xorm:"created" json:"-"`
	UpdatedAt time.Time              `xorm:"updated" json:"-"`
}

Initially DeviceID was defined as string, but then it was impossible to insert an Asset with device_id=NULL, it was trying to insert all the time an empty string.

This is the database definition:

CREATE TABLE assets
(
    id            UUID,
    name          TEXT,
    asset_type    TEXT,
    serial_number TEXT,
    typology      TEXT,
    device_id     UUID DEFAULT NULL,
    account_id    UUID,

    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    CONSTRAINT fk_accounts
        FOREIGN KEY (account_id)
            REFERENCES accounts(id) ON DELETE CASCADE,
    CONSTRAINT fk_devices
        FOREIGN KEY (device_id)
            REFERENCES devices(id)
);

This solution works for now, but I am not very confortable with it.

Is there any alternative?

Also, is there any way to define a Device in Asset so I can access Device with an statement like: asset.device.ID?

I have another project where I was using XORM, and I was able to do it, but I was using autoincremented ints. Is maybe the usage of int for primary id recommended?

Thank you very much!

Xavi

Hi, I am new to XORM, and for the moment it looks great. I am having some problems (maybe because of my inexperience in go and xorm) with relationships between tables. This is my model definition: ``` // Asset represents the structure we need for moving data. type Asset struct { ID string `xorm:"pk not null 'id'" json:"id"` AccountID string `xorm:"account_id" json:"account_id"` DeviceID *string `xorm:"device_id default null" json:"device_id"` CreatedAt time.Time `xorm:"created" json:"-"` UpdatedAt time.Time `xorm:"updated" json:"-"` } // Device represents the structure we need for moving data. type Device struct { ID string `xorm:"pk not null 'id'" json:"id"` Name string `xorm:"name" json:"name"` AccountID string `xorm:"account_id" json:"account_id"` AssetID *string `xorm:"asset_id default null" json:"asset_id"` CreatedAt time.Time `xorm:"created" json:"-"` UpdatedAt time.Time `xorm:"updated" json:"-"` } ``` Initially `DeviceID` was defined as `string`, but then it was impossible to insert an `Asset` with `device_id=NULL`, it was trying to insert all the time an empty string. This is the database definition: ``` CREATE TABLE assets ( id UUID, name TEXT, asset_type TEXT, serial_number TEXT, typology TEXT, device_id UUID DEFAULT NULL, account_id UUID, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), CONSTRAINT fk_accounts FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE, CONSTRAINT fk_devices FOREIGN KEY (device_id) REFERENCES devices(id) ); ``` This solution works for now, but I am not very confortable with it. Is there any alternative? Also, is there any way to define a `Device` in Asset so I can access Device with an statement like: `asset.device.ID`? I have another project where I was using XORM, and I was able to do it, but I was using autoincremented ints. Is maybe the usage of int for primary id recommended? Thank you very much! Xavi

Sign in to join this conversation.

No Milestone

No Assignees

1 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#2130