Observation from real-time work in Primary and Foreign Key Constraints

Primary keys are an important concept in SQL DB. They provide a unique ID for every row in a database table.

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

Properties :

1. No duplicate values are allowed, i.e., the Column assigned as the primary key should have UNIQUE values only.
2. NO NULL values are present in the column with the Primary key. Hence there is a Mandatory value in the column having a Primary key.
3. Only one primary key per table exists although the Primary key may have multiple columns.
4. No new row can be inserted with the already existing primary key.
5. Classified as: a) Simple primary key with a Single column 2) The composite primary key has Multiple columns.
6. Defined in Create table / Alter table statement.

USES OF SQL PRIMARY KEY FOR A B2B ORGANIZATION

The following SQL creates a PRIMARY KEY on the 'sid' (supplier) column when the "product" table is created:

Ref:

SQL Queries:

In the above queries, In a relational database, a primary key is a unique identifier for each record in a table. It serves as a way to uniquely identify a row within that table. A foreign key, on the other hand, is a field in a table that refers to the primary key in another table. The foreign key establishes a link between the two tables, creating a relationship.

The primary key is crucial for the concept of referential integrity, which ensures that relationships between tables are maintained accurately. When you define a foreign key in a table, it means that the values in the foreign key column must correspond to the values in the primary key column of the referenced table. This relationship helps maintain data consistency and integrity.

If a table does not have a primary key, there is no guarantee of unique identification for each row. In such a case, it becomes challenging to establish a reliable and meaningful relationship between tables using foreign keys. Without a primary key, it may be impossible to ensure that the values in the foreign key column of one table correspond to unique and valid values in the referenced table's primary key column.

Once SQL understood the reference from the inventory structure we can now implement the query according to our requirements,

SQL FOREIGN KEY Constraint

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.

The table with the foreign key (PID-Product ID) is called the child table, and the table with the primary key is called the referenced or parent table.

Foreign Key is a column that refers to the primary key/unique key of other table. So it demonstrates relationship between tables and act as cross reference among them. Table in which foreign key is defined is called Foreign table/Referencing table. Table that defines primary/unique key and is referenced by foreign key is called primary table/master table/ Referenced Table. It is Defined in Create table/Alter table statement.

For the table that contains the Foreign key, it should match the primary key in referenced table for every row. This is called Referential Integrity. Foreign key ensures referential integrity.

These ALTER TABLE statements are used to make changes to the existing supplier table (changes made from the previous query), adjusting the data type, and enforcing constraints to meet the desired structure and integrity requirements.

In SQL, the ALTER TABLE statement is used to modify the structure of an existing table. It allows you to add, modify, or drop columns, constraints, and other properties of a table. Here are some common operations performed using the

ALTER TABLE is a powerful and flexible command that allows you to make changes to the structure of a table after it has been created. It is important to use this statement carefully, especially in production environments, as modifications can impact existing data and application functionality. Always have a backup or ensure that you thoroughly understand the consequences of the changes you are making.

#SQL #Businessanaytics

Sharan Hritik