close
項目 PRIMARY KEY 單一 PRIMARY KEY 複合
建立 CREATE TABLE customer
(
  C_Id INT NOT NULL PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20)
);		 CREATE TABLE customer
(
  C_Id INT NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20),
  CONSTRAINT pk_Customer_Id PRIMARY KEY (C_Id,Name)
);
修改 ALTER TABLE customer
ADD PRIMARY KEY (C_Id);		 ALTER TABLE customer
ADD CONSTRAINT u_Customer_Id PRIMARY KEY (C_Id,Name);
OR
ALTER TABLE sales ADD CONSTRAINT sales_pk PRIMARY KEY(cust_id,prod_id,prom_id);
刪除 ALTER TABLE customer
DROP CONSTRAINT pk_PersonID;
or
刪除與主鍵相關的所有值
ALTER TABLE CUSTOMERS DROP PRIMARY KEY CASCADE
項目 FOREIGN KEY : 外鍵限制 單一 FOREIGN KEY : 外鍵限制 複合
建立 CREATE TABLE orders
(
  O_Id INT NOT NULL PRIMARY KEY,
  Order_No INT NOT NULL,
  C_Id INT FOREIGN KEY (C_Id) REFERENCES customers(C_Id)
);		 CREATE TABLE orders
(
  O_Id INT NOT NULL PRIMARY KEY,
  Order_No INT NOT NULL,
  C_Id INT,
  CONSTRAINT fk_Cusomer_Id FOREIGN KEY (C_Id) REFERENCES customers(C_Id)
);
修改 ALTER TABLE customer
ADD CONSTRAINT u_Customer_Id PRIMARY KEY (C_Id,Name);
OR
alter table sales add constraint sales_pk primary key(cust_id,prod_id,prom_id);		 替外鍵命名與多欄位的外鍵
ALTER TABLE sales ADD CONSTRAINT fk_cust_id FOREIGN KEY (cust_id) REFERENCES customer(cust_id);
or
ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (col1, col2) REFERENCES table_2 (cola,colb);
刪除 ALTER TABLE orders
DROP CONSTRAINT fk_Cusomer_Id;
項目 CHECK : 檢查限制 單一 CHECK : 檢查限制 複合
建立 CREATE TABLE customer
(
  C_Id INT NOT NULL CHECK (P_Id>0),
   cust_gender char(1) NOT NULL PRIMARY KEY  check(cust_gender in ('F','M')));
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20)
);		 CREATE TABLE customer
(
  C_Id INT NOT NULL,
  Name VARCHAR(50) NOT NULL,
  Address VARCHAR(255),
  Phone VARCHAR(20),
  CONSTRAINT chk_Customer CHECK (C_Id>0 AND Name!='XXX')
);
修改 ALTER TABLE customer
ADD CHECK (C_Id>0);		 ALTER TABLE customer
ADD CONSTRAINT chk_Customer CHECK (C_Id>0 AND Name!='XXX');
刪除 ALTER TABLE customer
DROP CONSTRAINT chk_Customer;
arrow
arrow
    全站熱搜

    oracleD2K 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄