Oracle modify field length and attribute method _oracle_ Script home
Script house Common server software
Quick navigation
Your location: Home page → Archive → oracle→ Oracle modifies fields

Oracle Method for modifying field length and properties

Updated: May 10, 2024 08:58:38 Author: Blue Street Light
This article mainly introduces the Oracle to modify the field length and attributes, this article through the example code to give you a very detailed introduction, for everyone's study or work has a certain reference value, the need for friends to refer to it

The Oracle database uses ALTER TABLE MODIFY syntax to modify column definitions. grammar

alter table table_name modify(column_name field type);

Oracle allows for a variety of operations, but the main ones are the following:

  • Modify the visibility of a data column (visible or invisible)
  • Modifies whether a field allows or disallows null
  • Modify field length
  • Modify the default values for columns

Modify multiple column syntax as follows:

ALTER TABLE table_name MODIFY (column_name_1 field type, column_name_2 field type,...) ;

For example, the database table and data are still used to test: two methods of creating a new data table in Oracle (click the previous text to jump), do not understand the self-jump link to view, here is the table under the column and insert the data statement as follows:

stuinfo_01 create table JT_CS.stuinfo_01 (stuid varchar2(11) not null,-- Student ID: 'S'+ class number (7 digits) + student number (3 digits) (cannot be empty)SC200101001 stuname varchar2(50) not null,-- Student name (cannot be empty) sex char(1) not null,-- Gender (cannot be empty)1 (male), 2(female) age number(2) not null,-- Age (cannot be empty) classno varchar2(7) not null,-- Class number: 'C'+ grade (4 digits) + class number (2 digits) (cannot be empty)C200101 stuaddress varchar2(100) default 'Address not entered ',-- Address (If not entered or left blank, enter' address not entered 'by default) grade char(4) not enroldate date, enrolment time idnumber varchar2(18) default 'ID card not collected' not null-- ID card (cannot be empty) INSERT test data INSERT INTO JT_CS.STUINFO VALUES ('SC200101001',' Lufei ','1','19','C200101',' East Haigua Kingdom Wind Village ','2001','2001/09/01','411428199602569201'); INSERT INTO JT_CS.STUINFO VALUES (' SC200101002 ', 'grand', '1', '21', 'C200101', 'the east China sea frost monthly village', '2001', '2001/09/01', '411428199602569202'); INSERT INTO JT_CS.STUINFO VALUES (' SC200101003 ', 'Naomi murdoch', '2', '20', 'C200101', 'cocoa west village', '2001', '2001/09/01', '411428199602569203'); INSERT INTO JT_CS.STUINFO VALUES (' SC200101004 ', 'wu thorpe', '1', 'the', 'C200101', 'rob village, west of the east China sea,' 2001 ', '2001/09/01', '411428199602569204'); INSERT INTO JT_CS.STUINFO VALUES (' SC200101005 ', 'mountain', '1', '21', 'C200101', 'the north sea JieErMa kingdom', '2001', '2001/09/01', '411428199602569205'); INSERT INTO JT_CS.STUINFO VALUES ('SC200101006',' Jobba ','1','15','C200101',' Magnetic Drum Island ','2001','2001/09/01','411428199602569206');  INSERT INTO JT_CS.STUINFO VALUES ('SC200101007',' Robin ','2','30','C200101',' O 'Hara ','2001','2001/09/01','411428199602569207');  INSERT INTO JT_CS.STUINFO VALUES ('SC200101008',' Franchi ','1','34','C200101',' Nanhai ','2001','2001/09/01','411428199602569208');  INSERT INTO JT_CS.STUINFO VALUES ('SC200101009',' Brooke ','1','63','C200101',' Xihai ','2001','2001/09/01','411428199602569209');  INSERT INTO JT_CS.STUINFO VALUES ('SC200101010',' Shiping ','1','46','C200101',' Fish Man Island ','2001','2001/09/01','411428199602569210');

The query result is as follows:

SELECT * FROM JT_CS.STUINFO_01;

1, modify the visibility of the data column (visible or invisible)

By default, the columns of a new table are visible without any definition. We can make certain columns invisible when creating a TABLE or using ALTER TABLE MODIFY statements.

ALTER TABLE JT_CS.STUINFO_01 MODIFY SEX INVISIBLE; alter table Jt_cs.stuinfo_01 modify SEX invisible; ALTER TABLE JT_CS.STUINFO_01 MODIFY SEX VISIBLE; alter table jt_cs. stuinfo_01 Modify sex visible;

2. Change whether the field allows or disallows null

Prepare test data (create a small problem first)

UPDATE JT_CS.STUINFO_01 SET enroldate='' WHERE STUID='SC200101001'; update Jt_cs. stuinfo_01 Set enroldate='' where STUid ='SC200101001';

Modify the enroldate field. The enrolDate field cannot be null

ALTER TABLE JT_CS.STUINFO_01 MODIFY enroldate NOT NULL; ALTER table Jt_cs. stuinfo_01 Modify enroldate NOT NULL;

Because we have just cleared Mr. Luffy's enrollment time, we are told that we have an error when we execute NOTNULL. NULL values exist, which means that we must ensure that the existing data conforms to the new constraint (that is, if there is NULL in the original data, it will not work).

To solve this problem, first we need to fill in all the empty columns of the enroldate field

UPDATE JT_CS.STUINFO_01 SET enroldate='2001-09-30' WHERE STUID='SC200101001'; Update Jt_cs. stuinfo_01 Set enroldate='2001-09-30';

Then change the enroldate column constraints:

ALTER TABLE JT_CS.STUINFO_01 MODIFY enroldate NOT NULL; ALTER table Jt_cs. stuinfo_01 Modify enroldate NOT NULL;

3. Change the field length

ALTER TABLE JT_CS.STUINFO_01 MODIFY IDNUMBER VARCHAR2(20); alter table Jt_cs. stuinfo_01 Modify idnumber varchar2 (20);

Note: To shorten or expand the size of the column, make sure that all data in the column matches the new size, otherwise the statement will not execute.

4. Change the default value of the column

Add a new column named status, which defaults to 1, to the JT_CS.STUINFO_01 table

ALTER TABLE JT_CS.STUINFO_01 ADD status NUMBER(1, 0) DEFAULT 1 NOT NULL; ALTER table Jt_cs. stuinfo_01 Add status number (1, 0) Default 1 not null;

Change the default value of the newly added column status to 0

ALTER TABLE JT_CS.STUINFO_01 MODIFY status DEFAULT 0; alter table Jt_cs. stuinfo_01 Modify Status default 0;

Insert a new piece of data to test

INSERT data by the specified field INSERT INTO JT_CS.STUINFO_01 (STUID, STUNAME, SEX, AGE, CLASSNO, STUADDRESS, GRADE, ENROLDATE, IDNUMBER) VALUES (' SC200101011 ', 'saab', '1', 'and', 'C200101', 'fish island', '2000', '2001/09/01', '411428199602569211');

Result query

To this article about Oracle to modify the field length and attributes of the article is introduced to this, more related Oracle to modify the field length and attributes of the content please search the script home previous articles or continue to browse the following related articles hope that you will support the script home in the future!

Articles you may be interested in:

Related article

Latest comments