Primary Key constraints of oracle
A primary key constraint is a primary key constraint used to uniquely identify a row of records. Only one primary key constraint can be defined in a table. This constraint can be defined on Individual columns or multiple columns (Table-level constraints ). A column or combination that defines the primary key constraint cannot have duplicate values or null values.
Add primary key
When creating a table, add the primary key constraint to the column in the following format:
Column_name data_type [constraint constraint_name] primary key or create table table_name (column_name data_type, [...,] [constraint constraint_name] primary key (column_name) [,...])
You can also add a primary key constraint to a common table in the following format:
Alter table table_name add [constraint constraint_name] primary key (column_name );
Delete primary key
To delete the primary key constraint on a column, you must use the alter table... drop statement. However, you can only use the specified constraint name as follows:
Alter table table_name drop constraint constraint_name;
If you use the constraint clause to specify a constraint name when adding a constraint, you can use this name directly. If the constraint clause is not used, the constraint name is automatically created by oracle, in this case, you can view the constraints by using the data dictionary user_cons_columns and user_constraints.
The specific operations are as follows:
SQL> create table person (
Pid number (4) not null,
Pname varchar (20 ),
Char (2)
);
The table has been created.
SQL> desc person;
Is the name empty? Type
----------------------------------------------------------------------------
Pid not null number (4)
PNAME VARCHAR2 (20)
CHAR (2)
SQL> alter table person add constraint person_pk primary key (pid );
The table has been changed.
SQL> insert into person values (1, 'aaa', 'femal ');
One row has been created.
SQL> insert into person values (1, 'aaa', 'femal ');
Insert into person values (1, 'aaa', 'femal') // Foreign keys cannot have duplicate values
*
Row 3 has an error:
ORA-00001: A unique constraint violation (SYSTEM. PERSON_PK) // Foreign keys do not allow repeated values
SQL> insert into person values (2, 'aaa', 'femal ');
One row has been created.
SQL> alter table person drop constraint person_pk; // delete a foreign key
The table has been changed.
SQL> insert into person values (2, 'aaa', 'femal'); // you can add duplicate values after deletion.
One row has been created.
SQL>
I
SQL> create table p2 (
2 pid number (4) primary key,
3 pname varchar (20 ),
4 bytes X char (2)
5 );
The table has been created.
II
SQL> create table p3 (
2 pid number (4) not null,
3 pname varchar (20 ),
4 bytes X char (2 ),
5 constraint pk_p3 primary key (pid)
6 );
The table has been created.
3.
SQL> create table p4 (
2 pid number (4) not null,
3 pname varchar (20 ),
4 bytes X char (2 ),
5 constraint pk_p4 primary key (pid, pname)
6 );
The table has been created.
