в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:02:00Z

1 версия

Новая страница

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



<source lang="sql">

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>

</source>

==One-to-one using a primary-key and foreign-key relationship==



<source lang="sql">

SQL>
SQL>
SQL> CREATE TABLE Office
2 (office_id VARCHAR2(10) NOT NULL,
3 building_name VARCHAR2(20),
4 PRIMARY KEY (office_id));
Table created.
SQL>
SQL> CREATE TABLE Programmer
2 (lect_id VARCHAR2(10) NOT NULL,
3 lect_name VARCHAR2(20),
4 office_id VARCHAR2(10),
5 PRIMARY KEY (lect_id),
6 FOREIGN KEY (office_id) REFERENCES Office (office_id)
7 ON DELETE CASCADE);
Table created.
SQL>
SQL> drop table Office cascade constraints;
Table dropped.
SQL> drop table Programmer cascade constraints;
Table dropped.

</source>

==ORA-12991: column is referenced in a multi-column constraint==



<source lang="sql">

SQL> create table registrations
2 ( attendee NUMBER(4)
3 , course VARCHAR2(6)
4 , begindate DATE
5 , evaluation NUMBER(1)
6 , constraint R_PK primary key (attendee,course,begindate)
7 ) ;
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> alter table registrations
2 drop column begindate;
drop column begindate
*
ERROR at line 2:
ORA-12991: column is referenced in a multi-column constraint

SQL>
SQL>
SQL> drop table registrations;
Table dropped.
SQL>
SQL>
SQL>

</source>

==Set primary key when declaring a column==



<source lang="sql">

SQL>
SQL> create table t
2 ( x int primary key ,
3 y date,
4 z clob )
5 /
Table created.
SQL>
SQL> drop table t;
Table dropped.
SQL>

</source>

==Use three columns as primary key==



<source lang="sql">

SQL>
SQL> create table registrations
2 ( attendee NUMBER(4)
3 , course VARCHAR2(6)
4 , begindate DATE
5 , evaluation NUMBER(1)
6 , constraint R_PK primary key (attendee,course,begindate)
7 ) ;
Table created.
SQL>
SQL> insert into registrations values (2,"SQL",date "2009-04-12",4 );
1 row created.
SQL> insert into registrations values (14,"SQL",date "2009-04-12",5 );
1 row created.
SQL> insert into registrations values (6,"SQL",date "2009-04-12",4 );
1 row created.
SQL> insert into registrations values (11,"SQL",date "2009-04-12",2 );
1 row created.
SQL> insert into registrations values (8,"SQL",date "2009-10-04",NULL);
1 row created.
SQL> insert into registrations values (9,"SQL",date "2009-10-04",3 );
1 row created.
SQL> insert into registrations values (13,"SQL",date "2009-10-04",4 );
1 row created.
SQL> insert into registrations values (13,"SQL",date "2009-12-13",NULL);
1 row created.
SQL> insert into registrations values (6,"SQL",date "2009-12-13",NULL);
1 row created.
SQL> insert into registrations values (3,"OAU",date "2009-08-10",4 );
1 row created.
SQL>
SQL> select evaluation
2 from registrations
3 where attendee = 8
4 order by evaluation;
EVALUATION
----------

SQL>
SQL> drop table registrations;
Table dropped.

</source>

==Using a CREATE TABLE statement: create a table with primary key==



<source lang="sql">

SQL>
SQL> --Using a CREATE TABLE statement
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)
6 );
Table created.
SQL>
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 supplier;
Table dropped.
SQL>
SQL>

</source>

==Violate the primary key and foreign key relation==



<source lang="sql">

SQL>
SQL> create table parent( pk int,constraint parent_pk primary key(pk) );
Table created.
SQL> create table child ( fk,constraint child_fk foreign key(fk)
2 references parent deferrable );
Table created.
SQL> /

SQL> insert into parent values( 1 );
1 row created.
SQL> insert into child values( 1 );
1 row created.
SQL> commit;
Commit complete.
SQL> update parent set pk = 2;
update parent set pk = 2
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.CHILD_FK) violated - child record found

SQL> update child set fk = 2;
update child set fk = 2
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.CHILD_FK) violated - parent key not found

SQL> set constraints child_fk deferred;
Constraint set.
SQL> update parent set pk=2;
1 row updated.
SQL> select * from parent;
PK
----------
2
SQL> select * from child;
FK
----------
1
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (SYS.CHILD_FK) violated - child record found

SQL> set constraints child_fk deferred;
Constraint set.
SQL> update parent set pk=2;
1 row updated.
SQL> select * from parent;
PK
----------
2
SQL> select * from child;
FK
----------
1
SQL> set constraints child_fk immediate;
set constraints child_fk immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.CHILD_FK) violated - parent key not found

SQL> update child set fk = 2;
1 row updated.
SQL> set constraints child_fk immediate;
Constraint set.
SQL> commit;
Commit complete.
SQL> select * from parent;
PK
----------
2
SQL> select * from child;
FK
----------
2
SQL>
SQL> drop table child cascade constraints;
Table dropped.
SQL> drop table parent cascade constraints;
Table dropped.

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Constraints/Create Primary Key - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия