Shulou(Shulou.com)06/01 Report--

The Oracle primary key Primary Key contains non-null and unique constraints.

Add a statement for a primary key

Alter table table_name add constraint cons_name primary key (col_name)

Check to see if the primary key is created successfully

Select dbms_metadata.get_ddl ('OBJECT_TYPE','NAME','SCHEMA') from dual

OBJECT_TYPE (TABLE,PARTITION,INDEX.)

NAME (OBJECT_NAME)

SCHEMA (INSTANCE)

Corresponding to the corresponding contents 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, which is located on CUSTTEL

CREATE TABLE "NEWCCS". "USERLEVELBYTEL_BAK" ("CUSTTEL" VARCHAR2 (20), "USERLEVEL" NUMBER (*, 0) NOT NULL ENABLE, "CUSTID" VARCHAR2 (20) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255NOCOMPRESS LOGGING 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)

After creating the primary key, it is found that the table has added a unique index and primary key field.

CREATE TABLE "NEWCCS". "USERLEVELBYTEL_BAK" ("CUSTTEL" VARCHAR2 (20), "USERLEVEL" NUMBER (*, 0) NOT NULL ENABLE, "CUSTID" VARCHAR2 (20) CONSTRAINT "PK_PRIMARY" PRIMARY KEY ("CUSTTEL") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255STORAGE (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 255NOCOMPRESS 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 255STORAGE (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 STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 0 PCTINCREASE 1 PCTINCREASE 1 PCTINCREASE) FREELISTS "FREELISTS" ALTER TABLE "NEWCCS". "USERLEVELBYTEL_BAK" MODIFY ("USERLEVEL" NOT NULL ENABLE)

Verify:

Repeatedly insert data insert into userlevelbytel values into the table ('10010')

The following error prompt appears (unique constraint error):

ORA-00001: unique constraint (NEWCCS.PK_PRIMARY) violated

Insert a null value of insert into userlevelbytel values ('', '3percent,'')

The following error prompt appears (unique constraint error):

ORA-01400: cannot insert NULL into ("NEWCCS". "USERLEVELBYTEL_BAK". "CUSTTEL")

Add unique constraints separately to compare differences

Delete the table primary key:

Alter table userlevelbytel_bak drop constraint pk_primary

CREATE TABLE "NEWCCS". "USERLEVELBYTEL_BAK" ("CUSTTEL" VARCHAR2 (20), "USERLEVEL" NUMBER (*, 0) NOT NULL ENABLE, "CUSTID" VARCHAR2 (20) CONSTRAINT "UNIQUE_CUSTTEL" UNIQUE ("CUSTTEL") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255COMPUTE 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 255NOCOMPRESS 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". "UNIQUE_CUSTTEL" ON "NEWCCS". "USERLEVELBYTEL_BAK" ("CUSTTEL") PCTFREE 10 INITRANS 2 MAXTRANS 255COMPUTE 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 MAXTRANS 255COMPUTE 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 unique constraints are created

Test:

Repeatedly insert a non-empty record:

ORA-00001: unique constraint (NEWCCS.UNIQUE_CUSTTEL) violated

Repeatedly insert an empty record:

Select * from userlevelbytel_bak where custtel is null

The query result is multiple duplicate empty records

It can be seen that the unique constraint only constrains the non-null value, but the null value in Oracle cannot be compared, so the unique constraint cannot be limited even if it is inserted repeatedly, but it can be compared under the combined constraint conditions, such as (Amaine Bline null).

For example:

Alter table userlevelbytel_bak add constraint unique_custtel unique (custtel,userlevel)

Repeatedly insert an empty record:

ORA-00001: unique constraint (NEWCCS.UNIQUE_CUSTTEL) violated

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.