Constraints In Microsoft SQL Server 2000 - Default Constraints (Page 8 of 8 )
Like all constraints, Default constraints become an integral part of the table definition. It defines what to do when a row is inserted with data for the column on which you have not determined a Default constraint. We can set the default value to be either as a literal string say default salary $3000 or as a one of the several system values such as GETDATE().
Look at Example 13:
USE Accounting
CREATE TABLE Shippers
(
ShipperID int IDENTITY NOT NULL PRIMARY KEY,
ShipperName varchar(30) NOT NULL,
Address
varchar(25) NOT NULL,
DateInSystem smalldatetime NOT
NULL
DEFAULT GETDATE()
)
Insert values into Shippers like follows:
Insert into Shippers
(shipperName)
Values
(‘UPS’)
When we run a select statement on Shippers:
Select
* from Shippers;
The default value is generated for DateInSystem column we if we did not supply the value because its default value is set to current system date.
ShipperID ShipperName DateInSystem
1 UPS 2003-12-25:23:26:00
Creating Default Constraint on an Existing Table
The Jobs table is altered so that, a character string default supplies a description (column job_desc) when the actual description is not entered explicitly. (Example 14)
ALTER TABLE Jobs
ADD CONSTRAINT
CnDef_jobs
DEFAULT
'New Position - title yet to be assigned’ FOR
job_desc;
Let us write a sample table (example 15) which looks more like the Employee table in the pubs database to test the skills we have learned so far.
CREATE TABLE
employee2
(emp_id varchar(7) CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
CONSTRAINT
CK_emp_id CHECK (emp_id LIKE'[A-Z][A-Z][A-Z][1-9][0-9][0-9][FM]'
or emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][FM]',fname varchar(20) NOT NULL,
minit char(1) NULL, lname varchar(30) NOT NULL, job_id smallint NOT NULL DEFAULT
1
REFERENCES jobs (job_id), job_lvl tinyint DEFAULT 10,
hire_date
datetime NOT NULL DEFAULT (getdate())
)
In the above table employee2 emp_id is the Primary key column. Each employee ID consists of three characters that represent the employee's initials, followed by a three-digit number ranging from 100 through 999 and then the employee's gender (M or F). A (hyphen) - is acceptable for the middle initial. The job_id column of the employee2 table for new hires refers to the job_id column of the Jobs table, so a Foreign key constraint is set up here. Default constraint is used for job_lvl column to set its value to 10 if no value is supplied at the time of insert. Similarly by default, the current system date is entered for the hire_date column.
So we have dealt with types of constraints in SQL Server 2000, its sub classes, creating tables with specific constraints, and modifying existing tables. Now the job is up to you to experiment with a few codes of your own to master this basic yet essential part of database designing and data integrity.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 116 
