CHECK (DATEPART(DAY, GETDATE()) < 28) : Check « Constraints « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Constraints
3. Check

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