I’ve been discussing general information about database/sql development but l’m now going to concentrate on one flavour of SQL called TSQL, microsoft version of SQL. TSQL is used by all microsoft SQL servers, so knowing TSQL in general will allow you to program in all microsoft SQL servers with minimal changes. I’m going to base my tutorials on Microsoft SQL Server 2008 R2. Which means, anything l’m going to talk about should work 100% perfect in Microsoft SQL Server 2008 R2.
This tutorial is about the different types of constraints that can be imposed on data using TSQL. I’ll take each constraint separate and give as much information and the query to implement that constraint on the server.
Assume we have a table called student and it has the attributes sid, firstname, lastname, rm#,dob.
The first constraint I’m going to discuss is the Primary key constraint.
Create table with primary key during creation time.
CREATE TABLE student
(sid int primary key,
firstname varchar(50),
rm# varchar(50),
dob date)
Add primary key to an existing table student. There can exist only one primary key per table. If the table does not have a primary key, then just add the primary key. if the table already has a primary key, then that key has to be dropped before adding the new primary key.
Add sid as primary key to an existing student table with no primary key present in it.
ALTER TABLE student
ADD CONSTRAINT P_K PRIMARY KEY(sid)
P_K is just any name you want to call the primary key constraint.If you add the primary key during table creation, the server will automatically create a name for that primary key constraint. To remove that primary key from the table, remove that constraint.
Remove a primary key constraint from a table
ALTER TABLE student
DROP CONSTRAINT P_K
Next, lets add a foreign key to a table during creation. Assume the student table depends on another table person with pid as the primary key. If the student has a pid colum which references the pid column of the person table, then we can create the foreign key in the student table as shown below. Remember a foreign key links two tables together.
CREATE TABLE student
(sid int primary key,
pid int foreign key references person(pid),
firstname varchar (50),
lastname varchar(50),
dob date)
The column in the student table that is serving as the foreign key don’t have to have the same name as the column it references in the person table. Also, the person table must already exist before creating the student table to reference it. During insertion into the student table, the foreign key column can only take values that are already available in the column of the person table it references. A foreign key can reference a primary key or a unique key of another table. We’ll look at unique key soon.
Assume the student and person tables already exist but the foreign key constraint was not added to the pid column of the student table, then we have to add that constraint like this,
ALTER TABLE student
ADD CONSTRAINT f_k FOREIGN KEY(pid) references person(pid)
The f_k is just any name you want to used to identify the foreign key constraint. If you want to remove the foreign key constraint, you do it like this,
ALTER TABLE student
DROP CONSTRAINT f_k.
Once again, if you create the foreign key at the time of creating the table, the server will automatically give a name to that foreign key constraint and you can use it to drop it later.
Note that you can not delete the table that is referenced by another table. If you have a table with foreign key in it and you want a record in that table to be deleted or update as soon as the record matching the foreign key in the table being referenced is deleted or updated, you do it like this,
CREATE TABLE student(sid int primary key,
pid int foreign key references person(pid),
firstname varchar (50), lastname varchar(50),
dob date)
on delete cascade on update cascade
if you don’t want it to be updated or deleted when the main table is updated or delete, you can use the ‘no action’ instead of the cascade
The next constraint is Unique key.
Add a unique key during table creation is done like this,
CREATE TABLE student(sid int primary key,
firstname varchar (50),
lastname varchar(50),
dob date
ssn varchar(20) unique
)
To add the unique key constraint after the table has been created, you proceed like this,
ALTER TABLE student
ADD CONSTRAINT U_K unique (ssn)
To remove the Unique key, Just do as we did for primary and foreign constraints.
The next constraint is check: This is used to make sure that the domain integrity for data being added to a particular column is maintained. Assuming in our student table, there is another column for tuition and we decide that any student’s tuition can not be more than 1000. We have to put the check constraint on the tuition column to avoid bad data being entered in this column.
Add check constraint during table creation for tuition.
CREATE TABLE student(sid int primary key,
firstname varchar (50),
lastname varchar(50),
dob date
ssn varchar(20) unique
tuition money check (tuition <= 1000)
)
add the check constraint on the student table after it has been created without one.
ALTER TABLE student
ADD CONSTRAINT C_K CHECK (tuition <=1000 )
YOU can remove the constraint just like we did for other constraints so far.
The next constraint is default: This simply means you want to have a default value inserted into a column if that column was not given a value during insertion. Assuming in our student table, there is column room_number and you want to assign all students with unknown room number to rm101. You can set rm101 as the default. If you don’t do this, then if the room_number column is given no value during insertion. it will be given NULL by the server but lets say you want the students to be assigned to rm101 by default. Remember NULL is not a value. It doesn’t even mean blank. Actually, blank is a value. NULL means unknown.
add rm101 as default room_number for student table during creation
CREATE TABLE student(sid int primary key,
firstname varchar (50),
lastname varchar(50),
dob date
ssn varchar(20) unique,
room_number varchar(50) default rm101
)
add rm101 as default room_number for student table after the table has been created without one.
ALTER TABLE student
ADD CONSTRAINT d_f default rm101 for room_number.
That’s all the constraints I know about, I’ll be giving tutorial on many ways to insert records into the table after they are created and how to copy content and structure of another table into another table using the insert statement. Check it out, coming soon.
