SQL - Constraints
Published: 2023-12-01 17:19:03

<u>**_Primary Key_**</u>

**_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)

```

**_<u>Foreign Key</u>_**

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;
```
**_<u>UIQUE</u>_**

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, ...);
```
**_<u>Dropping a Unique Constraint</u>_**

```
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
```

**_<u>NOT NULL</u>_**

**_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;
```


**_<u>CHECK</u>_**

```
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);
```