Constraint

Primary Key : A primary key is a column or a group of columns that uniquely identifies each row in a table. You create a primary key for a table by using the PRIMARY KEY constraint

If the primary key consists of only one column, you can define use PRIMARY KEY constraint as a column constraint:

create table sales (sale_id int primary key identity,sale_date datetime,sale_amount decimal(5,2),quantity int)



In case the primary key has two or more columns, you must use the PRIMARY KEY constraint as a table constraint:

create table participant(sale_id int,customer_id int,primary key(sale_id,customer_id))


Each table can contain only one primary key. All columns that participate in the primary key must be defined as NOT NULL. SQL Server automatically sets the NOT NULL constraint for all the primary key columns if the NOT NULL constraint is not specified for these columns.

How to Add Primary key on existing table:

create table event(event_id int not null,event_name varchar(500),event_date date,duration decimal(5,2))

alter table event add primary key(event_id)



Foreign Key:

The foreign key constraint ensures referential integrity. It means that you can only insert a row into the child table if there is a corresponding row in the parent table.


FOREIGN KEY (foreign_key_columns)
    REFERENCES parent_table(parent_key_columns)
    ON UPDATE action 
    ON DELETE action;

If you update one or more rows in the parent table, you can set one of the following actions:

  • ON UPDATE NO ACTION: SQL Server raises an error and rolls back the update action on the row in the parent table.
  • ON UPDATE CASCADE: SQL Server updates the corresponding rows in the child table when the rows in the parent table are updated.
  • ON UPDATE SET NULL: SQL Server sets the rows in the child table to NULL when the corresponding row in the parent table is updated. Note that the foreign key columns must be nullable for this action to execute.
  • ON UPDATE SET DEFAULT: SQL Server sets the default values for the rows in the child table that have the corresponding rows in the parent table updated.

If you delete one or more rows in the parent table, you can set one of the following actions:

  • ON DELETE NO ACTION: SQL Server raises an error and rolls back the delete action on the row in the parent table.
  • ON DELETE CASCADE: SQL Server deletes the rows in the child table that is corresponding to the row deleted from the parent table.
  • ON DELETE SET NULL: SQL Server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must be nullable.
  • ON DELETE SET DEFAULT SQL Server sets the rows in the child table to their default values if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must have default definitions. Note that a nullable column has a default value of NULL if no default value specified.

By default, SQL Server appliesON DELETE NO ACTION if you don’t explicitly specify any action.

Syntax:

create table department(dept_id int primary key identity,dept_name varchar(50));

create table employee(emp_id int primary key identity,emp_name varchar(100),salary decimal(5,2),dept_id int,constraint fk foreign key(dept_id) 
references department(dept_id))

Not Null Constraint:
The SQL Server NOT NULL constraints simply specify that a column must not assume the NULL

create table not_null_constraint
(event_id int not null,event_name varchar(500),event_date date,duration decimal(5,2))


Unique Constraint: SQL Server UNIQUE constraints allow you to ensure that the data stored in a column, or a group of columns, is unique among the rows in a table.

Behind the scenes, SQL Server automatically creates a UNIQUE index to enforce the uniqueness of data stored in the columns that participate in the UNIQUE constraint. Therefore, if you attempt to insert a duplicate row, SQL Server rejects the change and returns an error message stating that the UNIQUE constraint has been violated.

If you don’t specify a separate name for the UNIQUE constraint, SQL Server will automatically generate a name for it.

To assign a particular name to a UNIQUE constraint, you use the CONSTRAINT keyword as follows:

CREATE TABLE employee_uniq(
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    CONSTRAINT unique_email UNIQUE(email));

Add unique constraint on existing table:

ALTER TABLE employee_uniq
ADD CONSTRAINT unique_email UNIQUE(email);

ADD Unique Constraint on multiple column:

CREATE TABLE employee_COL(
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    UNIQUE(FIRST_NAME,EMAIL));

Check Constraint:

1.
CREATE TABLE products_check
(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) CHECK(unit_price > 0)

2.
CREATE TABLE products_checks(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) CONSTRAINT positive_price CHECK(unit_price > 0)
);

The CHECK constraints reject values that cause the Boolean expression evaluates to FALSE.

Because NULL evaluates to UNKNOWN, it can be used in the expression to bypass a constraint.

Check constraint on multiple column:

CREATE TABLE products_checks(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
discounted_price dec(10,2),
    unit_price DEC(10,2) CONSTRAINT positive_price CHECK(unit_price > 0),
CHECK(discounted_price > unit_price),
CHECK(discounted_price > 0 AND discounted_price > unit_price)
);

Drop Check Constraint:
ALTER TABLE products_checks
DROP CONSTRAINT positive_price;





Subscribe to: Posts (Atom)