SQL Server/T-SQL Tutorial/Constraints/Default — различия между версиями

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

Текущая версия на 13:25, 26 мая 2010

Column with default random value

   <source lang="sql">

3> CREATE TABLE random_data 4> ( 5> col1 int PRIMARY KEY IDENTITY(10,10) NOT NULL, 6> col2 int NOT NULL DEFAULT CASE 7> -- Random integer between -9999 and 9999 8> WHEN CONVERT(int, RAND() * 1000) % 2 = 1 9> THEN (CONVERT(int, RAND() * 100000) % 10000 * -1 ) 10> ELSE CONVERT(int, RAND() * 100000) % 10000 11> END, 12> col3 char(15) NOT NULL DEFAULT 13> CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65) 14> -- 65 is "A" 15> + CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65) 16> + CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65) 17> + CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65) 18> + REPLICATE(CHAR((CONVERT(int, RAND() * 1000) 19>  % 26) + 65), 11) 20> ) 21> GO 1> 2> DECLARE @counter int 3> SET @counter=1 4> WHILE (@counter <= 1000) 5> BEGIN 6> INSERT random_data DEFAULT VALUES 7> SET @counter=@counter + 1 8> END 9> GO 1> 2> drop table random_data; 3> GO</source>


DEFAULT Constraints

   <source lang="sql">

5> CREATE TABLE Shippers 6> ( 7> ShipperID int IDENTITY NOT NULL 8> PRIMARY KEY, 9> ShipperName varchar(30) NOT NULL, 10> DateInSystem smalldatetime NOT NULL 11> DEFAULT GETDATE () 12> ) 13> GO 1> 2> CREATE TABLE Customers 3> ( 4> CustomerNo int IDENTITY NOT NULL 5> PRIMARY KEY, 6> CustomerName varchar(30) NOT NULL, 7> Address1 varchar(30) NOT NULL, 8> Address2 varchar(30) NOT NULL, 9> City varchar(20) NOT NULL, 10> State char(2) NOT NULL, 11> Zip varchar(10) NOT NULL, 12> Contact varchar(25) NOT NULL, 13> Phone char(15) NOT NULL, 14> FedIDNo varchar(9) NOT NULL, 15> DateInSystem smalldatetime NOT NULL 16> ) 17> GO 1> 2> ALTER TABLE Customers 3> ADD CONSTRAINT CN_CustomerDefaultDateInSystem 4> DEFAULT GETDATE() FOR DateInSystem 5> GO 1> 2> drop table Shippers; 3> drop table Customers; 4> GO</source>


Default int type value and default char type value

   <source lang="sql">

2> CREATE TABLE xyz 3> ( 4> col1 int PRIMARY KEY IDENTITY(1, 1) NOT NULL, 5> col2 int NOT NULL DEFAULT 999, 6> col3 char(10) NOT NULL DEFAULT "ABCEFGHIJK" 7> ) 8> GO 1> 2> SET NOCOUNT ON 3> DECLARE @counter int 4> SET @counter=1 5> WHILE (@counter <= 1000) 6> BEGIN 7> INSERT xyz DEFAULT VALUES 8> SET @counter=@counter+1 9> END 10> GO 1> 2> 3> drop table xyz; 4> GO</source>


Designating Default Column Values: Assign Zeroes Instead of Null Values

   <source lang="sql">

6> CREATE TABLE T ( 7> int1 int, 8> bit1 bit NOT NULL DEFAULT 0, 9> varchar1 varchar(3), 10> dec1 dec(5,2), 11> cmp1 AS (int1 + bit1) 12> ) 13> GO 1> 2> drop table t; 3> GO</source>


Dropping Defaults

   <source lang="sql">

DROP DEFAULT <default name> EXEC sp_depends <object name></source>


Insert to a table with default value

   <source lang="sql">

2> CREATE TABLE T ( 3> int1 int, 4> bit1 bit NOT NULL DEFAULT 0, 5> rvr1 timestamp, 6> usr1 nvarchar(128) DEFAULT USER, 7> createtime datetime DEFAULT CURRENT_TIMESTAMP 8> ) 9> GO 1> 2> INSERT T (int1, bit1) VALUES (3, 1) 3> GO (1 rows affected) 1> 2> drop table t; 3> GO</source>


The syntax for defining a default

   <source lang="sql">

CREATE DEFAULT <default name> AS <default value></source>


Using the system date as a default parameter.

   <source lang="sql">

5> 6> 7> CREATE TABLE myusers( 8> UserID varchar(30)NOT NULL PRIMARY KEY, 9> FirstName varchar(30), 10> LastName varchar(30), 11> EmployeeType char(1) NOT NULL, 12> DBAccess varchar(30), 13> StartDate datetime, 14> ExpDate datetime 15> ) 16> GO 1> 2> 3> CREATE PROC pr_deluser (@TD datetime ) 4> AS 5> DECLARE getuser_curs CURSOR 6> FOR 7> SELECT UserID 8> FROM myusers 9> WHERE ExpDate <= @TD 10> DECLARE @HoldID varchar(30) 11> DECLARE @MyCount int 12> SELECT @MyCount = 0 13> OPEN getuser_curs 14> FETCH NEXT FROM getuser_curs into @HoldID 15> WHILE @@FETCH_STATUS = 0 BEGIN 16> EXEC sp_droplogin @HoldID 17> EXEC pr_copyuser @HoldID 18> SELECT @MyCount = @MyCount + 1 19> FETCH NEXT FROM getuser_curs into @HoldID 20> END 21> DECLARE @MyDisp varchar(50) 22> SELECT @MyDisp = "Number of Users Deleted is " + ltrim(str(@MyCount)) 23> PRINT @MyDisp 24> CLOSE getuser_curs 25> DEALLOCATE getuser_curs 26> GO Cannot add rows to sysdepends for the current object because it depends on the missing object "pr_copyuser". The object will still be created. 1> 2> 3> DECLARE @myvalue varchar(12) 4> 5> SELECT @myvalue=CONVERT(varchar(12), getdate()) 6> 7> EXEC pr_deluser @myvalue 8> GO Number of Users Deleted is 0 1> 2> drop PROC pr_deluser; 3> GO 1> 2> 3> drop table myusers; 4> GO</source>