This is how we can change the name of our table. ALTER TABLE tblTurnover RENAME TO tblIncome; SELECT * FROM tblIncome;
We can change the name of a column. ALTER TABLE tblIncome RENAME COLUMN Turnover to Income; SELECT * FROM tblIncome;
We can alter our column to accept nulls. ALTER TABLE tblIncome ALTER COLUMN Income SET NULL;
Beside READ ONLY and READ WRITE, we also have INSERT ONLY. ALTER TABLE tblCost SET INSERT ONLY;
If we try to delete a row from INSERT ONLY table, that will be prohibited.
We can change default value for some column: ALTER TABLE tblIncome ALTER COLUMN Income SET DEFAULT 200;
We can delete DEFAULT value for Income column: ALTER TABLE tblIncome ALTER COLUMN Income DROP DEFAULT;
We can add new column in tblCost table. ALTER TABLE tblCost ADD COLUMN NewColumn BIGINT DEFAULT 88; SELECT * FROM tblCost; If we hadn't provided default value, then this new column would be filled with nulls.
We will delete our new column: ALTER TABLE tblCost DROP COLUMN NewColumn;
Both of our tables are created in VOC schema. We can move tblCost table to sys schema , and we can move it back: ALTER TABLE tblCost SET SCHEMA sys; ALTER TABLE tblCost SET SCHEMA voc; We can check with "\d" command that our table is transferred to some other schema.
Removing Constraints
If want to remove some constraint (primary key, unique constraint, foreign key), we have to know the name of that constraint. In our example we have placed PRIMARY KEY constraint on the tblIncome table, column ID, and UNIQUE constraint on the tblCost table, column ID . We will first read from system table sys.tables to find what are IDs of our tables:
SELECT * FROM sys.tables WHERE name IN ('tblcost', 'tblincome');
Now that we know that our tables have IDs 13938 and 13946, we will search for our columns in the table sys.columns. Our columns have IDs 13932 and 13940.
SELECT * FROM sys.columns WHERE table_id IN ( 13938, 13946 ) AND name = 'id';
Next step is to find names of our constraints. We will find them in the "dependency_columns_on_keys" table. We will filter this table by our tables and columns.
SELECT * FROM dependency_columns_on_keys WHERE table_id IN ( 13938, 13946 ) AND column_id IN ( 13940, 13932 );
We can now create a query that will return the names of the constraints on the specific columns:
SELECT T.id, T.name, C.id, C.name, K.key_id, K.key_name FROM sys.tables T INNER JOIN sys.columns C ON T.id = C.table_id INNER JOIN dependency_columns_on_keys K ON C.id = K.column_id AND C.table_id = K.table_id WHERE T.name IN ( 'tblincome', 'tblcost' ) AND C.name = 'id';
Now that we know our constraints names, we can delete them: ALTER TABLE tblIncome DROP CONSTRAINT tblturnover_id_pkey; ALTER TABLE tblCost DROP CONSTRAINT tblcost_id_unique;
Constraint on Several columns
When we want to make constraint that encompass several columns, then we have to make a constraint on a table itself. In that case, our CREATE TABLE statement would be like: CREATE TABLE Tab1 ( Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10), PRIMARY KEY ( Col1, Col2 ), UNIQUE ( Col2, Col3 ));
We will now create PRIMARY KEY and UNIQUE constraints on our tables by using ALTER TABLE statement: ALTER TABLE tblIncome ADD PRIMARY KEY ( ID, SubID ); ALTER TABLE tblCost ADD UNIQUE ( ID, SubID );
If we now run the statement that will return constraints names, we will get four rows as a result: SELECT T.id, T.name, C.id, C.name, K.key_id, K.key_name FROM sys.tables T INNER JOIN sys.columns C ON T.id = C.table_id INNER JOIN dependency_columns_on_keys K ON C.id = K.column_id AND C.table_id = K.table_id WHERE T.name IN ( 'tblincome', 'tblcost' ) AND C.name IN ( 'id', 'subid' );
Naming of Constraints
We saw earlier that the name of constraint is created automatically by concatenating a) table name, b) column name, c) constraint type ( tblturnover_id_pkey ). If the constraint encompasses several columns, for example id and subid, then the name will be like tblturnover_id_subid_ pkey. If we want to give our constraint a name then we have to use this syntax in CREATE TABLE statement:
We will now give names to our constraints by using ALTER TABLE statement. We can not create another primary key constraint because table can only have one primary key constraint.
On the other side, it is possible to have many UNIQUE constraints on one table. ALTER TABLE tblCost ADD CONSTRAINT Uniquous UNIQUE ( ID, SubID );
Now we have three constraints. We still have old UNIQUE constraint, and we have two new constraints with custom names. SELECT T.id, T.name, C.id, C.name, K.key_id, K.key_name, K.key_type FROM sys.tables T INNER JOIN sys.columns C ON T.id = C.table_id INNER JOIN dependency_columns_on_keys K ON C.id = K.column_id AND C.table_id = K.table_id WHERE T.name IN ( 'tblincome', 'tblcost' ) AND C.name IN ( 'id', 'subid' );
On the image above, we have a new column "key_type". Zero in this column means that we are dealing with primary key constraint, and 1 is unique constraint.
In the table sys.keytypes we can see that there are 4 types of constraints. CHECK constraint is not supported in MonetDB.
SELECT * FROM sys.key_types;
Foreign Key Constraint
We know that in Excel, "data validation" allows us to restrict that the user can only enter values (2) from a predefined list (1). Similarly, on the image below, the "key_type" column can only accept values 0,1,2,3,4 because those are the only values listed in the main table sis.key_types (3). In relational databases, a list (1) is located within the Primary Key column (4) of the table that performs the function of a "Parent Table". The list (2), where we are limiting what to enter, is in the "Child Table", in the column declared as a "Foreign Key" (5).
This "primary key" > "foreign key" relation is called foreign key constraint, because "primary key" column defines what can be entered into "foreign key" column. When creating a table, we should use a syntax like this one to create foreign key constraint:
We can omit the part ", CONSTRAINT PkFk", but in that case our constraint will get the default name " tab3_col1_col2_fkey".
We can find data about foreign key constraints in this system table:
SELECT * FROM sys.dependency_tables_on_foreignkeys;
We will now add foreign key constraints with ALTER TABLE statement. We will try to link "id" columns. This will fail. Foreign key constraint can only relate to column ( or columns ) that are declared as primary key or as unique column.
ALTER TABLE tblCost ADD CONSTRAINT FromTblIncomeConstraint FOREIGN KEY ( id ) REFERENCES tblIncome ( id );
We will alter our statement to relate to primary key in the table tblIncome. Current primary key in the table tblIncome is composite key ( id, subid ). We will use that.
Foreign key constraint now limits what can be placed into tblCost table.
We can not add "4" into ID column in the table tblCost. That would violate the foreign key constraint.
We can not delete any row from the table tblIncome because each row is referenced in the tblCost table, and that would, again, violate the foreign key constraint.
RESTRICT / CASCADE when Deleting Constraints and Columns
We will try to delete ID column in tblCost table. ALTER TABLE tblCost DROP COLUMN ID;
We will try to delete primary key constraint in the tblIncome table. ALTER TABLE tblIncome DROP CONSTRAINT Primarius;
We will fail big. This is because the default mode is RESTRICT. By using CASCADE, we would be able to delete this column and this constraint.
START TRANSACTION; ALTER TABLE tblIncome DROP CONSTRAINT Primarius CASCADE; ROLLBACK;
If we rollback our transaction, we want really delete the constraint. I don't want to delete it.
NULL Handling
We can not insert NULL into primary key column. Primary key column doesn't allow nulls.
Column IDwithNULL in tblCost table has two fields with nulls. On such column we can add UNIQUE constraint. This is because mod "NULLS DISTINCT" is the default. But if we use mode "NULLS NOT DISTINCT", it will fail because we already have two nulls. > ALTER TABLE tblCost ADD UNIQUE NULLS NOT DISTINCT ( IDwithNULL );
The same statement will succeed if the column has only one NULL, which is the case in the tblIncome table. Because our null is single, then our column can be unique. ALTER TABLE tblIncome ADD UNIQUE NULLS NOT DISTINCT ( IDwithNULL );
tblIncome
tblCost
What is Not Supported in Foreign Key Constraint
For foreign key constraint, match can be only SIMPLE, on update can only be RESTRICT, on delete can only be RESTRICT. Other options are not supported so we will ignore them.
The ON UPDATE and ON DELETE subclauses mean that it is not possible to update or delete a key in the parent table if there is a row in the child table that referenced that key.
MATCH SIMPLE means that if a foreign key is a composite key ( it is made of several columns ), and if one of the columns in the foreign key has a null, then the referential integrity will not be checked for that foreign key.
Error Avoidance
As always, if we use IF NOT EXISTS or IF EXISTS, we will avoid errors. We will always get "operation successful".
CREATE TABLE IF NOT EXISTS employees ( id INT PRIMARY KEY, name VARCHAR(100), department VARCHAR(50), salary DECIMAL(10, 2) );
ALTER TABLE IF EXISTS employees ADD COLUMN hire_date DATE;
