我刚开始阅读七周七个数据库,第二版,目前正在完成PostgreSQL
课。
创建新表时,我错误地将MATCH FULL
包含在外键约束中。
这是我用来创建表的命令:
CREATE TABLE venues (
venue_id SERIAL PRIMARY KEY,
name varchar(255),
street_address text,
type char(7) CHECK (type in ('public', 'private') ) DEFAULT 'public',
postal_code varchar(9),
country_code char(2),
FOREIGN KEY (country_code, postal_code)
REFERENCES cities (country_code, postal_code) MATCH FULL
);
我正在浏览 ALTER TABLE 文档,但我无法找出删除MACTH FULL
的正确语法。
>恐怕你不能ALTER
它(只有ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
)。您需要添加simple match
并删除旧的,例如(简化结构):
db=# create table p(i int primary key);
CREATE TABLE
db=# create table f(i int references p(i) match full);
CREATE TABLE
db=# d f
Table "public.f"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
Foreign-key constraints:
"f_i_fkey" FOREIGN KEY (i) REFERENCES p(i) MATCH FULL
您会看到名称和匹配策略,所以现在:
db=# alter table f add constraint ms FOREIGN KEY (i) REFERENCES p(i) MATCH simple;
ALTER TABLE
db=# alter table f drop constraint f_i_fkey;
ALTER TABLE
db=# d f
Table "public.f"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
Foreign-key constraints:
"ms" FOREIGN KEY (i) REFERENCES p(i)