code: 101, message: Unexpected packet Query received from client when updating many-to-many association about clickhouse HOT 2 OPEN

Further research indicates to me that Clickhouse does not even support constraints with foreign keys. It does not seem to be supported in their SQL spec.

See:
https://clickhouse.com/docs/en/sql-reference/statements/alter/constraint
https://stackoverflow.com/questions/66354832/how-can-i-use-functionality-of-primary-key-and-foreign-key-in-clickhouse

Take for instance this table:

type User struct {
	HModel
	Email     string  `gorm:"index"`
	Roles     []*Role `gorm:"many2many:user_roles;"`
	FirstName string
	LastName  string
	Role      string
}

...

type HModel struct {
	ID        string `gorm:"primarykey"`
	CreatedAt time.Time
	UpdatedAt time.Time
	// DeletedAt DeletedAt `gorm:"index"`
}

When doing a err = db.AutoMigrate(&User{}, &Role{}) the driver will attempt this:

ALTER TABLE `user_roles` ADD CONSTRAINT `fk_user_roles_role` 
FOREIGN KEY (`role_id`,`role_type`) REFERENCES `roles`(`id`,`type`)

But, at least based on my very limited (a week worth of messing with Clickhouse) this SQL statement is just wrong.

For instance running it in clickhouse-client results in:

Syntax error: failed at position 63 ('FOREIGN'):

 ALTER TABLE `user_roles` ADD CONSTRAINT `fk_user_roles_role` FOREIGN KEY (`role_id`,`role_type`) REFERENCES `roles`(`id`,`type`)

Expected one of: CHECK, ASSUME

Maybe someone could shed light on this.

But - as far as i can tell - this driver is just basically broken.

from clickhouse.

I think the only reasonable way to do many-to-many in clickhouse would be to simply use the ReplacingMergeTree engine using TUPLE() as the ORDER BY

Anyone? thoughts?

Or please tell me I am missing something big here.

from clickhouse.