CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle-sqldevelopercreate-table
Suresh Madhusanka Rodrigo
Suresh Madhusanka Rodrigo

Reputation: 141

How to write a create table script based on certain column values in Oracle DB?

I want to write a create table script based on certain conditions of column values in the Oracle table.

For example I have a table called A and in this table we have 3 columns Country, Country Code and Country Short Name.

If Country column has a value then Country Code and Country Short Name columns may or may not have values.

If Country column not have a value then Country Code and Country Short Name columns have to be mandatory.

enter image description here

How can I write a create table script for this in Oracle DB?

Upvotes: 0

Views: 253

Answers (2)

Younes
Younes

Reputation: 46

you can do it as below, just make sure that the conditions are okay

create table tableA (
country varchar2(20),
countryCode varchar2(20),
countryshort varchar2(20),
constraint chk check ((country is null and ( countryCode is not null or countryshort is not null) ) or (country is not null and  (countryCode is null or countryshort is null)))
);

Upvotes: 1

gsalem
gsalem

Reputation: 2028

Yes it can be done, here's an example with just 2 columns, you can easily expand it to 3 or more:

create table tcon (c1 number, c2 number, constraint ck  check ((c1 is not null) or (c1 is null and c2 is not null)));

Upvotes: 2

Related Questions