PL/SQL外键的建立和表数据删除

最新推荐文章于 2021-12-16 11:52:03 发布

最新推荐文章于 2021-12-16 11:52:03 发布 · 656 阅读

文章标签：

#SQL

Database 专栏收录该内容

9 篇文章

订阅专栏

本文介绍了如何在Customer和PartOrder表中创建外键约束，并演示了如何通过SQL语句删除特定记录。首先创建Customer和PartOrder表，接着为Customer表设置主键，然后在PartOrder表上创建对外键的引用。最后，通过锁定表并执行删除操作来移除指定记录。

1。表和外键的建立

--Create the Foreign Key on the PartOrder Table
ALTER TABLE PartOrder
 ADD CONSTRAINT PartOrder_Customer_FK
    FOREIGN KEY (CustomerID)
    REFERENCES Customer(CustomerID);

This code assumes that you have the structure listed below. Be aware that you must have a Primary Key before you can create the Foreign Key.

--Create the Customer Table
CREATE TABLE Customer
 (
  customerid    NUMBER(12) NOT NULL,
  name          VARCHAR2(50),
  address       VARCHAR2(200),
  lastvisit     DATE DEFAULT (sysdate)
 );

--Create the PartOrder Table
CREATE TABLE PartOrder
  (
  PartOrderID   Number(12) NOT NULL,
  CustomerID    Number(12) NOT NULL,
  SKU           Number(8)  
  );

--Create a Primary Key on the Customer Table
ALTER TABLE Customer
 ADD CONSTRAINT Customer_PK
    PRIMARY KEY (CustomerID)
    USING INDEX;

2。删除表中的数据

declare

begin
 LOCK TABLE customer in SHARE ROW EXCLUSIVE MODE NOWAIT;
 LOCK TABLE partorder in SHARE ROW EXCLUSIVE MODE NOWAIT;
 delete from partorder where partorderid=99;
 delete from partorder where partorderid=77;
 delete from customer where customerid=1;
end;
/