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

Question

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.

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

Yes you can! Please check the documentation of create table, insert and NULL. — Marmite Bomber
– Marmite Bomber, Commented Jan 5, 2022 at 13:06
Creating a table should be a one time thing. Why bother with a script and just create it manually and get it over and done with or is there and underlying problem your trying to solve, which you haven't mentioned — Pugzly
– Pugzly, Commented Jan 5, 2022 at 13:24

gsalem · Accepted Answer · 2022-01-05 13:29:55Z

2

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

answered Jan 5, 2022 at 13:29

gsalem

2,0281 gold badge10 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Younes · Accepted Answer · 2022-01-05 13:38:13Z

1

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

answered Jan 5, 2022 at 13:38

Younes

462 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related