ALTER TABLE; ALTER TABLE; ALTER TABLE;

1. Add columns to the table



Add S_entrance column to Student table whose data type is date.

ALTER TABLE Student ADD S_entrance DATE

Add field “Lecturers” to KCXX table with data type CHAR (10).

Alter table KCXX add (10)

Alter TABLE KCXX add identity column: ID(int type, initial value 1, increment 1)

Alter table KCXX add ID int identity(1,1)

Add the column birth year: Sbirth to the Student table and set it to calculate automatically: 2021-sage.

alter table Student add Sbirth as 2021-Sage

2. Modify the columns of the table



Change the data type of the Sage column in the Student table from integer.

ALTER TABLE Student ALTER COLUMN Sage INT

To modify the “instructor” KCXX table field, called the teacher data type is char (20).Two steps

Sp_rename 'KCXX. ',' teacher','column'

alter table KCXX alter column teacher char(20)

3. Add table constraints



Is the constraint that the increment value of Ccredit column of Course table is (1~4).

ALTER TABLE Course ADD CHECK (Ccredit>=1 AND Ccredit<=4) 或

ALTER TABLE Course ADD CONSTRAINT chk_credit CHECK (Ccredit>=1 AND Ccredit<=4)

Create the check constraint chk_JG for the Price column to restrict its value from being null.

Alter table 材 料 Add constraint chk_JG check(constraint chk_JG check is not null)

Adds a constraint that must have a unique value for the Course name (Cname) column of the Course in the curriculum schedule. ALTER TABLE Course ADD CONSTRAINT U_name UNIQUE(Cname) ALTER TABLE Course ADD CONSTRAINT U_name UNIQUE(Cname) Alter table Student add constraint MoRen default ‘male’ for Ssex alter table Student add constraint MoRen default ‘male’ for Ssex

Add “Title” key to textbook table. Alter table 教材 add primary key(constraint PK_教材_ 教材 名 称) alter table 教材 add primary key(constraint PK_教材 名 称) Only one foreign key constraint can be added at a time! Alter table textbook add Constraint FK_ textbook _ discipline Foreign Key (discipline) References Subject (Subject No.)

Create a check constraint named chk_LX for the Type column, limiting its value to new books or used books. Alter table testbook add constraint chk_LX check(type in (‘ new ‘,’ old ‘))

Adds the default value New Books to the Type column of the textbook table. Alter table textbook add default ‘new book’ for type

4. Delete the columns of the table

Alter table XSDA1 DROP column Sbirth alter table XSDA1 drop column Sbirth

5. Delete table constraints

Primary keys, foreign keys, unique values, default values, and check constraints are all dropped by constraint

Delete the foreign key from the textbook table. Alter table dropfk__ dropfk__ __66603565 alter table dropfk__ dropfk__ __66603565 Alter table test1 drop constraint chk_date

Alter table XSCJ drop constraint… Alter table XSCJ drop [constraint] fk1, fk2

The PRIMARY KEY constraint differs from the UNIQUE constraint as follows:

(1) A table can define only one PRIMARY KEY constraint, but a table can define several UNIQUE constraints for different columns as required. (2) The PRIMARY KEY value cannot be NULL, while the UNIQUE value can be NULL. (3) When a PRIMARY KEY constraint is created, the system automatically generates an index. The default type of the index is a cluster index. When a UNIQUE constraint is created, the system automatically generates a UNIQUE index whose default type is non-cluster index.

The PRIMARY KEY constraint is similar to the UNIQUE constraint in that it does not allow duplicate values for corresponding fields in the table.

Set cascading changes

Alter table XSCJ add constraint FK_kcxx_ ALTER table XSCJ add constraint FK_kcxx_ ALTER table XSCJ add constraint FK_kcxx_ alter table XSCJ add constraint FK_kcxx_ KCXX (Course Id) on Update Cascade

Example Set cascading deletion

Alter table XSCJ add constraint FK_kcxx_ ALTER table XSCJ add constraint FK_kcxx_ ALTER table XSCJ add constraint FK_kcxx_ alter table XSCJ add constraint FK_kcxx_ References KCXX (Course ID) on Delete Cascade