It is assumed that, having reached the point where you are ready to create tables, you have already prepared your data analysis and model, and you have a very clear blueprint for the structures of your main tables and their relationships. In preparation for creating these tables, you need to have performed these steps:
Table Ownership and Privileges
When a table is created, Firebird automatically applies the default SQL security scheme to it. The person who creates the table (the owner) is assigned all SQL privileges for it, including the right to grant privileges to other users, triggers, and stored procedures. No other user, except the SYSDBA, will have any access to the table until explicitly granted privileges.
This security is as good (or bad) as the security of access to your server. Anyone who can log into your server can create a database. Anyone who can attach to a database can create tables in it. Firebird 1.5 improves slightly on this unfortunate situation by allowing you to limit the locations where databases can be created and accessed. See the DatabaseAccess parameter in firebird.conf.
CREATE TABLE Statement
The DDL for creating a table is the CREATE TABLE statement. The syntax is
The first essential argument to CREATE TABLE is the table identifier. It is required and must be unique among all table, view, and procedure names in the database, otherwise you will be unable to create the table. You must also supply at least one column definition.
When you create a table in the database, your main task is to define the various attributes and constraints for each of the columns in the table.
This is the syntax for defining a column:
The next sections list the required and optional attributes that you can define for a column.
These are the required attributes:
Columns are separated by commas, for example:
The column FULL_NAME is a computed column calculated by concatenating two other columns in the definition, FIRST_NAME and LAST_NAMES. We will come back to computed columns a little later. A NOT NULL constraint is applied to PERSON_ID because we want to make it a primary key (details to come later).
For the PHONE_NUMBER column, we use the domain:
Columns Based on Domains
If a column definition is based on a domain, it can include a new default value, additional CHECK constraints, or a COLLATE clause that overrides one already defined in the domain definition. It can also include additional attributes or column constraints. For example, you can add a NOT NULL constraint to the column if the domain does not already define one.
For example, the following statement creates a table, COUNTRY, referencing a domain called COUNTRYNAME, which doesn’t have a NOT NULL constraint:
We add the NOT NULL constraint to the column definition of COUNTRYNAME because we know it is going to be needed as the primary key of the COUNTRY table.
The following sections describe optional attributes for columns.
Defining a default value can save data entry time and prevent data entry errors when new rows are inserted into a table. If the row is inserted without including the column in the column list, a default value—if defined—can be automatically written into the column. In a column based on a domain, the column can include a default value that locally overrides any default defined for the domain.
For example, a possible default for a TIMESTAMP column could be the context variable CURRENT_TIMESTAMP (server date and time). In a (True/False) Boolean- style character column, the default could be set to 'F' to ensure that a valid, non-null state was written on all new rows.
A default value must be compatible with the data type of the column and consistent with any other constraints on the column or its underlying domain. A default, as appropriat to data type, can be
Nullable columns default to NULL automatically, but you may wish to override an unwanted domain-level default. Don’t define this default on a column that has a NOT NULL constraint.
The following example defines a column, CREATED_BY, that defaults to the context variable CURRENT_USER:
A new row is inserted by user JILLIBEE, omitting CREATED_BY from the column list:
The table is queried:
A CHARACTER SET can be specified for an individual character or text BLOB column when you define the column. If you do not specify a character set, the column assumes the character set of the domain, if applicable; otherwise, it takes the default character set of the database. For example:
The COLLATE Clause
A COLLATE clause can be added to a CHAR or VARCHAR column to override a collation sequence otherwise defined for the column’s character set by the underlying domain, if applicable. Collation sequence is not applicable to BLOB types.
The following extends the previous example to include a COLLATE clause:
A computed column is one whose value is calculated each time the column is accessed at runtime. It can be a convenient way to access redundant data without the negative effects of actually storing it. Not surprisingly, such columns cannot perform like hard data—refer to the restrictions listed later in this section.
This is the syntax:<col_name> COMPUTED [BY] (<expr>);
There is no need to specify the data type—Firebird calculates an appropriate one. Expr is any scalar expression that is valid for the data types of the columns involved in the calculation. External functions are fine to use, as long as you are sure that the libraries used by the functions will be available on all platforms where the database might be installed.
Other restrictions exist for computed columns:
Examples of COMPUTED Columns
The following statement creates a computed column, FULL_NAME, by concatenating the LAST_NAMES and FIRST_NAME columns.
The next statement computes two columns using context variables. This can be useful for logging the particulars of row creation:
The next example creates a table with a calculated column (NEW_PRICE) using the previously created OLD_PRICE and PERCENT_CHANGE definitions:
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.