Oracle primary keys and constraints

Oracle PRIMARY key Primary key contains non-null constraints and UNIQUE constraints.

Add a PRIMARY KEY statement

ALTER TABLE table_name add constraint cons_name primary KEY (col_name);

To see if the primary key was created successfully

Select Dbms_metadata.get_ddl (' object_type ', ' NAME ', ' SCHEMA ') from dual;

Object_type (Table,partition,index ...)

NAME (object_name)

SCHEMA (INSTANCE)

Corresponds to the corresponding content in the object table:

Select Object_type,object_name,owner from Dba_objects

The obtained DDL statement indicates that a primary key column has been created and that the column is on Custtel

CREATE TABLE "Newccs". " Userlevelbytel_bak "(" Custtel "VARCHAR2 ()," USERLEVEL "number (*,0) not NULL ENABLE," CUSTID "VARCHAR2 ()) PCTF REE pctused Initrans 1 Maxtrans 255 nocompress LOGGING STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 MAXEXTENTS 2   147483645 pctincrease 0 freelists 1 FREELIST GROUPS 1 buffer_pool DEFAULT) tablespace "Newccs"; ALTER TABLE "Newccs". " Userlevelbytel_bak "MODIFY (" USERLEVEL "not NULL ENABLE);

The table was created with a unique index and a primary key field added after the primary key

create table  "Newccs". " Userlevelbytel_bak "     ( " Custtel " varchar2),  " USERLEVEL "  Number (*,0)  NOT NULL ENABLE,   "CUSTID"  varchar2,    constraint   "Pk_primary"  PRIMARY KEY  ("Custtel")   using index pctfree 10  initrans 2 maxtrans 255   storage (initial 65536 next  1048576 minextents 1 maxextents 2147483645  pctincrease 0 freelists  1 freelist groups 1 buffer_pool default)   TABLESPACE  "Newccs"   ENABLE   )  PCTFREE 10 PCTUSED 40 INITRANS 1  Maxtrans 255 nocompress logging  storage (initial 65536 next 1048576  minextents 1 maxextents 2147483645  pctincrease 0 freelists 1  Freelist groups 1 buffer_pool default)   TABLESPACE  "Newccs"  ;    CREATE UNIQUE INDEX  "Newccs". " Pk_primary " ON " Newccs "." Userlevelbytel_bak "  (" Custtel ")    PCTFREE 10 INITRANS 2 MAXTRANS  255   storage (initial 65536 next 1048576 minextents 1 maxextents  2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1  Buffer_pool default)   TABLESPACE  "Newccs"  ;   ALTER TABLE  " Newccs "." Userlevelbytel_bak " ADD CONSTRAINT " Pk_primary " PRIMARY KEY  (" Custtel ")    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255    STORAGE (initial 65536 next 1048576 minextents 1 maxextents 2147483645   pctincrease 0 freelIsts 1 freelist groups 1 buffer_pool default)   TABLESPACE  "Newccs "  ENABLE;   ALTER TABLE " Newccs "." Userlevelbytel_bak " MODIFY  (" USERLEVEL " not null enable);

Verify:

Insert data repeatedly into the table insert into Userlevelbytel values (' 10010 ', ' 3 ', ');

The following error message appears ( UNIQUE constraint error ):

Ora-00001:unique constraint (Newccs. Pk_primary) violated

Insert null value INSERT INTO Userlevelbytel values (' ', ' 3 ', ');

The following error message appears ( UNIQUE constraint error ):

Ora-01400:cannot insert NULL into ("Newccs". " Userlevelbytel_bak "." Custtel ")

Add UNIQUE constraint comparison differences separately

Delete the table primary key:

ALTER TABLE Userlevelbytel_bak drop constraint pk_primary;

create table  "Newccs". " Userlevelbytel_bak "     ( " Custtel " varchar2),  " USERLEVEL "  Number (*,0)  NOT NULL ENABLE,   "CUSTID"  varchar2,    constraint   "Unique_custtel"  UNIQUE  ("Custtel")   USING INDEX PCTFREE 10  Initrans 2 maxtrans 255 compute statistics   storage (INITIAL  65536 next 1048576 minextents 1 maxextents 2147483645  pctincrease  0 freelists 1 freelist groups 1 buffer_pool default)    tablespace  "Newccs"   ENABLE   )  PCTFREE 10 PCTUSED 40  Initrans 1 maxtrans 255 nocompress logging  storage (INITIAL 65536  next 1048576 minextents 1 maxextents 2147483645  pctincrease 0& nbsp; Freelists 1 freelist groups 1 buffer_pool default)   TABLESPACE  " Newccs " ;   CREATE UNIQUE INDEX " Newccs "." Unique_custtel " ON " Newccs "." Userlevelbytel_bak "  (" Custtel ")    PCTFREE 10 INITRANS 2 MAXTRANS  255 compute statistics   storage (initial 65536 next 1048576  minextents 1 maxextents 2147483645  pctincrease 0 freelists 1  Freelist groups 1 buffer_pool default)   TABLESPACE  "Newccs"  ;    ALTER TABLE  "Newccs". " Userlevelbytel_bak " MODIFY  (" USERLEVEL " not null enable);    alter  TABLE  "Newccs". " Userlevelbytel_bak " ADD CONSTRAINT " Unique_custtel " UNIQUE  (" Custtel ")    Using index pctfree 10 initrans 2&nbsP Maxtrans 255 compute statistics   storage (initial 65536 next  1048576 minextents 1 maxextents 2147483645  pctincrease 0 freelists  1 freelist groups 1 buffer_pool default)   TABLESPACE  "Newccs"   ENABLE;

You can see that the unique constraint is created

Test:

To insert a non-empty record repeatedly:

Ora-00001:unique constraint (Newccs. Unique_custtel) violated

To insert an empty record repeatedly:

SELECT * from Userlevelbytel_bak where custtel is null;

Query results are multiple duplicate empty records

It can be seen that the unique constraint constrains only non-null values, whereas NULL values cannot be compared in Oracle, so even if you repeatedly insert, the unique constraint cannot be limited, but under combined constraints, such as (A,b,null), it can be compared.

Example:

ALTER TABLE Userlevelbytel_bak add constraint Unique_custtel unique (custtel,userlevel);

To insert an empty record repeatedly:

Ora-00001:unique constraint (Newccs. Unique_custtel) violated

This article is from the "linuxoracle" blog, make sure to keep this source http://onlinekof2001.blog.51cto.com/3106724/1582962

Oracle primary keys and constraints