Create a foreign key "set null on delete" with more than one field, and use desc to check : Create Primary Key « Constraints « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Constraints
  3. Create Primary Key

Create a foreign key "set null on delete" with more than one field, and use desc to check

  


SQL>
SQL>
SQL>
SQL> -- Create a foreign key "set null on delete" with more than one field
SQL>
SQL>     CREATE TABLE supplier
  2      (      supplier_id     numeric(10)     not null,
  3             supplier_name   varchar2(50)    not null,
  4             contact_name    varchar2(50),
  5             CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
  6      );

Table created.

SQL>
SQL>
SQL>     CREATE TABLE products
  2      (      product_id      numeric(10)     not null,
  3             supplier_id     numeric(10),
  4             supplier_name   varchar2(50),
  5             CONSTRAINT fk_supplier_comp
  6               FOREIGN KEY (supplier_id, supplier_name)
  7              REFERENCES supplier(supplier_id, supplier_name)
  8              ON DELETE SET NULL
  9      );

Table created.

SQL>
SQL>
SQL>     desc products;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------

 PRODUCT_ID                                                                                            NOT NULL NUMBER(10)
 SUPPLIER_ID                                                                                            NUMBER(10)
 SUPPLIER_NAME                                                                                          VARCHAR2(50)

SQL>     desc supplier;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------

 SUPPLIER_ID                                                                                           NOT NULL NUMBER(10)
 SUPPLIER_NAME                                                                                         NOT NULL VARCHAR2(50)
 CONTACT_NAME                                                                                           VARCHAR2(50)

SQL>
SQL>     drop table products cascade constraints;

Table dropped.

SQL>
SQL>     drop table supplier cascade constraints;

Table dropped.

SQL>
SQL>








Related examples in the same category

1.Using a CREATE TABLE statement: create a table with primary key
2.Violate the primary key and foreign key relation
3.ORA-12991: column is referenced in a multi-column constraint
4.One-to-one using a primary-key and foreign-key relationship
5.Use three columns as primary key
6.Set primary key when declaring a column