Pular para o conteúdo principal

DROP CONSTRAINT clause

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Drops a PRIMARY KEY, FOREIGN KEY, or CHECK constraint from the relation.

Syntax​

DROP { PRIMARY KEY [ IF EXISTS ] [ RESTRICT | CASCADE ] |
       FOREIGN KEY [ IF EXISTS ] ( column [, ...] ) |
       CONSTRAINT [ IF EXISTS ] name [ RESTRICT | CASCADE ] }

Parameters​

  • PRIMARY KEY [ IF EXISTS ]

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above check marked yes Unity Catalog only

    Drops the primary key from the relation.

  • FOREIGN KEY [ IF EXISTS ] ( column [, …] )

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 11.3 LTS and above check marked yes Unity Catalog only

    Drops the foreign key identified by the ordered list of columns.

  • CONSTRAINT [ IF EXISTS ] name

    Drops the primary key, foreign key, or check constraint identified by name. Check constraints can only be dropped by name.

  • RESTRICT or CASCADE

    When you specify RESTRICT, and the primary key is referenced by any foreign key, the statement fails. When you specify CASCADE, dropping the primary key results in dropping any foreign keys referencing the relation. The default is RESTRICT.

  • IF EXISTS

    When you specify IF EXISTS, the statement is ignored if the relation has no matching constraint.

Examples​

SQL
> 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;
Última atualização em