CLASS 2 - working with DATABASE, What is Constraints ?what are they?
Creating, altering and dropping a database
1.Graphically using
SQL Server Management Studio (SSMS) or
2. Using a Query
2. Using a Query
Whether, you create a database graphically using the designer or,
using a query, the following 2 files gets generated.
.MDF file - Data File (Contains actual data)
.LDF file - Transaction Log file (Used to recover the database)
.MDF file - Data File (Contains actual data)
.LDF file - Transaction Log file (Used to recover the database)
To alter a database,
once it's created
Alter database DatabaseName Modify Name = NewDatabaseName
Alternatively, you can also use system stored procedure
Execute sp_renameDB 'OldDatabaseName','NewDatabaseName'
To Delete or Drop a database
Drop Database DatabaseThatYouWantToDrop
Dropping a database, deletes the LDF and MDF files.
You cannot drop a database, if it is currently in use. You get an error stating - Cannot drop database "NewDatabaseName" because it is currently in use. So, if other users are connected, you need to put the database in single user mode and then drop the database.
Alter Database DatabaseName Set SINGLE_USER With Rollback Immediate
With Rollback Immediate option, will rollback all incomplete transactions and closes the connection to the databas
Alter database DatabaseName Modify Name = NewDatabaseName
Alternatively, you can also use system stored procedure
Execute sp_renameDB 'OldDatabaseName','NewDatabaseName'
To Delete or Drop a database
Drop Database DatabaseThatYouWantToDrop
Dropping a database, deletes the LDF and MDF files.
You cannot drop a database, if it is currently in use. You get an error stating - Cannot drop database "NewDatabaseName" because it is currently in use. So, if other users are connected, you need to put the database in single user mode and then drop the database.
Alter Database DatabaseName Set SINGLE_USER With Rollback Immediate
With Rollback Immediate option, will rollback all incomplete transactions and closes the connection to the databas
To add a foreign key reference using a query
Alter table tblPerson
add constraint tblPerson_GenderId_FK FOREIGN KEY (GenderId) references tblGender(ID)
The general formula is here
Alter table ForeignKeyTable add constraintForeignKeyTable_ForiegnKeyColumn_FK
FOREIGN KEY (ForiegnKeyColumn) references PrimaryKeyTable (PrimaryKeyColumn)
Alter table tblPerson
add constraint tblPerson_GenderId_FK FOREIGN KEY (GenderId) references tblGender(ID)
The general formula is here
Alter table ForeignKeyTable add constraintForeignKeyTable_ForiegnKeyColumn_FK
FOREIGN KEY (ForiegnKeyColumn) references PrimaryKeyTable (PrimaryKeyColumn)
Altering an existing column to add
a default constraint:
ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME }
DEFAULT { DEFAULT_VALUE } FOR { EXISTING_COLUMN_NAME }
Adding a new column, with default value, to an existing table:
ALTER TABLE { TABLE_NAME }
ADD { COLUMN_NAME } { DATA_TYPE } { NULL | NOT NULL }
CONSTRAINT { CONSTRAINT_NAME } DEFAULT { DEFAULT_VALUE }
The following command will add a default constraint, DF_tblPerson_GenderId.
ALTER TABLE tblPerson
ADD CONSTRAINT DF_tblPerson_GenderId
DEFAULT 1 FOR GenderId
ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME }
DEFAULT { DEFAULT_VALUE } FOR { EXISTING_COLUMN_NAME }
Adding a new column, with default value, to an existing table:
ALTER TABLE { TABLE_NAME }
ADD { COLUMN_NAME } { DATA_TYPE } { NULL | NOT NULL }
CONSTRAINT { CONSTRAINT_NAME } DEFAULT { DEFAULT_VALUE }
The following command will add a default constraint, DF_tblPerson_GenderId.
ALTER TABLE tblPerson
ADD CONSTRAINT DF_tblPerson_GenderId
DEFAULT 1 FOR GenderId
Drop Constraint
ALTER TABLE { TABLE_NAME
}
DROP CONSTRAINT { CONSTRAINT_NAME }
DROP CONSTRAINT { CONSTRAINT_NAME }
What is constraint?
A constraint is a property that is assigned to
a column or group of columns to prevent incorrect or corrupt data from entering
into the tables. These constraints ensure the accuracy and Reliability of the
data into the tables
1. Primary key
2. Foreign key
3. Unique key
4. Not Null
5. Check constraint
6. Default
1. What is primary key?
Primary key is used to uniquely identify a row
in a table. A table can have only one primary key. Primary keys don’t allow
null values. When we create primary key ,cluster index is created automatically.
SQL PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:
MySQL:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
)
2. What is
foreign key?
A Foreign key is a key used to link
two tables together. A foreign key is a field in one table that refers to the
primary key in another table. The table containing the foreign key is called
child table, and the table containing the primary key is called referenced or
parent table
SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
SQL FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
3. Unique key constraint?
The UNIQUE constraint ensures that
all values in a column are different.
SQL UNIQUE Constraint on CREATE TABLE
The following SQL creates a UNIQUE constraint on the "ID" column when the "Persons" table is created:
SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
MySQL:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
4.NOT NULL
By default, a column can hold NULL values.
The NOT NULL constraint enforces a column to NOT accept NULL values.
This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
SQL NOT NULL on CREATE TABLE
The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created:
Example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
SQL NOT NULL on ALTER TABLE
To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use the following SQL:
ALTER TABLE Persons
MODIFY Age int NOT NULL;
5. Primary key VS Unique key?
1. There can be only one primary key
possible in a table but there can be many unique keys possible in a table.
2. Primary key does not allow null
values but a unique key allow one null value
3. When a primary key create a
clustered index is made by default but if an unique key is created, a
non-clustered index is created default
6.SQL CHECK Constraint
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 single column it allows 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.
SQL CHECK on CREATE TABLE
The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that you can not have any person below 18 years:
SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age>=18)
);
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
SQL CHECK on ALTER TABLE
To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CHECK (Age>=18);
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
DROP a CHECK Constraint
To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
7.SQL DEFAULT Constraint
The DEFAULT constraint is used to provide a default value for a column.
The default value will be added to all new records IF no other value is specified.
SQL DEFAULT on CREATE TABLE
The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created:
My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
CREATE TABLE Orders (
ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE()
);
SQL DEFAULT on ALTER TABLE
To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:
SQL Server:
ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR City;
DROP a DEFAULT Constraint
To drop a DEFAULT constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
Delete VS Truncate?
1. Delete command maintained logs
file of each delete row but truncate command do not maintain the log file.
2. Truncate command is much faster
than delete command
3. we can use where clause in case
of delete command to delete particular row but in
truncate use to delete all the rows in a table since where clause is not work
with truncate command
4. Triggers is fired in case of
delete command only and they are not fired when truncate command is used
5. Truncate command resets the
identity property its initial value where as delete command do not resets the
identity property
6. Delete is DML command and truncate
is DDL command.
Comments