Primary Key
Creating Primary key while Creating the table itself
CREATE TABLE Employees (
ID INT PRIMARY KEY,
NAME TEXT,
AGE INT,
ADDRESS CHAR(50)
);
To add the constraint Primary key to an already existing column in the table
ALTER TABLE Employees
ADD PRIMARY KEY (ID);
Composite Primary Key
We can also use multiple columns to define a primary key. Such key is known as composite key.
CREATE TABLE Customers (
CustomerID INT,
StoreID INT,
CONSTRAINT pk_CustomerID_StoreID PRIMARY KEY (CustomerID,StoreID)
Foreign Key
While creating a table
MySQL
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
SQL FOREIGN KEY on ALTER TABLE
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE child_table
ADD FOREIGN KEY (fk_column)
REFERENCES parent_table (parent_key_column)
DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
UIQUE
While creating table
CREATE TABLE table_name (
column1 data_type UNIQUE,
column2 data_type,
column3 data_type,
....
)
Adding a Unique Constraint to an Existing Table
ALTER TABLE table_name
ADD UNIQUE (column1, column2, ...);
Dropping a Unique Constraint
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
NOT NULL
Creating a table with NOT NULL
While creating a table
CREATE TABLE Employees (
ID int NOT NULL,
Name varchar(255) NOT NULL,
Age int,
Address varchar(255)
);
Adding NOT NULL to an existing table
ALTER TABLE Employees
MODIFY Address varchar(255) NOT NULL;
To remove
undeclared attributes as NOT NULL will go back to default settings which is null in this case
alter table products
alter column brand_id smallint;
CHECK
CREATE TABLE table_name (
column1 datatype CONSTRAINT constraint_name CHECK (condition),
column2 datatype,
...
);
If you need to apply the CHECK constraint on multiple columns, use the following syntax:
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...,
CONSTRAINT constraint_name CHECK (condition)
);
EXAMPLES
CREATE TABLE Employees (
ID int NOT NULL,
Age int,
Salary int CHECK (Salary>0),
);
Multiple Columns
CREATE TABLE Employees (
ID int NOT NULL,
Age int,
Salary int,
CONSTRAINT CHK_Person CHECK (Age>=18 AND Salary>=0)
);
Alter table
ALTER TABLE Employees
ADD CONSTRAINT CHK_EmployeeAge CHECK (Age >= 21 AND Age <= 60);