SQL Server/T-SQL Tutorial/System Settings/SUSER ID

Use SUSER_ID() as constraint

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