适用于: Databricks SQL Databricks Runtime
从关系中删除一个PRIMARY KEY
、FOREIGN KEY
或CHECK
的约束。
DROP { PRIMARY KEY [ IF EXISTS ] [ RESTRICT | CASCADE ] |
FOREIGN KEY [ IF EXISTS ] ( column [, ...] ) |
CONSTRAINT [ IF EXISTS ] name [ RESTRICT | CASCADE ] }
主键 [如果存在]
适用于:
Databricks SQL
Databricks Runtime 11.3 LTS 及更高版本
仅 Unity Catalog
从关系中删除主键。
FOREIGN KEY [ IF EXISTS ] ( column [, …] )
适用于:
Databricks SQL
Databricks Runtime 11.3 LTS 及更高版本
仅 Unity Catalog
删除由列的有序列表标识的外键。
CONSTRAINT [ IF EXISTS ] name
删除由名称标识的主键、外键或 CHECK 约束。 CHECK 约束只能按名称删除。
RESTRICT 或 CASCADE
如果指定
RESTRICT
,并且主键由任何外键引用,则语句将失败。 指定CASCADE
时,删除主键会导致删除引用关系的任何外键。 默认为RESTRICT
。IF EXISTS
指定
IF EXISTS
时,如果关系没有匹配约束,则忽略该语句。
> CREATE TABLE persons(first_name STRING NOT NULL, last_name STRING NOT NULL, nickname STRING);
> ALTER TABLE persons ADD CONSTRAINT persons_pk PRIMARY KEY(first_name, last_name);
> CREATE TABLE pets(name STRING, owner_first_name STRING, owner_last_name STRING);
> ALTER TABLE pets ADD CONSTRAINT pets_persons_fk FOREIGN KEY (owner_first_name, owner_last_name) REFERENCES persons;
> ALTER TABLE pets ADD CONSTRAINT pets_name_not_cute_chk CHECK (length(name) < 20);
-- Drop the check constraint by name
> ALTER TABLE pets DROP CONSTRAINT pets_name_not_cute_chk;
-- Attempt to drop the primary key of persons by name
> ALTER TABLE persons DROP CONSTRAINT persons_pk RESTRICT;
Error: A foreign key `pets_persons_fk` depends on the primary key
-- Drop the foreign key from pets by listing the columns
> ALTER TABLE pets DROP FOREIGN KEY IF EXISTS (owner_first_name, owner_last_name);
-- Drop the primary key of persons
> ALTER TABLE persons DROP PRIMARY KEY CASCADE;