PRIMARY KEY

SQL PRIMARY KEY Keyword

The PRIMARY KEY constraint uniquely identifies each record in a table. A table can have only one primary key, which may consist of one or multiple columns.

SQL PRIMARY KEY on CREATE TABLE Syntax

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype,
    PRIMARY KEY (column1)
);

CREATE TABLE table_name (

    column1 datatype NOT NULL,

    column2 datatype,

    PRIMARY KEY (column1)

);

SQL PRIMARY KEY on CREATE TABLE Example

This query creates a new table Rooms with room_id as the primary key.

CREATE TABLE Rooms (
    room_id INTEGER NOT NULL,
    room_type TEXT,
    PRIMARY KEY (room_id)
);

CREATE TABLE Rooms (

    room_id INTEGER NOT NULL,

    room_type TEXT,

    PRIMARY KEY (room_id)

);

SQL PRIMARY KEY with Column-Level Declaration Syntax

CREATE TABLE table_name (
    column1 datatype NOT NULL PRIMARY KEY,
    column2 datatype
);

CREATE TABLE table_name (

    column1 datatype NOT NULL PRIMARY KEY,

    column2 datatype

);

SQL PRIMARY KEY with Column-Level Declaration Example

This query creates a Buildings table with building_id set as the primary key.

CREATE TABLE Buildings (
    building_id INTEGER NOT NULL PRIMARY KEY,
    building_name TEXT
);

CREATE TABLE Buildings (

    building_id INTEGER NOT NULL PRIMARY KEY,

    building_name TEXT

);

SQL PRIMARY KEY on Multiple Columns Syntax

CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype NOT NULL,
    column3 datatype,
    CONSTRAINT pk_name PRIMARY KEY (column1, column2)
);

CREATE TABLE table_name (

    column1 datatype NOT NULL,

    column2 datatype NOT NULL,

    column3 datatype,

    CONSTRAINT pk_name PRIMARY KEY (column1, column2)

);

SQL PRIMARY KEY on Multiple Columns Example

This query creates a LabRooms table with a composite primary key of room_id and floor_no.

CREATE TABLE LabRooms (
    room_id INTEGER NOT NULL,
    floor_no INTEGER NOT NULL,
    equipment TEXT,
    CONSTRAINT pk_LabRoom PRIMARY KEY (room_id, floor_no)
);

CREATE TABLE LabRooms (

    room_id INTEGER NOT NULL,

    floor_no INTEGER NOT NULL,

    equipment TEXT,

    CONSTRAINT pk_LabRoom PRIMARY KEY (room_id, floor_no)

);

SQL PRIMARY KEY on ALTER TABLE Syntax

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

ALTER TABLE table_name

ADD PRIMARY KEY (column_name);

SQL PRIMARY KEY on ALTER TABLE Example

This query adds a primary key to the Rooms table on the room_id column.

ALTER TABLE Rooms
ADD PRIMARY KEY (room_id);

ALTER TABLE Rooms

ADD PRIMARY KEY (room_id);

Note: This will not work in TechKubo (SQLite), as SQLite does not support adding a primary key after table creation.

SQL PRIMARY KEY on ALTER TABLE with Constraint Name Syntax

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2);

ALTER TABLE table_name

ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2);

SQL PRIMARY KEY on ALTER TABLE with Constraint Name Example

ALTER TABLE LabRooms
ADD CONSTRAINT pk_LabRoom PRIMARY KEY (room_id, floor_no);

ALTER TABLE LabRooms

ADD CONSTRAINT pk_LabRoom PRIMARY KEY (room_id, floor_no);

Note: Not supported in TechKubo (SQLite). In SQLite, you cannot add primary keys to an existing table using ALTER TABLE.

SQL DROP PRIMARY KEY Constraint Syntax

ALTER TABLE table_name
DROP PRIMARY KEY;

ALTER TABLE table_name

DROP PRIMARY KEY;

SQL DROP PRIMARY KEY Constraint Example

ALTER TABLE Rooms
DROP PRIMARY KEY;

ALTER TABLE Rooms

DROP PRIMARY KEY;

Note: This command is not supported in TechKubo (SQLite). You must recreate the table if you want to remove a primary key in SQLite.

SQL PRIMARY KEY Labs