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