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

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;
/