Integrity constraints are rules that help to maintain the accuracy and consistency of data in a database. For example, a simple integrity constraint in DBMS might state that all students must have a valid Roll Number. This would prevent someone from accidentally entering an invalid roll number into the database.
Integrity constraints can also be used to enforce relationships between tables. For example, if a student can only have one aadhaar number, then an integrity constraint can be used to ensure that only one aadhaar number is entered for each student.
A domain constraint is a restriction on the values that can be stored in a column. Strings, character, time, integer, currency, date etc. Are examples of the data type of domain constraints.
example, if you have a column for "age" domain integrity constraints in DBMS would ensure that only integer values can be entered into that column. This ensures that only valid data is entered into the database.
Entity integrity constraints would ensure that null values are not entered into any required columns. It states that primary key value can't be null. This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can't identify those rows
For example, if you have a column for "roll_number" an entity integrity constraint in DBMS would ensure that this column cannot contain any null values.
Syntax in MysqlCREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... PRIMARY KEY(column) );
A referential integrity constraint is a restriction on how foreign keys can be used. A foreign key is a column in one table that references a primary key in another table.
For example, let's say you have a table of Students and a table of Marks. The "roll_number" column in the Marks table would be a foreign key that references the "roll_number" column in the Students table.
Syntax in MysqlCREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... FOREIGN KEY (column) REFERENCES table_name1(column) );
A key constraint is a rule that defines how data in a column(s) can be stored in a table. A key is composed of one or more columns whose values uniquely identify each row in the table. There are several different types of key constraints in DBMS, each with its own specific purpose.
A unique key refers to a column or a set of columns that identify every record uniquely in a table. All the values in this key would have to be unique. values of a unique key won’t allow duplicate values and it is only capable of having one null value.
Syntax in MysqlCREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... UNIQUE (column) );
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... CONSTRAINT constraint_name UNIQUE(column) );
The primary key refers to a column of a table that helps us identify all the records uniquely present in that table. Any table can consist of only a single primary key constraint. values of a primary key won’t allow null value or a duplicate values.
Syntax in MysqlCREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... CONSTRAINT constraint_name PRIMARY KEY(column) );
We use a foreign key to establish relationships between two available tables. The foreign key would require every value present in a column/set of columns to match the referential table’s primary key. A foreign key helps us to maintain data as well as referential integrity.
Syntax in MysqlCREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... CONSTRAINT constraint_name FOREIGN KEY (column) REFERENCES table_name1(column) );
The composite key refers to a set of multiple attributes that help us uniquely identify every tuple present in a table. The attributes present in a set may not be unique whenever we consider them separately. Thus, when we take them all together, it will ensure total uniqueness.
Syntax in MysqlCREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... CONSTRAINT constraint_name UNIQUE(column,column) );
A super key refers to the set of all those keys that help us uniquely identify all the rows present in a table. It means that all of these columns present in a table that can identify the columns of that table uniquely act as the super keys.
The candidate keys refer to those attributes that identify rows uniquely in a table. In a table, we select the primary key from a candidate key. Thus, a candidate key has similar properties as that of the primary keys that we have explained above. In a table, there can be multiple candidate keys.
As we have stated above, any table can consist of multiple choices for the primary key. But, it can only choose one. Thus, all those keys that did not become a primary key are known as alternate keys.
The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a column it will allow only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
Syntax in MysqlCREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... CONSTRAINT constraint_name CHECK (condition) );
CREATE TABLE students (
ID int,
LastName varchar(30),
FirstName varchar(30),
Age int,
CONSTRAINT con_age CHECK (Age>=18)
);
The DEFAULT constraint is used to set a default value for a column. The default value will be added to all new records, if no other value is specified.
The DEFAULT constraint can also be used to insert system date, by using functions like GETDATE().
Syntax in MysqlCREATE TABLE table_name ( column1 datatype, column2 datatype DEFAULT 'value', column3 datatype DEFAULT GETDATE(), .... );
CREATE TABLE students (
ID int,
LastName varchar(30),
FirstName varchar(30),
Age int DEFAULT 18,
joining_date DEFAULT GETDATE()
);