英文:
How to remove REFERENCES from PostgreSQL table but save indexes for these columns
问题
我有一个包含对其他表的引用的PostgreSQL表格。我需要移除所有的引用，但保留它的列。而且困难之处在于这些列有索引。
需要将它变成如下的表格：
CREATE TABLE _read_status
(
id uuid NOT NULL CONSTRAINT _read_status_pkey PRIMARY KEY,
chat_id uuid NOT NULL,
user_id text,
company_id text,
CONSTRAINT uk_chat_id_user_id UNIQUE (chat_id, user_id),
CONSTRAINT uk_chat_id_company_id UNIQUE (chat_id, company_id)
);
CREATE INDEX IF NOT EXISTS _idx_chat_id_user_id ON _read_status (chat_id DESC, user_id);
CREATE INDEX IF NOT EXISTS _idx_chat_id_company_id ON _read_status (chat_id DESC, company_id);
英文:
I have a postgreql table with references to other tables. I need remove all references, but save its column. And the difficulty is that these columns have indexes
CREATE TABLE _read_status
(
id uuid NOT NULL CONSTRAINT _read_status_pkey PRIMARY KEY,
chat_id uuid NOT NULL CONSTRAINT _read_status_chat_fkey REFERENCES _chat (id),
user_id text CONSTRAINT _read_status_user_info_pkey REFERENCES _user_info (user_id),
company_id text,
CONSTRAINT uk_chat_id_user_id UNIQUE (chat_id, user_id),
CONSTRAINT uk_chat_id_company_id UNIQUE (chat_id, company_id)
);
CREATE INDEX IF NOT EXISTS _idx_chat_id_user_id ON _read_status (chat_id DESC, user_id);
CREATE INDEX IF NOT EXISTS _idx_chat_id_company_id ON _read_status (chat_id DESC, company_id);
I need to turn it into a table like this:
CREATE TABLE _read_status
(
id uuid NOT NULL CONSTRAINT _read_status_pkey PRIMARY KEY,
chat_id uuid NOT NULL,
user_id text,
company_id text,
CONSTRAINT uk_chat_id_user_id UNIQUE (chat_id, user_id),
CONSTRAINT uk_chat_id_company_id UNIQUE (chat_id, company_id)
);
CREATE INDEX IF NOT EXISTS _idx_chat_id_user_id ON _read_status (chat_id DESC, user_id);
CREATE INDEX IF NOT EXISTS _idx_chat_id_company_id ON _read_status (chat_id DESC, company_id);
答案1
得分: 3
可以尝试使用以下命令：
ALTER TABLE _read_status DROP CONSTRAINT _read_status_chat_fkey;
ALTER TABLE _read_status DROP CONSTRAINT _read_status_user_info_pkey;
英文:
You can try these commands:
ALTER TABLE _read_status DROP CONSTRAINT _read_status_chat_fkey;
ALTER TABLE _read_status DROP CONSTRAINT _read_status_user_info_pkey;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论