如何从表中删除而不必删除约束 [英] How to delete from a Table without having to drop a constraint

问题描述

我有一个问题，即即时消息需要删除信息而不删除或暂停约束.如果允许放开约束，问题很简单.但是，我可以将值从NULL更改为NOT NULL.

I have a question in which im required to delete information without dropping or suspending constraints. The question, if allowed to drop constraints is simple. However i am allowed to change Values from NULL to NOT NULL.

DELETE FROM SUPPLIER
WHERE COUNTRY = 'USA';

但是，另一个表PRODUCT是SUPPLIER中列SUPPLIER_NAME的外键.我不允许放弃此约束.

however another table PRODUCT is the foreign key of a column SUPPLIER_NAME within SUPPLIER. I am not allowed to drop this Constraint.

我曾尝试将外键设置为null，但不起作用.

Ive tried setting the Foreign key to null but it doesnt Work.

不允许编辑我限制或暂停约束

EDIT im not allowed to restrict or suspend constraints

这是完整的问题

从数据库中删除有关位于美国的所有供应商的信息.有关位于美国的供应商提供的所有产品的信息必须保留在数据库中.不允许您删除和/或暂停任何参照完整性约束，并且您必须修改NULL/NOT NULL一致性约束之一."

"Delete from the database information about all suppliers located in USA. Information about all products supplied by the suppliers located in USA must remain in the database. You are not allowed to drop and/or to suspend any referential integrity constraints and you must modify one of NULL/NOT NULL consistency constraints. "

正在使用的表

CREATE TABLE PRODUCT
(
    PRODUCT_NAME    VARCHAR(40)     NOT NULL,
    SUPPLIER_NAME   VARCHAR(40)     NOT NULL,
    CATEGORY_NAME   VARCHAR(30) NOT NULL,
    QUANTITY_PER_UNIT   VARCHAR(20),
    UNIT_PRICE      DECIMAL(10,2)   NOT NULL    DEFAULT 0,
    UNITS_IN_STOCK  DECIMAL(9)  NOT NULL    DEFAULT 0,
    UNITS_ON_ORDER  DECIMAL(9)  NOT NULL    DEFAULT 0, 
    REORDER_LEVEL   DECIMAL(9)  NOT NULL    DEFAULT 0,
    DISCONTINUED    CHAR(1)     NOT NULL    DEFAULT 'N',
    CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_NAME),
    CONSTRAINT FK_CATEGORY_NAME FOREIGN KEY (CATEGORY_NAME) REFERENCES CATEGORY(CATEGORY_NAME),
    CONSTRAINT FK_SUPPLIER_NAME FOREIGN KEY (SUPPLIER_NAME) REFERENCES SUPPLIER(COMPANY_NAME),
    CONSTRAINT CK_PRODUCT_UNIT_PRICE CHECK (UNIT_PRICE >= 0),
    CONSTRAINT CK_PRODUCT_UNITS_IN_STOCK CHECK (UNITS_IN_STOCK >= 0),
    CONSTRAINT CK_PRODUCT_UNITS_ON_ORDER CHECK (UNITS_ON_ORDER >= 0),
    CONSTRAINT CK_PRODUCT_REORDER_LEVEL CHECK (REORDER_LEVEL >= 0),
    CONSTRAINT CK_PRODUCT_DISCONTINUED CHECK (DISCONTINUED in ('Y','N'))
);

CREATE TABLE SUPPLIER
(
    COMPANY_NAME    VARCHAR(40) NOT NULL,
    CONTACT_NAME    VARCHAR(30),
    CONTACT_TITLE   VARCHAR(30),
    ADDRESS         VARCHAR(60),
    CITY        VARCHAR(15),
    REGION      VARCHAR(15),
    POSTAL_CODE     VARCHAR(10),
    COUNTRY         VARCHAR(15),
    PHONE       VARCHAR(24),
    FAX         VARCHAR(24),
    HOME_PAGE       VARCHAR(500),
    CONSTRAINT PK_SUPPLIER PRIMARY KEY (COMPANY_NAME)  
);

推荐答案

由于PRODUCT表的外键未指定ON DELETE操作，因此它将具有默认行为RESTRICT.由于您无法将此约束更新为SET NULL，因此您可能必须自己将它们设置为NULL.

As the foreign key to your PRODUCT table doe not specify an ON DELETE action, it'll have the default behaviour which is RESTRICT. Since you can't update this constraint to SET NULL, you'd probably have to set them NULL yourself.

首先，更改表，以便SUPPLIER_NAME外键可以接受NULL值.

First, alter the table so the SUPPLIER_NAME foreign key can accept NULL values.

然后，更新供应商在美国的PRODUCTS，将其SUPPLIER_NAME设置为NULL.像这样:

Then, update the PRODUCTS whose supplier are in the USA, set their SUPPLIER_NAME to NULL. Something like this:

update PRODUCT set SUPPLIER_NAME = NULL where SUPPLIER_NAME IN (
select SUPPLIER_NAME from SUPPLIER where COUNTRY = 'USA');

最后，您可以删除COUNTRY ='USA'的供应商.

And at last you can then delete the SUPPLIERS with COUNTRY = 'USA'.

这篇关于如何从表中删除而不必删除约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！