Loading

SQL PRIMARY KEY Constraint

Submitted by: 
Language: 
Visitors have accessed this post 4862 times.


Every table must be identified with a unique key or the Primary Key. Primary Key uniquely identifies each record in the table. It makes searching of record faster.
A Primary Key cannot contain a NULL values and must be unique in every record.

Different DBMS have different implementations to set the primary keys, so let’s see each of them below.

SQL PRIMARY KEY Syntax


  1. CREATE TABLE TABLE_NAME
  2. (
  3. column_name1 data_type CONSTRAINT PRIMARY KEY,
  4. column_name2 data_type NULL/NOT NULL ,
  5. column_name3 data_type NULL/NOT NULL,....)


Example For Oracle/SQL Server

  1. CREATE TABLE Users
  2. (
  3. ID INT NOT NULL PRIMARY KEY,
  4. Firstname VARCHAR(255) NOT NULL,
  5. Lastname VARCHAR(255),
  6. Salary FLOAT NOT NULL,
  7. DeptID INT NOT NULL)

Users

ID Firstname Lastname Salary DeptID

Example For MySQL

  1. CREATE TABLE Users
  2. (
  3. ID INT NOT NULL,
  4. Firstname VARCHAR(30) NOT NULL,
  5. Lastname VARCHAR(30),
  6. Salary FLOAT NOT NULL,
  7. DeptID INT NOT NULL,
  8. PRIMARY KEY (ID)
  9. )

Users

ID Firstname Lastname Salary DeptID

We have set the primary key for the column ID in the users table

ALTER TABLE method

Example

  1. ALTER TABLE Users
  2. ADD PRIMARY KEY (column_name)

Users

ID Firstname Lastname Salary deptnumber

TO REMOVE PRIMARY KEY

Example

  1. ALTER TABLE Users
  2. DROP PRIMARY KEY



Add new comment

(If you're a human, don't change the following field)
Your first name.
(If you're a human, don't change the following field)
Your first name.
(If you're a human, don't change the following field)
Your first name.

Filtered HTML

  • You may insert videos with [video:URL]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.