Constraint can be applied to specify rules on a database. There are many constraints but we will only discuss the most commonly used in this blog post.
This serves as a unique identifier for each observation in a database table. A database table can only contain one primary key which must be unique values and cannot contain null values.
CREATE TABLE CUSTOMER (customer_id INTEGER NOT NULL, last_name CHAR (10), first_name CHAR (10), age INTEGER, PRIMARY_KEY (customer_id) );
This constraint can be used to link two tables together. A table with foreign key is known as the child table, while a table with primary key is called as the parent table.
CREATE TABLE PAYMENT (customer_id INTEGER, check_no INTEGER, payment_date DATE, amount DOUBLE, PRIMARY_KEY (customer_id) FOREIGN_KEY(customer_id) REFERENCES CUSTOMER(customer_id) );
To ensure all rows in a table contains a value for the column specified as not null. This constraint can be applied to any datatype including character, real and so on. For instance, on the CUSTOMER relation, customer_id cannot be a null value and this can be done using:
CREATE TABLE CUSTOMER (customer_id INTEGER NOT NULL, last_name CHAR (10), first_name CHAR (10), age INTEGER);
To ensure each value in a column has a distinct value and the particular column can have a null value but the values cannot be duplicated. So in this case we can apply to the table and ensure each customer has a unique id.
CREATE TABLE CUSTOMER (customer_id INTEGER NOT NULL UNIQUE, last_name CHAR (10), first_name CHAR (10), age INTEGER);
Categories: SQL Learning