Oracle establishes primary key and index "go"

This article was transferred from: http://blog.sina.com.cn/s/blog_439f80c4010094n1.html

To create a primary key:

ALTER TABLE T add primary key (V)

T is the name of the table, and V is the column name

To create an index:

Create INDEX F2009100000nminfosys_xiang on F2009100000nminfo (Sys_xiang);
Create a generic index with the index name table name + column name

Create unique index f2009100000nminfosys_zdm on f2009100000nminfo( sys_zdm );

Create a unique index

Create BITMAP index F2009100000nminfosys_xiang on F2009100000nminfo (Sys_xiang);
Create a bitmap index

The complete syntax is as follows:

CREATE (unique| BITMAP) INDEX [user name.] Index name on [user name.] Table name (column name [ASC | DESC], [column name [ASC | DESC]] ...)

[Tablespace table space name]

[PCTFREE positive integer number]

[Initrans positive integer number]

[Maxtrans positive integer number]

[Save clause]

[LOGGING | Nologging]

[Nosort]

How does Oracle know that the table does not have a primary key?

In general, a table's primary key is necessary, and a table without a primary key can be said to be incompatible with the design specification.
SELECT table_name from User_tables t WHERE not EXISTS
(SELECT table_name from user_constraints c WHERE constraint_type = ' P ' and T.table_name=c.table_name)

Other Related Data dictionary interpretation
User_tables table
User_tab_columns columns for tables
user_constraints constraints
User_cons_columns the relationship between constraints and columns
User_indexes Index

Oracle builds a self-augmented primary key

First of all, you have to have a table!
CREATE TABLE Example (
ID Number (4) Not NULL PRIMARY KEY,
NAME VARCHAR (25),
PHONE VARCHAR (10),
ADDRESS VARCHAR (50));
If you have any questions about the above statement,, it is recommended that you do not continueThere are some times you might as well go and see Jin Yong reading Qiong Yao!
Then, you need a customSequence
CREATE SEQUENCE emp_sequence
INCREMENT by 1--add a few each time
Start with 1-from1 Start Count
Nomaxvalue--Do not set the maximum value
Nocycle--keep accumulating, not looping
NOCACHE--Do not build buffers
The above code completes a sequence(sequence) the establishment process, a name calledEmp_sequence, the range is from1 start to infinity(The extent of infinity is determined by your machine.)), Nocycle is decided not to cycleIf you set the maximum value then you can useCycle will makeSeq to Max after loop. forNoCache, by the way, if you giveCache value then the system will automatically read yourcache value size seq
, which will speed up the operation when repeated operation oracle dead seq value will be the same as the last discontinuity cache, because time is Money ! irrelevant! The book is connected to the above, you only have the table and the sequence is not enough, but also need a trigger to execute it! The code is as follows:
CREATE TRIGGER "trigger name " before
INSERT on example for each ROW when (New.id was null)
Begin
Select Emp_sequence.nextval into:new.id from dual;end;
Finish the call! Let's try inserting the data below!
INSERT into example (name,phone,address) Values (' Cao ', ' 56498543 ', ' Heibei ') ;

Primary Key and Unique key

Both the Primary key and the unique key are unique constraints. But there's a big difference:

1, Primary key 1 or more columns must be NOT NULL, if column is null, when the Primary key is added, the columns are automatically changed to NOT NULL. The unique KEY does not have this requirement on the column.

2. A table can have only one primary key, but there may be multiple unique keys.

Here's a test Note:

sql> Create table t (a int,b int,c int,d int);

Table created.

Sql> desc T
Name Null? Type
----------------------------------------- -------- -----------

A Number (38)
B Number (38)
C Number (38)
D Number (38)

Sql> ALTER TABLE t add constraint pk_t primary key (A, b);

Table altered.

Sql> desc T
Name Null? Type
----------------------------------------- -------- ----------------

A not NULL number (38)
B not NULL number (38)
C Number (38)
D Number (38)

You can see that both A and B two columns are automatically changed to not NULL

Sql> ALTER TABLE T Modify (a int null);
ALTER TABLE T Modify (a int null)
*
ERROR at line 1:
Ora-01451:column to is modified to null cannot is modified to null
As you can see, column A is not allowed to change to null

Sql> ALTER TABLE t drop constraint pk_t;

Table altered.

Sql> ALTER TABLE t add constraint uk_t_1 unique (a, b);

Table altered.

Sql> desc T
Name Null? Type
----------------------------------------- -------- -----------

A Number (38)
B Number (38)
C Number (38)
D Number (38)

We see that column a goes back to null.

Notice that when you delete a primary key, the nullable of the column returns to its original state. If, after the primary key is created, the primary key column that was originally NULL is explicitly set to NOT NULL, it is still not NULL after the primary key is deleted. For example, after creating the primary key, do the following to see:

Sql> ALTER TABLE t modify (b int not null);

Table altered.

Sql> ALTER TABLE t drop constraint pk_t;

Table altered.

Sql> desc T
Name Null? Type
----------------------------------------- -------- ----------

A Number (38)
B not NULL number (38)
C Number (38)
D Number (38)

Then do the following experiments:

sql> drop table t;

Table dropped.

Sql> CREATE TABLE T (a int,b int,c int,d int);

Table created.

Sql> ALTER TABLE t add constraint uk_t_1 unique (a, b);

Table altered.

Sql> ALTER TABLE t add constraint uk_t_2 unique (c,d);

Table altered.

You can see the addition of two unique keys. See if you can add two primary keys:

Sql> ALTER TABLE t add constraint pk_t primary key (c);

Table altered.

Sql> ALTER TABLE t add constraint pk1_t primary key (d);
ALTER TABLE t add constraint pk1_t primary key (d)
*
ERROR at line 1:
Ora-02260:table can has only one primary key
From this you can see that a table can have only one primary key.

Sql> ALTER TABLE t drop constraint pk_t;

Table altered.

sql> INSERT into t (A, B) values (null,null);

1 row created.

Sql>/

1 row created.

sql> INSERT into t (A, B) values (null,1);

1 row created.

Sql>/
INSERT into t (A, B) values (null,1)
*
ERROR at line 1:
Ora-00001:unique constraint (sys.uk_t_1) violated

sql> INSERT into t (A, B) values (1,null);

1 row created.

Sql>/
INSERT into t (A, B) values (1,null)
*
ERROR at line 1:
Ora-00001:unique constraint (sys.uk_t_1) violated

Primary KEY and unique key constraints are implemented through a reference index, and if the inserted values are null, then according to the principle of the index, all null values are not recorded on the index, so when you insert a full null value, you can have duplicates, while others cannot insert duplicate values.

Oracle establishes primary key and index "go"