/* This script creates a table with multiple constraints and a
** self-referencing foreign key. It then displays
** the constraints on the table.
*/
USE pubs
GO
if exists (select * from sysobjects where name='SHOW_ERROR' and type='U')
DROP TABLE employees
GO
CREATE TABLE employees
(
emp_id int NOT NULL PRIMARY KEY
CHECK (emp_id BETWEEN 0 AND 1000),
emp_name varchar(30) NOT NULL CONSTRAINT no_nums
CHECK (emp_name NOT LIKE '%[0-9]%'),
mgr_id int NOT NULL REFERENCES employees(emp_id),
entered_date datetime NULL CHECK (entered_date >=
CURRENT_TIMESTAMP),
entered_by int CHECK (entered_by IS NOT NULL),
CONSTRAINT valid_entered_by CHECK
(entered_by = SUSER_ID(NULL) 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
