Create Oracle Database Table and Specify Some Constraints
The following are the main constraints which can be defined when you are creating a TABLE. I will be using the ORACLE DBMS .
- Primary key – The primary key of the table to be created.
- Foreign key – The primary key of some other table which is being referenced in this table to be created. (The other table should already exist with the column being referenced before calling it in this creation statement)
- Unique – unique (for candidate keys)
- Required (NOT NULL) – No Null Values are allowed
- Check – The column should be some specified createria –
and you can either place the contrainst specification as either :
- External: after column definitions
- Inline: same line as a column definition
All of these constraints can be external or inline in Oracle. Typically required (NOT NULL) constraints are inline and others are external.
Here is a basic TABLE creation syntax in Oracle
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
--CREATE HOTEL LOCATION TABLE CREATE TABLE HOTEL_LOCATION( LOCATION_ID INT, ISCITY CHAR(1) CONSTRAINT HL_ISCITY NOT NULL, NAME VARCHAR2(255), DISTANCE_FROM_AIRPORT INTEGER, CONSTRAINT HL_LOCATION_ID PRIMARY KEY (LOCATION_ID) ); --CREATE HOTEL TABLE CREATE TABLE HOTEL_1( HOTELID INTEGER, HOTELNAME VARCHAR(255), DESCRIPTION VARCHAR(255), IS_FIVE_STAR CHAR(1), LOCATION_ID INT, NUMROOMS INTEGER, CONSTRAINT PK_HOTEL_1 PRIMARY KEY (HOTELID), CONSTRAINT FK_LOCATION_ID FOREIGN KEY (LOCATION_ID) REFERENCES HOTEL_LOCATION, CONSTRAINT HOTEL_1 CHECK (NUMROOMS BETWEEN 1 AND 325) ); |
Here is the table created with the specified constraints
Want more information like this?
People Who Read The Above Post Also Read This:
Free Entity Relationship and Relational Schema Diagram Tool
Creating Oracle PL/SQL Tables for Intercollegiate Athletic Database – Data Warehousing
Teradata Viewpoint – SQL Scratchpad – Writing Queries Dillard’s Department Store Database
Using Subqueries To Join 2 More Tables Which Do Not Have Unique Primary and Foreign Keys
