/* This script creates a table with multiple constraints, some defaults
** and a self-referencing foreign key. It then displays
** the constraints on the table.
*/
USE pubs
GO
if exists (select * from sysobjects where name = 'employees' and type='U')
DROP TABLE employees
GO
CREATE TABLE employees
(
emp_id int NOT NULL PRIMARY KEY DEFAULT 1000
CHECK (emp_id BETWEEN 0 AND 1000),
emp_name varchar(30) NULL DEFAULT NULL CONSTRAINT no_nums
CHECK (emp_name NOT LIKE '%[0-9]%'),
mgr_id int NOT NULL DEFAULT (1) REFERENCES
employees(emp_id),
entered_date datetime NOT NULL CHECK (entered_date >=
CONVERT(char(10), CURRENT_TIMESTAMP, 102))
CONSTRAINT def_today DEFAULT (CONVERT(char(10), GETDATE(), 102)),
entered_by int NOT NULL DEFAULT SUSER_ID()
CHECK (entered_by IS NOT NULL),
CONSTRAINT valid_entered_by CHECK (entered_by=SUSER_ID() AND
entered_by <> emp_id),
CONSTRAINT valid_mgr CHECK (mgr_id <> emp_id OR emp_id=1),
CONSTRAINT end_of_month CHECK (DATEPART(DAY, GETDATE()) < 28)
)
GO
EXEC sp_helpconstraint employees
GO
