After changing the data sheet, the user may need to modify the design of the table after establishing or pouring a data sheet. The ALTER TABLE statement can be used at this time. Note that the structure of changing existing tables may cause users to lose some data. For example, changing a data type of a domain will cause data loss or rounding errors, depending on the data type of the user now used. Changing the data table may also damage the part of the user's application involving the changed domain. So the user must be particularly careful before modifying the structure of the existing table. Using the ALTER TABLE statement, the user can increase, delete, or change the column or domain, or add or delete a constraint. You can also set a default value for a domain, but only one domain can be modified at a time. Suppose we have a database of bills, and we want to add a domain in the customer's data sheet, then you can use the ALTER TABLE statement, write the name, data type, and data size of the upper domain after its Add Column clause ( If needed). Alter Table TBLCUSTOMERSADD Column Address Text (30) To change the data type or size of the domain, you can use the Alter Column clause to add the desired data type and data size. Alter Table TBLCUSTOMERSALTER COLUMN Address Text (40) If you need to change the name of the domain, you must delete the domain and recreate it. Delete a domain to use the Drop Column clause, in the next name. Alter Table TBLCUSTOMERSDROP COLUMN Address Note The use of this method will delete existing data for this domain. If you need to save these data, users need to change the name of the domain in the design mode of Access's user interface, or write code to save existing data in a temporary table and then add it to the name of the changed rename. The default refers to the value of adding a new record in the table and is not assigned to the column to assign a value to the domain. Set the default value for a domain. To use the default keyword after defining the domain, use the Add Column or Alter Column clause. ALTER TABLE TBLCUSTOMERSALTER Column Address Text (40) Default Unknown Note The default value does not use single quotes, if single quotes are used, the quotation marks will be inserted into the record. You can also use the Default keyword in the CREATE TABLE statement. CREATE TABLE tblCustomers (CustomerID INTEGER CONSTRAINT PK_tblCustomersPRIMARY KEY, [Last Name] TEXT (50) NOT NULL, [First Name] TEXT (50) NOT NULL, Phone TEXT (10), Email TEXT (50), Address TEXT (40) DEFAULT UNKNOWN) Note: The default statement is only available in Jet OLE DB Provider and ADOs, and the use will return to the error message in the user interface of Access SQL View. The following section will discuss how to use constraints in the ALTER TABLE statement. To get more detailed instructions for ALTER TABLE, enter Alter Table in the Office Assistance or in the tabs of the answer wizard help in Microsoft Access, and then click Find. Constraints in the "Access 2000 Basic Microsoft Jet SQL", we discussed the constraints of the connection between the establishment table. Constraints can also be used to establish primary keys and reference integrity to limit data values inserted into one domain. Typically, constraints can be used to maintain data integrity and consistency in the user database.
There are two types of constraints: single data domain (or domain-grade) constraints and multi-data domains (or titled grades) constraints. Both constraints can be used in the CREATE TABLE or ALTER TABLE statement. Single field constraints, that is, the constraints of the usual collections are defined after the domain and their data types are defined for this domain. Below we use the user table, generate a single domain primary key in the Customerid domain. When the constraint is added, use the constraint key after domain name. Alter Table TBLCUSTOMERSALTER Column Customerid INTEGERCONSTRAINT PK_TBLCUSTOMERS PRIMARY KEY Note The name of the constraint here is given. The user can also use the abbreviation to omit the constraint clause when defining the primary key. Alter Table TBLCUSTOMERSALTER COLUMN CUSTOMERID INTEGER PRIMARY Key However, using the abbreviation method will result in the name of the generated constraints of Access random, so that it is difficult to reference in the code. Therefore, it is best to set the name to the constraint. To delete a constraint, you can use the DROP CONSTRAINT clause in the ALTER TABLE statement and give the name of the constraint. Alter Table TblCustomersDrop Constraint PK_TBLCUSTOMERS constraints can also be used to give domain restrictions. The user can set the limit value to NOT NULL or Unique, or define a testistic constraint that means that a rule that can be applied to a certain domain. For example, the user wants to limit the name of the name and the name of the name, means that there will never be two records of the same name in the table. This is because this constraint is a multi-domain, and is the level defined at the table, not the domain level. Use the Add Constraint clause to define a multi-domain list. Alter Table Tblcustomersadd ConsTraint Customernames Unique ([Last Name], [First Name]) Note: We are just to demonstrate how to use constraints, and in actual applications, users may not want to completely limit the uniqueness of the name. Testive constraint is a new powerful SQL feature that allows the user to add data legitimacy inspections in the table, which can point to a single domain, or point to one or more tables. Multiple domains. For example, if the user wants to determine if the value input to the invoice record is always greater than 0, a Check key can be defined in the ADD CONSTRAINT clause of the ALTER TABLE statement. Alter Table TblinVoicesAdd Constraint CheckAmountcheck (Amount> 0) Note: The test constraint statement can only be performed by Jet OLE DB Provider and ADO, and an error message will be returned in the user interface of Access SQL View. Moreover, to delete a testive constraint, you must also perform a DROP CONSTRAINT statement in JET OLE DB Provider and ADO. In addition, if the user has defined a test constraint: (1) The user interface in the Access is not shown as a legitimacy rule, (2) The user cannot define the properties of the legitimacy text in this interface, otherwise it will A general error message is given, and (3) cannot be able to pass the Access user interface or delete the data table in the code before the user uses the DROP CONSTRAINT statement via ADO. Expression used to define a testistic constraint can also be used to point to multiple domains in the same table, or even domains in other tables.
Among them, any operators in Microsoft Jet SQL can be used, such as SELECT statements, math operators, and collection functions. The length of the expression used to define the testive constraint cannot exceed 64 characters. Imagine that the user wants to check the credit line of each customer before adding customers to the Customers table. You can generate a constraint using the ALTER TABLE statement with the Add Column and Constraint clauses that will find the value in the CreditLimit table to verify the customer's credit line. The following SQL statement will generate a TBLCReditLimit table, then add the CustomerLimit field into the TBLCUSTOMERS table and add the test constraint to the TBLCUSTOMERS table, and finally test the test constraint. CREATE TABLE tblCreditLimit (Limit DOUBLE) INSERT INTO tblCreditLimitVALUES (100) ALTER TABLE tblCustomersADD COLUMN CustomerLimit DOUBLEALTER TABLE tblCustomersADD CONSTRAINT LimitRuleCHECK (CustomerLimit <= (SELECT LimitFROM tblCreditLimit)) UPDATE TABLE tblCustomersSET CustomerLimit = 200WHERE CustomerID = 1 Note: when a user performs UPDATE TABLE statement When it is prompted to be updated because the statement violates the testive constraint. If the user uses a value that is less than 100, it can be successful. Constraint-II Cascade Updates and Delete Constraints can also be used to establish reference integrity in the database in the database. Reference integrity means that data is consistent and unbearable. For example, if a customer's record is deleted but the customer's waybill record still exists in the database, the data is not consistent, that is, the database is in the database, there is an isolated customer portfolio record. Reference integrity is established when the user has established the relationship between each table. In addition to establishing reference integrity, users can also use cascader updates and deletion to ensure synchronization of each other. For example, once a cascade update and deletion are defined, the customer's waybill record will also be automatically deleted when the user deletes the customer record. To use cascading updates and delete, users can use the on Update Cascade and / or on delete cascade keyword in the CONSTRAINT clause of the ALTER TABLE statement. Note They must be applied to foreign keys. Alter Table TBLshippingAdd ConsTRAINT FK_TBLSHIPPINGEIGN Key (Customerid) ReferenceStBLCUSTOMERS (Customerid) ON UPDATE CASCADEON DELETE CASCADE Foreign key When the foreign key is processed, the concept of fast foreign keys is useful. The fast foreign key is an outer key that is not indexed. Although this is a bit unreasonable, it can be explained well. By default, once a foreign key is defined, an index based on the foreign bond is automatically generated, which improves the performance of the operation to maintain the reference integrity in many cases. However, if there is a lot of repetition values in the domain defined outside the foreign key, the foreign key index will affect the increase and delete data make efficiency. To prevent the generation of the foreign key index, we can define the foreign key is the no index keyword.
Alter Table TBlinvoicesAdd ConsTRAINT FK_TBLINVOICESFOREIGN Key No Index (Customerid) ReferenceStBLCUSTOMERS (Customerid) ON Update Cascadeon Delete Cascade Note: Quick foreign key statements can only be performed by Jet OLE DB Provider and ADO. Use the error message to return to the user interface of Access SQL View. Moreover, to delete a quick foreign key, you must also execute the DROP CONSTRAINT statement in Jet Ole DB Provider and ADO. In recorded database applications, the fast foreign key will also work. For example, there is a table for identifying the Customertypes of the tracked customer type, a Customer Table and an Orders Table. There are 10 lines in the Customertypes table, and there is 100,000 lines in the Customer table, and there are 350000 rows in the Orders table. At this point, the fast foreign key to point to the primary key to the Customertypes table in the Customers table will be a good choice because the only 10 lines are only 10 rows in 100,000 rows. At this time, the index is useless to extract data, and inserting, deleting and updating data in Customertype is a cumbersome. On the other hand, the quick foreign key is applied to the CustomerID column in the ORDERS table is probably nothing, because each of the values represent different customers, so it is generally unique. In this case, it will be beneficial to use the usual foreign border with indexes, because it is applied to the connection and other lookup rules. Note: Although the ALTER TABLE statement is used in most examples of this section, all of this can be written in the CREATE TABLE statement. To learn more about the constraint clause, enter Alter Table in the Office Assistance or in the tab of the answer wizard help in Microsoft Access, and then click Find. Data types have made the Access application of the base JET data engine to make the Jet data engine changes some data types to those applications based on Microsoft SQL Server or MSDE, add some new data types. The following section will discuss the main data type and how it is achieved. Text Data Type Text (Text) Data Type is used to set a domain that can store plain text, or it can be used to store content containing text and numbers, but the numbers are not used for calculations, such as Phone number or email address. When the user creates a table over the user interface of the Acess, the user has two basic text types: text (Text) and Note (MEMO). But when you create a table via the SQL statement such as CREATE TABLE or ALTER TABLE, there are many other text and MEMO types of synonymous characters available. In general, there can be up to 255 characters in the text domain, and the memo domain can have up to 65,535 characters, but if the odd field does not contain any binary data, its unique limit is the maximum capacity of the entire database (probably 2.14 GB or 1,070,000,000 dual character characters). In addition, the text that is not used and the part will not be saved in memory. The following table lists the most basic Jet text data type, its synonym, and the number of bytes assigned.
Jet Data Types Similarly Word Storage Capacity Text (Text) Text (N), Char, Char (N), Alphaumeric, Alphaumeric (N), String, String (N), VARCHAR, VARCHAR (N), NTEXT (N Nchar, Nchar (N), CHAR VARYING, CHAR VARYING (N), Character Varying, Character Varying (N), National Char, National Character (N), National Char Varying, National Char Varying, National Char Varying, National Character CHAR VARYING (N), National Character Varying, National Character Varying (N) can have 255 characters, each character, two sub-sections (if not compressed). MEMO (Note) Longtext, Longchar, Note, NText65,535 characters; if non-binary data can have a CREATE TABLE statement below the 2.14 GB Different forms that can be used to create tables through the Access SQL View user interface. Synonym. CREATE TABLE tblUITextDataTypes (Field1_TEXT TEXT, Field2_TEXT25 TEXT (25), Field3_MEMO MEMO, Field4_CHAR CHAR, Field5_CHAR25 CHAR (25), Field6_LONGTEXT LONGTEXT, Field7_LONGCHAR LONGCHAR, Field8_ALPHA ALPHANUMERIC, Field9_ALPHA25 ALPHANUMERIC (25), Field10_STRING STRING, Field11_STRING25 STRING (25), Field12_VARCHAR VARCHAR, FIELD13_VARCHAR25 VARCHAR (25), Field14_note Note) If the user sees the designs of the TBLUITEXTDATYPES table through Access user interface, Memo, Longtext, Longchar, and Note Synonyms are MEMO data types, others All synonyms are all TEXT data types. For TEXT data types that do not define the length, the default is 255 characters. Although the above SQL statement can also be performed through Jet Ole DB Provider and ADO, there are still some other TEXT and MEMO data types that can only be performed by Jet OLE DB Provider and ADO.
CREATE TABLE tblCodeTextDataTypesField1_NTEXT NTEXT, Field2_NTEXT25 NTEXT (25), Field3_NCHAR NCHAR, Field4_NCHAR NCHAR (25), Field5_VARYING CHAR VARYING, Field6_VARYING CHAR VARYING (25), Field7_VARYING CHARACTER VARYING, Field8_VARYING CHARACTER VARYING (25), Field9_NATIONAL NATIONAL CHAR, Field10_NATIONAL NATIONAL CHAR ( 25), Field11_NATIONAL NATIONAL CHARACTER, Field12_NATIONAL NATIONAL CHARACTER (25), Field13_NATIONAL NATIONAL CHAR VARYING, Field14_NATIONAL NATIONAL CHAR VARYING (25), Field15_NATIONAL NATIONAL CHARACTER VARYING, Field16_NATIONAL NATIONAL CHARACTER VARYING (25)) view if the user through the Access user interface above If the TBLUITEXTDATYPES table is designed, you will see that only the nchar data type is Memo, and others are the TEXT data type. For TEXT data types that do not define the length, the default is 255 characters. Note: These data types listed in the SQL statement above can only be performed by Jet OLE DB Provider and ADO, which will result in error messages in the user interface of Access SQL View. Also note that a domain of a Text data type is created via Jet Ole DB Provider and ADO, you will be the data type of MEMO through the Access user interface. Unicode compression is now in the Microsoft Jet 4.0 data engine, all TEXT data types are stored in unified coding in two bytes. It replaces the multibly character set (MBCS) format used in the previous versions. Although the double-byte format requires more space to store each character, it can define automatic compression using the Text data type. When the user uses SQL's data type, the double-byte encoded compression attribute default is set to NO, and if you need to set this property to YES, the user can use the WITHCOMPRESSION (or with comp) key when defining the domain. . The following CREATE TABLE statement will create a new customer table where the double-byte encoded compression attribute is set to Yes. CREATE TABLE tblCompressedCustomers (CustomerID INTEGER CONSTRAINTPK_tblCompCustomers PRIMARY KEY, [Last Name] TEXT (50) WITH COMP NOT NULL, [First Name] TEXT (50) WITH COMPRESSION NOT NULL, Phone TEXT (10), Email TEXT (50), Address TEXT (40) DEFAULT UNKNOWN Note The with compression and the consPb key are defined before NOT NULL.
The user can also use the ALTER TABLE statement to change the compressed properties of the existing domain's dual-byte encoding, as shown below: Alter Table TBLCUSTOMERSALTER Column [Last Name] TEXT (50) with compression Note: The with compression listed in the SQL statement above The AND with with with with with the comp key can only be performed by Jet OLE DB Provider and ADO, which will result in error messages in the user interface of Access SQL View. Take that data type at the user design data, depending on the purpose of the user's application. If the app is always available on the Jet database, you should use the most suitable data types. However, if the application is ultimately on the database of ODBC-Compliant, such as SQL Server or MSDE, you should use those that are consistent and convenient data types. NUMERIC DATA Digital Data Type is used to define a domain for storing calculations. Typically, some Numeric Type and other distinguishment is the number of bytes used to store data, which also affects the accuracy of the stored numbers. Many Jet SQL data type soy oil synergies can be used to define, which uses which one depends on this data table is only in a JET database or in a database server such as Microsoft SQL Server. If porting will be performed, the user should select the data type that makes the transplant easier. The following table lists the basic Jet Numeric data types, which all synonymates and the number of bytes allocated. Jet data type synonyms memory size TINYINTINTEGER1, BYTE1 byte SMALLINTSHORT, INTEGER22 bytes INTEGERLONG, INT, INTEGER44 bytes REALSINGLE, FLOAT4, IEEESINGLE4 bytes FLOATDOUBLE, FLOAT8, IEEEDOUBLE, NUMBER8 bytes DECIMALNUMERIC, DEC17 bytes following various exemplary CREATE TABLE statement can The SNUMERIC data type used when you create a table through the user interface of Access SQL View. CREATE TABLE tblUINumericDataTypes (Field1_INT INT, Field2_INTEGER INTEGER, Field3_LONG LONG, Field4_INTEGER1 INTEGER1, Field5_BYTE BYTE, Field6_NUMERIC NUMERIC, Field7_REAL REAL, Field8_SINGLE SINGLE, Field9_FLOAT FLOAT, Field10_FLOAT4 FLOAT4, Field11_FLOAT8 FLOAT8, Field12_DOUBLE DOUBLE, Field13_IEEESINGLE IEEESINGLE, Field14_IEEEDOUBLE IEEEDOUBLE, Field15_NUMBER NUMBER, Field16_SMALLINT Smallint, Field17_Short Short, Field18_integer2 Integer2, Field19_integer4 Integer4) Although the above SQL statement can also be performed by Jet Ole DB Provider and ADO, there are still some other Numeric data types that can only be performed by Jet Ole DB Provider and ADO.
CREATE TABLE tblCodeNumericDataTypes (Field1_TINYINT TINYINT, Field2_DECIMAL DECIMAL, Field3_DEC DECIMAL, Field4_DPRECISION DOUBLE PRECISION) Note: The data type of the above SQL statement can only be executed by Jet OLE DB provider and ADO, using Access SQL View user interface will lead to Error message. Also note that the domain of a Numeric data type is created via Access SQL View, which will be Double Data types when you see the design of the table, but if you create the Numeric data type by Jet Ole DB Provider and ADO, The Access user interface will be a Decimal data type. Use the new Decimal data type, the user can set the accuracy and decumeration of the value. Accuracy is the total number of numbers that can be included in this domain, and the small digits determine that there are several numbers on the right side of the decimal point. The accuracy default is 18, the maximum allowable value 28, and the decimal default is 0, the maximum value Time 28. Create Table TBLDECIMALDATATYPES (DEFAULTTYPE DECIMAL, SPECIFICTYPE DECIMAL (10, 5)) Currency Data Type Currency (CURRENCY) Data Type is used to store 15-bit integers and 4-digit digital value, which uses 8 bytes The amount of storage is Money. The following CREATE TABLE statement demonstrates the usage of the Currency data type when creating a data table, which can also be used in Jet Ole DB Provider and ADO in the Access SQL View user interface. CREATE TABLE TBLCURRENCYDATATYPES (Field1_currency currency, whether the boolean data type is the logical data type, which is (true) or no (false). They use one byte storage, synonyms are Bit, Logical, Logical1, and Yesno. The value of TRUE is equal to -1, FALSE is equal to 0. The following CREATE TABLE statement demonstrates different forms of creating Boolean data types through Jet Ole DB Provider and ADO. Create Table TBluibooleAndatatypes (Field1_bit Bit, Field2_Logical Logical, Field3_Logical1 Logical1, Binary Data Type Byte (binary) Data Type The data type is stored in a binary's original surface stored small capacity any type of data. It uses only one byte of the stored amount of storage for each character stored, and the user can specify the number of bytes assigned. If there is no number of bytes, the default value is 510, which is also the maximum number of bytes that can be allowed. Its synonymates have binary, varbinary, and binary varying. Binary data type is unavailable in the Access user interface. The following CREATE TABLE statement demonstrates different forms of Binary data types that can be used in the Access SQL View user interface.
CREATE TABLE tblUIBinaryDataTypes (Field1_BINARY BINARY, Field2_BINARY250 BINARY (250), Field3_VARBINARY VARBINARY, Field4_VARBINARY250 VARBINARY (250)) Although the above SQL statements can be executed by Jet OLE DB provider and ADO, but still some of the other binary data type synonyms can only be performed by the ADO and Jet OLE DB provider, as follows: CREATE TABLE tblCodeBinaryDataTypes (Field1_BVARYING bINARY VARYING, Field2_BVARYING250 bINARY VARYING (250)) OLEOBJECT OLEOBJECT data type data type used to store binary large objects, such as Word or Excel documents Form. Its number of bytes is not sure, up to 2.14 GB. The synonym features: image, longbinary, general, and oleobject The CREATE TABLE statement below shows the OleObject data type when creating a table via Access SQL View User Interface or Jet Ole DB Provider and ADO. CREATE TABLE tblImageDataTypes (Field1_IMAGE IMAGE, Field2_LONGBINARY LONGBINARY, Field3_GENERAL GENERAL, Field4_OLEOBJECT OLEOBJECT) date and time (DATETIME) data type Date Time (DATETIME) data type used to store date, time, and the combined value the date and time, the number of years from 100-9999 . It uses 8 bytes of storage, its synonymates with the CREATE TABLE statement below the Date, Time, DateTime, and TimeStamp demonstrates the DateTime data type used by Access SQL View User Interface or Jet Ole DB Provider and ADO. Different forms. CREATE TABLE TBLDATETIMEDATATYPES (Field1_date Date, Field2_time Time, Field3_dateTime DateTime, Field4_timeStamp TimeStamp) Counter Data Type Counter Data Type Used to save long-intensive values, which can be automatically added each time a new record in the table. With the Counter data type, the user can set a seed value and add value, and the seed value will be input to the value in the domain when the first record is inserted into the table, and the value is used to add the previous count value. The next record value. If the seed value and add value are not specified, they use the default use 1. There can only be a Counter domain in a table. Its synonym has Counter, AutoInCrement and Identity. The following CREATE TABLE statement demonstrates the synonym of the Counter data type used when creating a table through the Access SQL View user interface. Create Table TBluiicounterDataTypes (Field1 Counter, Field2 Text (10)) Note that the seed value and add value are not specified here, all will use the default value 1. Another way to define the Counter data is to use the AutoInCrement keyword, as shown below: CREATE TABLE TBLUIUNTERDATATYPES (Field1 AutoIncrement (10, 5), Field2 Text (10)) This time the seed value and the added value are specified, then start Time 10, will add 5 each time.
