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

Материал из SQL эксперт
Перейти к: навигация, поиск

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>