MariaDB CONSTRAINT Keyword
ADD CONSTRAINT
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';
DROP CONSTRAINT
The MariaDB DROP CONSTRAINT command is used to delete a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint.
DROP a UNIQUE Constraint
To drop UC_Employee UNIQUE constraint from table called Employee, the below mentioned statement can be used:
ALTER TABLE Employee DROP INDEX UC_Employee; OR ALTER TABLE Employee DROP CONSTRAINT UC_Employee;
DROP a PRIMARY KEY Constraint
To drop PK_Employee PRIMARY KEY constraint from table called Employee, the statement is given below:
ALTER TABLE Employee DROP PRIMARY KEY;
DROP a FOREIGN KEY Constraint
To drop FK_Contact_Info FOREIGN KEY constraint from table called Contact_Info, the statement is given below:
ALTER TABLE Contact_Info DROP FOREIGN KEY FK_Contact_Info; OR ALTER TABLE Contact_Info DROP CONSTRAINT FK_Contact_Info;
DROP a CHECK Constraint
To drop CHK_Employee CHECK constraint from table called Employee, the statement is given below:
ALTER TABLE Employee DROP CONSTRAINT CHK_Employee;
DROP DEFAULT Constraint
To drop DEFAULT constraint from table called Employee, the statement is given below:
ALTER TABLE Employee ALTER City DROP DEFAULT; OR ALTER TABLE Employee ALTER COLUMN City DROP DEFAULT;
❮ MariaDB Keywords