SQl CONSTRAINTS are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.
A NULL is not the same as no data.
NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column. It enforces a column to contain a proper value. One important point to note about NOT NULL constraint is that it cannot be defined at table level.
CREATE TABLE Student(stud_id INT NOT NULL, stud_ame VARCHAR(60))
The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.
CREATE TABLE CUSTOMERS( id INT NOT NULL, name VARCHAR (20) NOT NULL, salary DECIMAL (18, 2) DEFAULT 5000.00, PRIMARY KEY (id) )
UNIQUE constraint ensures that a field or column will only have unique values.
A UNIQUE constraint field will not have duplicate data.
UNIQUE constraint can be applied at column level or table level.
CREATE TABLE student(stud_id INT NOT NULL UNIQUE, stud_name VARCHAR(60))
A primary key is a field in a table which uniquely identifies each row/record in a database table.
Primary keys must contain unique values.
A primary key column cannot have NULL values.
CREATE TABLE student (stud_id INT PRIMARY KEY, stud_name VARCHAR(60) NOT NULL,)
A foreign key is a key used to link two tables together. This is sometimes called a referencing key.
Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.
The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.
In customer_detail table, c_id is the primary key which is set as foreign key in order_detail table. The value that is entered in c_id which is set as foreign key in order_detail table must be present in customer_detail table where it is set as primary key. This prevents invalid data to be inserted into c_id column of order_detail table.
CREATE TABLE order_detail(order_id INT PRIMARY KEY, order_name VARCHAR(60) NOT NULL, c_id INT FOREIGN KEY REFERENCES customer_detail(c_id))
The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered into the table.
CREATE TABLE stud(sid int NOT NULL CHECK(s_id > 0), sname VARCHAR(60) NOT NULL)
Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.
For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command:
ALTER TABLE customer DROP CONSTRAINT cust_pk