1. What is a PRIMARY KEY in Oracle?
A PRIMARY KEY in Oracle is a constraint used to uniquely identify each record in a database table. It ensures that the values in the primary key column(s) are unique and cannot be NULL. A table can only have one PRIMARY KEY constraint.
2. Can a PRIMARY KEY contain NULL values?
No, a PRIMARY KEY cannot contain NULL values. Each column that is part of the PRIMARY KEY must have a valid, non-null value to ensure every record can be uniquely identified.
3. Can a table have more than one PRIMARY KEY?
No, a table can only have one PRIMARY KEY constraint. However, you can create a composite primary key, which involves multiple columns to form a unique combination for each row.
4. What happens if I try to insert a duplicate value in a PRIMARY KEY column?
If you attempt to insert a duplicate value into a column defined with a PRIMARY KEY constraint, Oracle will raise an error, indicating a violation of the primary key uniqueness rule.
5. Can a composite PRIMARY KEY contain NULL values?
No, none of the columns in a composite PRIMARY KEY can have NULL values. The combination of values in those columns must always be unique and non-null.
6. How do I create a PRIMARY KEY in Oracle?
You can create a PRIMARY KEY constraint during the table creation or add it later using the ALTER TABLE statement.
- During table creation:
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
name VARCHAR2(100)
);
- For a composite key:
CREATE TABLE orders (
order_id NUMBER,
customer_id NUMBER,
CONSTRAINT pk_orders PRIMARY KEY (order_id, customer_id)
);
- After table creation:
ALTER TABLE employees
ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);
7. Can I define a PRIMARY KEY on multiple columns?
Yes, you can define a composite PRIMARY KEY using multiple columns. This ensures that the combination of values in these columns is unique across all rows.
Example:
CREATE TABLE orders (
order_id NUMBER,
customer_id NUMBER,
order_date DATE,
CONSTRAINT pk_orders PRIMARY KEY (order_id, customer_id)
);
8. How does Oracle handle indexing for PRIMARY KEY?
When you define a PRIMARY KEY, Oracle automatically creates a unique index on the column(s) defined as the primary key. This index helps enforce the uniqueness constraint and improves query performance.
9. Can I remove a PRIMARY KEY constraint from a table?
Yes, you can drop a PRIMARY KEY constraint using the ALTER TABLE statement:
ALTER TABLE employees
DROP CONSTRAINT pk_employee_id;
This will remove the primary key constraint and the associated unique index.
10. What is the difference between PRIMARY KEY and UNIQUE constraint?
- PRIMARY KEY: Ensures that the values in the column(s) are unique and not NULL. A table can only have one PRIMARY KEY constraint.
- UNIQUE: Ensures uniqueness but allows NULL values. A table can have multiple UNIQUE constraints.
11. Can a column with a PRIMARY KEY be indexed?
Yes, when you define a PRIMARY KEY, Oracle automatically creates a unique index for that column or combination of columns. This index ensures the uniqueness of the primary key and helps improve query performance.
12. Can I modify a PRIMARY KEY after it is created?
To modify a PRIMARY KEY (e.g., changing the columns), you must first drop the existing primary key and then recreate it with the desired columns.
Example to drop a primary key:
ALTER TABLE employees
DROP CONSTRAINT pk_employee_id;
Then, recreate it with the new columns.
13. What is the benefit of using a PRIMARY KEY?
The PRIMARY KEY enforces data integrity by:
- Ensuring that each record in the table is uniquely identifiable.
- Preventing duplicate and NULL values in the key columns.
- Automatically creating an index that improves query performance.
- Serving as the basis for defining relationships with foreign keys.
14. What happens if I try to insert a NULL value into a PRIMARY KEY column?
If you attempt to insert a NULL value into a PRIMARY KEY column, Oracle will raise an error, because NULL values are not allowed in primary key columns.
15. Can I have both a PRIMARY KEY and a UNIQUE constraint on the same column?
A column can have only one PRIMARY KEY constraint. However, a column that has a PRIMARY KEY automatically enforces uniqueness, so there’s no need for an additional UNIQUE constraint on the same column.
16. What happens if I try to insert a duplicate record in a PRIMARY KEY column?
If you try to insert a duplicate value (one that already exists) into a PRIMARY KEY column, Oracle will raise a ORA-00001: unique constraint violated error.
17. What type of index is created for the PRIMARY KEY?
Oracle automatically creates a unique index for the column(s) defined as the PRIMARY KEY. This index helps enforce the uniqueness rule and improves the performance of queries that filter based on the primary key.
18. What is the impact of using a PRIMARY KEY on table performance?
- Positive impact on retrieval: The unique index created for the PRIMARY KEY improves query performance by providing efficient lookups.
- Potential performance cost on inserts/updates: Inserting or updating rows might be slightly slower because the index has to be updated to maintain uniqueness.
19. Can a PRIMARY KEY be defined with a default value?
No, a PRIMARY KEY cannot have a default value because the values in a PRIMARY KEY column must always be unique and non-null, and the database cannot generate default unique values automatically.
20. How does a PRIMARY KEY relate to foreign keys in other tables?
A PRIMARY KEY is often used in conjunction with foreign keys to establish relationships between tables. A foreign key in one table references the PRIMARY KEY in another table, ensuring referential integrity between the related tables.
Example:
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY,
customer_id NUMBER,
CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
No comments:
Post a Comment