Primary key in SQL

-

A primary key is a field (or more) that identifies a single record (row) in a table.

For a key field value there is only one record.

Let's see an example, if we have a table with people data, the document number can be set as a primary key, it is a non-repeating value; There may be people with the same surname and first name, even the same address (father and son for example), but their document will always be different.

If we have the table "users", the name of each user can be established as a primary key, it is a value that is not repeated; There may be users with the same password, but their username will always be different.

We can set a field to be the primary key when the table is created or after it has been created. We are going to learn how to set it when creating the table. There is no single way to do it, for now we will see the simplest syntax.

We have our table "users" defined with 2 fields ("name" and "password").

The basic and general syntax is as follows:


 create table TABLENAME (

  TYPE FIELD,

  ...,

  TYPE FIELD,

  PRIMARY KEY (FIELD)

 );


What we do add, after the definition of each field, "primary key" and between parentheses, the name of the field that will be the primary key.

In the following example we define a primary key for our "users" table to ensure that each user will have a different and unique name:


 create table users (

  name varchar2 (20),

  key varchar2 (10),

  primary key (name)

 );


A table can only have one primary key. Any field (of any type) can be a primary key, it must meet the requirement that its values ​​are not repeated or null. Therefore, when defining a field as primary key, Oracle automatically converts it to "not null".


After having established a field as primary key, when entering records, Oracle controls that the values ​​for the field established as primary key are not repeated in the table; if they are repeated, it shows a message and the insertion is not carried out. In other words, if in our "users" table there is already a user named "juanperez" and we try to enter a new user with name "juanperez", a message appears and the "insert" instruction is not executed.


Likewise, if we perform an update, Oracle controls that the values ​​for the field set as the primary key are not repeated in the table, if it were, a message appears indicating that the primary key is violated and the update is not performed.


We can see the field set as the primary key of a table by performing the following query:


 select uc.table_name, column_name from user_cons_columns ucc

  join user_constraints uc

  on ucc.constraint_name = uc.constraint_name

  where uc.constraint_type = 'P' and

  uc.table_name = 'USERS';


We will not explain the previous query for the moment, we will only execute it; if the query returns an empty table, it means that the specified table has no primary key. The table name MUST be in uppercase, otherwise Oracle will not find it.

Comments

Post a Comment

Popular posts from this blog

Data Consistency and Inconsistency - What is Difference

-
Image
  Data Consistency: Definition 1: Data consistency means that the changes made to the different occurrences of data should be controlled and managed in such a way that all the occurrences have the same value for any specific data item. Data inconsistency leads to a number of problems, including loss of information and incorrect results. In the database approach, it is controlled because data is shared and consistency is controlled and maintained. Data Consistency Definition 2: Data consistency meaning is the validity, accuracy and usability of related data. It ensures that each user observes a consistent(Same) view of the data, including changes made by the user’s own transactions and transactions of other users. Types of Data Consistency: Point in time consistency Transaction consistency Application consistency Data Inconsistncy: Data Inconsistency Definition: Data inconsistency meaning is that different versions of the same data appear in d...
Read more

Cardinality in DBMS – max/minimum cardinality & examples

-
Image
  Logical Relationship in Tables: Database tables have a major advantage to bridge them with some logical relationship so to filter the records. These relationship allowed us to access one table data using another table records(via columns or rows). In other words logical filtration is called cardinality DBMS. Read Also: Relational Model in DBMS Definitions of Cardinality in DBMS: Definition 1: A number that represents, one instance of first entity(table) is related to how many instances of the second entity, is known as cardinality in dbms. Definition 2: Cardinality is a relationship or join/bridge between rows of  1 table to the rows of another table. via some column (e.g. User Table column {user_id} in another table Posts to filter the posts by user_id). Types of Cardinality in DBMS: There are three types of Cardinality in DBMS. one-to-one one-to-many many-to-one many-to-many Read Also: Degree of Relationship in DBMS ...
Read more

Relational Model in DBMS

-
Image
  Relational Model (RM) represents the database as a collection of relationships. A relationship is nothing more than a table of values. Each row of the table represents a collection of related data values. These rows of the table denote a real-world entity or relationship. The table name and column names are useful for interpreting the meaning of the values in each row. Data are represented as a set of relationships. In the relational model, the data are stored as tables. However, the physical storage of data is independent of how data is organized logically. Some popular relational database management systems are: DB2 and Informix Dynamic Server – IBM Oracle and RDB – Oracle SQL Server and Access – Microsoft Concepts of relational model in DBMS Attribute: Each column in a table. Attributes are the properties that define a relationship. e.g. Student_Rollno, NAME, etc. Tables – In the Relational model the relationships are saved in table format. It ...
Read more