Sample code for creating and deleting constraints using SQL statements

Time:2021-10-25
Create and delete constraints using SQL statements 

Constraint type 
Primary key constraint: the primary key column data is required to be unique and cannot be empty.  
Unique constraint: the column is required to be unique and can be null, but only one null value can appear.  
Check constraint: limit the value range and format of a column, such as constraints on age and mailbox (there must be @).  
Default constraint: the default value of a column. If there is a lot of duplicate data in the database, it can be set as the default value.  
Foreign key constraint: used to establish a relationship between two tables. You need to specify which column of the main table to reference.  
********************************************************************** 
Add constraint: 
alter table tablename 
add constraint pk_ Colname primary key (colname) primary constraint 
alter table tablename 
add constraint uq_ Colname unique (colname) unique constraint 
alter table tablename 
add constraint df_ Colname default ('unknown address') for colname default constraint 
alter table tablename 
add constraint ck_ Colname check (colname between 12 and 15) check constraints 
alter table tablename 
add constraint fk_ Colname foreign key (colname) references tablename (colname) external constraints 
Delete constraint: 
alter table tablename 
Drop constraint constraint name  
Create login account / database user 
  
Create login account: 
exec sp_ Grantlogin 'Windows domain name / domain account' 
Create database user: 
exec sp_ Grantdbaccess' login account ',' database user ' 
Authorization to database: 
Grant permission [on table name] to database user 
The above statements can be operated directly in the enterprise manager 
 
Enterprise Manager / security / login / new login 
Fill in the name and password 
Select database access, and then "allow in database role" dB_ Owner also checked 

The default constraint enables the user to define a value and provide the defined value to a column whenever the user does not enter a value in the column. If the user has no specific requirements for this column, you can use the default constraint to enter default values for this column.