MariaDB ADD CONSTRAINT Keyword
The MariaDB ADD CONSTRAINT command is used to create a constraint after a table is already created. It is mostly used in conjunction with ALTER statement.
ADD UNIQUE Constraint
To enforce UNIQUE constraint on Salary column of table called Employee, the statement is given below:
ALTER TABLE Employee ADD UNIQUE (Salary);
To provide a name to UNIQUE constraint, and to define a UNIQUE constraint on multiple columns (say Age and Salary), the statement is given below:
ALTER TABLE Employee ADD CONSTRAINT UC_Employee UNIQUE (Age, Salary);
ADD PRIMARY KEY Constraint
To enforce PRIMARY KEY constraint on EmpID column of table called Employee, the statement is given below:
ALTER TABLE Employee ADD PRIMARY KEY (EmpID);
To provide a name to PRIMARY KEY constraint, and to define a PRIMARY KEY constraint on multiple columns (say EmpID and Name), the statement is given below:
ALTER TABLE Employee ADD CONSTRAINT PK_Employee PRIMARY KEY (EmpID, Name);
ADD FOREIGN KEY Constraint
To enforce FOREIGN KEY constraint on EmpID column of table called Contact_Info, the statement is given below:
ALTER TABLE Contact_Info ADD FOREIGN KEY (EmpID) REFERENCES Employee(EmpID);
To provide a name to FOREIGN KEY constraint, and to define a FOREIGN KEY constraint on multiple columns, the statement is given below:
ALTER TABLE Contact_Info ADD CONSTRAINT FK_Contact_Info FOREIGN KEY (EmpID) REFERENCES Employee(EmpID);
The statement given below demonstrates how to enforce a FOREIGN KEY constraint on multiple columns (EmpID and PersonName). Please note that, the Employee table must have PRIMARY KEY constraint on these columns - EmpID and Name.
ALTER TABLE Contact_Info ADD CONSTRAINT FK_Contact_Info FOREIGN KEY (EmpID, PersonName) REFERENCES Employee(EmpID, Name);
ADD CHECK Constraint
To enforce CHECK constraint on Age column of table called Employee, the statement is given below:
ALTER TABLE Employee ADD CHECK (Age >= 21);
To provide a name to CHECK constraint, and to define a CHECK constraint on multiple columns (say Age and City), the statement is given below:
ALTER TABLE Employee ADD CONSTRAINT CHK_Employee CHECK (Age >= 21 AND City = 'London');
ADD DEFAULT Constraint
To enforce DEFAULT constraint on City column of table called Employee, the statement is given below:
ALTER TABLE Employee ALTER City SET DEFAULT 'London'; OR ALTER TABLE Employee ALTER COLUMN City SET DEFAULT 'London';
❮ MariaDB Keywords