oracle set primary key

In Oracle database, the primary key is a constraint used to uniquely identify row data in each table. Primary key constraints can ensure that any row of data in the table has a unique identity. Therefore, when designing a database, it is very important to set a primary key for the table. Below we will explain in detail how to set up primary keys in Oracle.

1. Set primary key constraints when creating a table

When creating a table, you can define the primary key of the table by setting primary key constraints. The following is the syntax for setting primary key constraints when creating a table:

CREATE TABLE table_name (
   column1 datatype constraint constraint_name PRIMARY KEY,
   column2 datatype,
   column3 datatype,
   .....
);

In the above syntax, column1 represents the column name in the table, and datatype represents the data type of the column . The PRIMARY KEY constraint is used to set the primary key constraint, and constraint_name is the name of the primary key constraint.

For example, we can create a Employee table and set the EmployeeID column as the primary key:

CREATE TABLE Employee (
   EmployeeID number(10) constraint pk_Employee PRIMARY KEY,
   FirstName varchar2(50),
   LastName varchar2(50),
   Age number(3)
);

2. In the existing table Add primary key constraints in

If the primary key constraints are not set when creating the table, we can also add the primary key constraints after the table is created. The following is the syntax for adding a primary key constraint to an existing table:

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

In the above syntax, table_name represents the name of the table to which the primary key constraint is to be added, and constraint_name is the primary key The name of the constraint, (column1, column2, ... column_n) represents the column name to be set as the primary key. Multiple columns can be set as the primary key.

For example, if we have created an Orders table, we can add the OrderID and OrderDate columns as primary keys in that table:

ALTER TABLE Orders
ADD CONSTRAINT pk_Orders PRIMARY KEY (OrderID, OrderDate);

3. Prohibit Null values

After setting the primary key constraint, Oracle will automatically prohibit Null values ​​from appearing in the table. If any row of data inserted contains a Null value, it will be rejected and an error message will be triggered. Therefore, make sure that all values ​​in the primary key columns in the table are not Null.

In some cases, we need the primary key to allow Null values. At this time, you need to use the NULL keyword in the primary key column to allow Null values.

For example, in the Employee table, if we do not want the employee to be identified by the EmployeeID column, we can set it to allow Null values:

CREATE TABLE Employee (
   EmployeeID number(10) constraint pk_Employee PRIMARY KEY,
   FirstName varchar2(50),
   LastName varchar2(50),
   Age number(3),
   AltEmpID number(10) NULL
);

The above is the method of setting primary key constraints in Oracle. The setting of primary keys is very important for database performance and data integrity, so when designing the database, be sure to set primary key constraints to ensure data consistency and integrity.

The above is the detailed content of oracle set primary key. For more information, please follow other related articles on the PHP Chinese website!