6> CREATE TABLE employee
7> (
8> emp_id int NOT NULL PRIMARY KEY
9> CHECK (emp_id BETWEEN 0 AND 1000),
10>
11> emp_name varchar(30) NOT NULL CONSTRAINT no_nums
12> CHECK (emp_name NOT LIKE '%[0-9]%'),
13>
14> mgr_id int NOT NULL REFERENCES employee(emp_id),
15>
16> entered_date datetime NULL CHECK (entered_date >=
17> CURRENT_TIMESTAMP),
18>
19> entered_by int CHECK (entered_by IS NOT NULL),
20> CONSTRAINT valid_entered_by CHECK
21> (entered_by = SUSER_ID(NULL) AND
22> entered_by <> emp_id),
23>
24> CONSTRAINT valid_mgr CHECK (mgr_id <> emp_id OR emp_id=1),
25>
26> CONSTRAINT end_of_month CHECK (DATEPART(DAY, GETDATE()) < 28)
27> )
28> GO
1>
2> EXEC sp_helpconstraint employee
3> GO
Object Name
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
employee
constraint_type constraint_name
delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -----------------------------------------------------
--------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------
CHECK on column emp_id CK__employee__emp_id__5C6D822E
(n/a) (n/a) Enabled Is_For_Replication ([emp_id]>=(0) AND [emp_id]<=(1000))
CHECK on column entered_date CK__employee__entere__5F49EED9
(n/a) (n/a) Enabled Is_For_Replication ([entered_date]>=getdate())
CHECK on column entered_by CK__employee__entere__603E1312
(n/a) (n/a) Enabled Is_For_Replication ([entered_by] IS NOT NULL)
CHECK Table Level end_of_month
(n/a) (n/a) Enabled Is_For_Replication (datepart(day,getdate())<(28))
FOREIGN KEY FK__employee__mgr_id__5E55CAA0
No Action No Action Enabled Is_For_Replication mgr_id
REFERENCES master.dbo.employee (emp_id)
CHECK on column emp_name no_nums
(n/a) (n/a) Enabled Is_For_Replication (NOT [emp_name] like '%[0-9]%')
PRIMARY KEY (clustered) PK__employee__5B795DF5
(n/a) (n/a) (n/a) (n/a) emp_id
CHECK Table Level valid_entered_by
(n/a) (n/a) Enabled Is_For_Replication ([entered_by]=suser_id(NULL) AND [entered_by]<>[emp_id])
CHECK Table Level valid_mgr
(n/a) (n/a) Enabled Is_For_Replication ([mgr_id]<>[emp_id] OR [emp_id]=(1))
Table is referenced by foreign key
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
master.dbo.employee: FK__employee__mgr_id__5E55CAA0
1>
2>
3> drop table employee;
4> GO