4> CREATE TABLE employee
5> (
6> emp_id int NOT NULL PRIMARY KEY DEFAULT 1000
7> CHECK (emp_id BETWEEN 0 AND 1000),
8>
9> emp_name varchar(30) NULL DEFAULT NULL CONSTRAINT no_nums
10> CHECK (emp_name NOT LIKE '%[0-9]%'),
11>
12> mgr_id int NOT NULL DEFAULT (1) REFERENCES
13> employee(emp_id),
14>
15> entered_date datetime NOT NULL CHECK (entered_date >=
16> CONVERT(char(10), CURRENT_TIMESTAMP, 102))
17> CONSTRAINT def_today DEFAULT
18> (CONVERT(char(10), GETDATE(), 102)),
19>
20> entered_by int NOT NULL DEFAULT SUSER_ID()
21> CHECK (entered_by IS NOT NULL),
22>
23> CONSTRAINT valid_entered_by CHECK (entered_by=SUSER_ID() AND
24> entered_by <> emp_id),
25>
26> CONSTRAINT valid_mgr CHECK (mgr_id <> emp_id OR emp_id=1),
27>
28> CONSTRAINT end_of_month CHECK (DATEPART(DAY, GETDATE()) < 28)
29> )
30> GO
1>
2>
3> EXEC sp_helpconstraint employee
4> 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__66EB10A1
(n/a) (n/a) Enabled Is_For_Replication ([emp_id]>=(0) AND [emp_id]<=(1000))
CHECK on column entered_date CK__employee__entere__6BAFC5BE
(n/a) (n/a) Enabled Is_For_Replication ([entered_date]>=CONVERT([char](10),getdate(),(102)))
CHECK on column entered_by CK__employee__entere__6E8C3269
(n/a) (n/a) Enabled Is_For_Replication ([entered_by] IS NOT NULL)
DEFAULT on column entered_date def_today
(n/a) (n/a) (n/a) (n/a) (CONVERT([char](10),getdate(),(102)))
DEFAULT on column emp_id DF__employee__emp_id__65F6EC68
(n/a) (n/a) (n/a) (n/a) ((1000))
DEFAULT on column emp_name DF__employee__emp_na__67DF34DA
(n/a) (n/a) (n/a) (n/a) (NULL)
DEFAULT on column entered_by DF__employee__entere__6D980E30
(n/a) (n/a) (n/a) (n/a) (suser_id())
DEFAULT on column mgr_id DF__employee__mgr_id__69C77D4C
(n/a) (n/a) (n/a) (n/a) ((1))
CHECK Table Level end_of_month
(n/a) (n/a) Enabled Is_For_Replication (datepart(day,getdate())<(28))
FOREIGN KEY FK__employee__mgr_id__6ABBA185
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__6502C82F
(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() 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__6ABBA185
1>
2> INSERT employee DEFAULT VALUES
3> GO
Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 2
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK__employee__mgr_id__6ABBA185". The conflict occurred in database "master", table "dbo.employee", column 'emp_id'.
The statement has been terminated.
1>
2>
3> drop table employee;
4> GO
| 26.36.SUSER_ID |
| 26.36.1. | Use SUSER_ID() as constraint |