Tuesday, November 8, 2022

HOW TO CREATE A PRIMARY KEY AND A FOREIGN KEY

 A primary key is the unique identifier of a table. It is how each row is uniquely identified. If the primary key has more than one column, it is called a "composite primary key".

All primary keys are unique keys, but not all unique keys are primary keys. The main difference is that unique keys can have null values. 

Another important feature is that all primary keys have an implicit index included upon creation. 

Having an index in a column is a great advantage because it results in making a query run faster and get the results in a quicker manner. 

There are two ways to create a primary key, 

First: upon table creation


In this case, customer_pk is the primary key name, and customer_id is the column name that is having the primary key.

Second: as an independent statement, after the table has been created


For the foreign key, there are also two ways,

First: upon table creation


And second: as an independent statement, after the table has been created


Creating primary keys and foreign keys is a fundamental aspect of database design in Oracle PL/SQL. Primary keys ensure the uniqueness of a column (or a combination of columns) in a table, while foreign keys establish relationships between tables. Here's how you can create a primary key and a foreign key in Oracle PL/SQL:

1. Creating a Primary Key:

A primary key ensures that the values in a column (or a set of columns) are unique and not null. It uniquely identifies each row in the table.


CREATE TABLE parent_table ( 
     parent_id NUMBER PRIMARY KEY, 
     parent_name VARCHAR2(100) 
);

In this example, the parent_id column is designated as the primary key.

2. Creating a Foreign Key:

A foreign key establishes a relationship between two tables, usually linking a column in one table to the primary key column in another table.


CREATE TABLE child_table ( 
     child_id NUMBER PRIMARY KEY, 
     child_name VARCHAR2(100), 
     parent_id NUMBER,
    CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES         parent_table (parent_id) 
);

In this example, the parent_id column in the child_table is a foreign key referencing the parent_id column in the parent_table.

Here's a breakdown of the syntax:

  • CONSTRAINT fk_parent: This names the foreign key constraint. You can choose any meaningful name.

  • FOREIGN KEY (parent_id): This specifies the column in the current table (child_table) that will act as the foreign key.

  • REFERENCES parent_table (parent_id): This specifies the referenced table and column (the parent table and its primary key column).

Foreign keys maintain data integrity by ensuring that values in the child table's foreign key column match values in the referenced parent table's primary key column. This enforces relationships between the tables.

Remember that you need to create the referenced table (parent table) before you create the table containing the foreign key (child table).

These constraints play a crucial role in maintaining the integrity and relationships of your database data, helping to prevent inconsistencies and errors.



Hope you all have a great day!!!








on