SQL Primary Key Constraint With Example

Constraints Definition:

SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.

Constraints provide a standard mechanism to maintain the accuracy and integrity of the data inside a database table.


2. PRIMARY KEY

A PRIMARY KEY is a combination of a NOT NULL and UNIQUE. A Primary key constraint is applied for uniquely identifying rows in a table.

It cannot contain Null values and rest of table data should be unique.

While creating a table if we do not specify a name to the constraint, sql server automatically assigns a name to the constraint.

A PRIMARY KEY must contain unique value and it must not contain null value. Usually Primary Key is used to index the data inside the table.

SQL Primary Key in a table have following three special attributes,

  • The NOT NULL attribute is automatic active.
  • The data across the column must be unique.
  • Defines column as a mandatory column.

Example: Defined at Column level

Syntax:


CREATE TABLE table_name(
    column_name datatype[(size)] [ NULL | NOT NULL ] PRIMARY KEY,
    column_name datatype[(size)] [ NULL | NOT NULL ] PRIMARY KEY,
    ....
);	


SQL> CREATE TABLE std_info(
    no NUMBER(3,0) PRIMARY KEY,
    name VARCHAR(30),
    address VARCHAR(70),
    contact_no VARCHAR(12)
);

---------------------------
Table created.

Example: Defined at Table level

Syntax:

CREATE TABLE table_name(
    column_name datatype[(size)] [ NULL | NOT NULL ],
    column_name datatype[(size)] [ NULL | NOT NULL ],
    ...,
    PRIMARY KEY ( column_name, ... ),
    ...
);	

Example:

SQL> CREATE TABLE std_info(
    no NUMBER(3,0),
    name VARCHAR(30),
    address VARCHAR(70),
    contact_no VARCHAR(12),
    PRIMARY KEY(no)
);

-------------------------
Table created.

Read Also: