Create Table Commands

Create table without primary key

CREATE TABLE employees

( employee_id INT NOT NULL,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50),

  salary MONEY

)

Create table with primary key

• Primary key doesn’t allow duplicate values.
• Primary key doesn’t allow Null values.
• PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT.
• By default primary key adds a clustered index.
• A table can have only one PRIMARY KEY 

We can apply primary key in following ways.

  1^st method to apply primary key

CREATE TABLE employees

( employee_id INT PRIMARY KEY,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50) NOT NULL,

  salary MONEY

)

2^nd method to apply primary key

CREATE TABLE employees

( employee_id INT constraint  employees_pk PRIMARY KEY,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50) NOT NULL,

  salary MONEY

)

^3rd method to apply primary key

CREATE TABLE employees

( employee_id INT,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50) NOT NULL,

  salary MONEY,

  CONSTRAINT employees_pk PRIMARY KEY (employee_id)

)

We can create Composite primary key by Combining Two columns in following ways 

CREATE TABLE employees

( last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50) NOT NULL,

  salary MONEY,

  CONSTRAINT employees_pk PRIMARY KEY (last_name, first_name)

)

We can  add primary after creating table by using following syntax.

ALTER TABLE employees

ADD CONSTRAINT employees_pk PRIMARY KEY (employee_id)

We can  add Composite primary after creating table by using following syntax.

ALTER TABLE employees

ADD CONSTRAINT employees_pk PRIMARY KEY (last_name, first_name)

We can drop primary key constraint by using following syntax.

ALTER TABLE employees

DROP CONSTRAINT employees_pk

We can add new column by using following syntax.

ALTER TABLE employees

  ADD last_name VARCHAR(50)

  ALTER TABLE employees

  ADD last_name VARCHAR(50),

      first_name VARCHAR(40)

We can alter column by using following syntax.

  ALTER TABLE employees

  ALTER COLUMN last_name VARCHAR(75) NOT NULL

We can drop column by using following syntax

  ALTER TABLE employees

  DROP COLUMN last_name

We can rename column name by using following syntax

  sp_rename 'employees.last_name', 'lname', 'COLUMN'

We can rename table name by using following syntax

  sp_rename 'employees', 'emps'

We can drop table by using following syntax

  DROP TABLE employees

  DROP TABLE Database2.dbo.suppliers