Primary key in sql - sql - sql tutorial - learn sql




 primary key in sql
  • A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself, or it can be an artificial field.
  • Setting of primary keys is an important factor in database design, as the choices of primary key can have significant impact on the performance, usability, and the extensibility of the entire database.
  • A primary key can consist of one or more columns on a table. When multiple columns are used as a primary key, they are called a composite key.
  • In designing the primary key, we want to make sure we use as few columns as possible. This is for both storage and performance reasons.
  • In terms of performance, fewer columns means that the database can process through the primary key faster since there is less data, and it also means that table joins will be faster since primary keys, together with foreign keys, are often used as the joining condition.
  • A primary key cannot be NULL, as it does not make sense to use the NULL value to uniquely identify a record. Therefore, the column that is set as a primary key or as part of a primary key cannot be NULL.
  • Primary keys can be specified either
    • when the table is created (using CREATE TABLE) or
    • when changing the existing table structure (using ALTER TABLE).
  • The examples for specifying a primary key when creating a table.
  • Please note when primary key is specified upon table creation, the column(s) that are set to be the primary key are automatically set to NOT NULL.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Primary Key on One Column:

MySQL:

CREATE TABLE wikitechy Customer 
(SID integer, 
Last_Name varchar(30), 
First_Name varchar(30), 
PRIMARY KEY (SID));

Oracle:

CREATE TABLE wikitechy Customer 
(SID integer PRIMARY KEY, 
Last_Name varchar(30), 
First_Name varchar(30));

SQL Server:

CREATE TABLE wikitechy Customer 
(SID integer PRIMARY KEY, 
Last_Name nvarchar(30), 
First_Name nvarchar(30));
  • In the examples above, SID column is specified as the primary key.
  • Below is an example of specifying the primary key on a single column using ALTER TABLE. The same SQL statement can be used in MySQL, Oracle, and SQL Server.
ALTER TABLE wikitechy Customer ADD PRIMARY KEY (SID);
  • Note: Before using the ALTER TABLE command to add a primary key, you'll need to make sure that the field is defined as 'NOT NULL' - in other words, NULL cannot be an accepted value for that field.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Primary Key on Multiple Columns

  • In the examples below, the composite primary key consists of two columns, Birth_Date and Social_Security_Number.

MySQL:

CREATE TABLE Customer_Composite_Key 
(Last_Name varchar(30), 
First_Name varchar(30), 
Birth_Date datetime, 
Social_Security_Number integer, 
PRIMARY KEY (Birth_Date, Social_Security_Number));

Oracle:

CREATE TABLE Customer_Composite_Key 
(Last_Name varchar(30), 
First_Name varchar(30), 
Birth_Date date, 
Social_Security_Number integer, 
PRIMARY KEY (Birth_Date, Social_Security_Number));

SQL Server:

CREATE TABLE Customer_Composite_Key 
(Last_Name narchar(30), 
First_Name narchar(30), 
Birth_Date datetime, 
Social_Security_Number integer, 
PRIMARY KEY (Birth_Date, Social_Security_Number));
  • Below is an example of using ALTER TABLE to add a primary key that consists of two columns. The command is the same in MySQL, Oracle, and SQL Server.
ALTER TABLE Customer_Composite_Key ADD PRIMARY KEY (Birth_Date, Social_Security_Number);
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Primary key in SQL

Primary key in SQL

Difference between Primary and Foreign Key

Difference between Primary and Foreign Key

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Primary key in sql