SQL – Constraints

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.

PRIMARY KEY 

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) 

 );

 

FOREIGN KEY

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) 

 );

 

NOT NULL

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);

 

UNIQUE

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);

Screen Shot 2018-01-06 at 11.00.17 PM

Advertisements


Categories: SQL Learning

Tags: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: