SQL Server/T-SQL Tutorial/Query/Null

NOT NULL 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

Nullability

To enable a column to be nullable, the NULL keyword is added immediately after the data type.
To prevent nulls, the NOT NULL keywords are added.
If NULL or NOT NULL is not specified, the default setting is to allow nulls.
7>
8> CREATE TABLE MyTable (
9>   Category nVarChar(50) NOT NULL,
10>   MyDescription nVarChar(50)
11> )
12> GO
1>
2> drop table MyTable;
3> GO