CODEWITHSUNDEEP
oracle-database
Ana Mar
Ana Mar

Reputation: 31

Super Beginner - Oracle SQL

I am beginning to learn about SQL and our professor told us to do 6 tables with their respective indexes and constraints.

The datatype for some of the columns has to be a positive integer greater than 1. How do I specify this? I already created the table but accidentally put 

CREATE TABLE Loan (
    LoanID number  NOT NULL CHECK (0>=1),

and I am scared I messed up the whole thing.

Sorry if the answer is too obvious. I don't know much about sql.

Upvotes: 1

Views: 65

Answers (3)

SimplyInk
SimplyInk

Reputation: 6852

MySQL / Oracle / MS SQL Server

Source: http://www.w3schools.com/sql/sql_check.asp

CREATE TABLE Loan1 ( LoanID int NOT NULL, CONSTRAINT chk_Loan1 CHECK (LoanID>1) );
CREATE TABLE Loan2 ( LoanID int NOT NULL, CONSTRAINT chk_Loan2 CHECK (LoanID>1) );
...
Select * From Loan1;
Select * From Loan2;
...

Also see: SQL Data Types

Upvotes: 0

Sachu
Sachu

Reputation: 7766

example for setting a constraint for a table column as positive integer is 

CREATE TABLE Loan 
( LoanID INT UNSIGNED NOT NULL 
)

UNSIGNED will make it as no sign so it will accept only positive integers

IF you need to set as accept something greater than a value you can use CHECK constraint it will be

CREATE TABLE Loan
(
   LoanID int unsigned NOT NULL CHECK (LoanID > 1)
)

but as per MYSQL documentation

The CHECK clause is parsed but ignored by all storage engines.

Upvotes: 1

Code Different
Code Different

Reputation: 93191

You got it mostly right:

CREATE TABLE Loan
(
   LoanID int NOT NULL CHECK (LoanID > 1)
)

Upvotes: 0

Related Questions