SQL Server/T-SQL Tutorial/System Settings/SUSER ID
Use SUSER_ID() as constraint
<source lang="sql">
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</source>