Styjun

What is my clock telling me to do?

Why tantalum for the Hayabusa bullets?

What are the closest international airports in different countries?

May a hotel provide accommodation for fewer people than booked?

How can Paypal know my card is being used in another account?

How to have poached eggs in "sphere form"?

Is it possible to tell if a child will turn into a Hag?

Microgravity indicators

Is it bad style if the personal first person narrator of a story dies during said story?

What is a good example for artistic ND filter applications?

What language is Raven using for her attack in the new 52?

What clothes would flying-people wear?

What is a Trio Word™?

Antonym of "Megalomania"

To which atoms correspond left and right in showlists?

What is the source of this clause, often used to mark the completion of something?

How do I say "this is why…"?

Does dual boot harm a laptop battery or reduce its life?

Boots or trail runners with reference to blisters?

Why doesn't the Gottesman-Knill theorem render quantum computing almost useless?

Prepare a user to perform an action before proceeding to the next step

What is the reason for cards stating "Until end of turn, you don't lose this mana as steps and phases end"?

Security measures that could plausibly last 150+ years?

Why don't short runways use ramps for takeoff?

How can I create a check to make sure only one entry in the column can have a specific value based on an id from a different column in SQL?

How to return only the Date from a SQL Server DateTime datatypeSQLite - UPSERT *not* INSERT or REPLACEHow can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?How can I get column names from a table in SQL Server?Restrict varchar() column to specific values?SQL select only rows with max value on a columnBest practices for SQL varchar column lengthSQL - Only UNIQUE or PRIMARY KEY constraints can be created on computed columnsHow to Insert Values From Column A to Column B From Two Different TablesSQL Server : how to force NOT NULL in a column only if another column in the same table is a specific value?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

I am trying to create a new table in SQL Developer that has a four columns. In one column there is a numerical value called ORG_ID, this ORG_ID can be the same across multiple entries in the table. Another column is called DEFAULT_FLAG, this column only contains a Y or N character denoting if it is the default entry for the table for that ORG_ID.

I am trying to create a CHECK in the DEFAULT_FLAG column that makes sure there is only one entry with a Y for all entries with the same ORG_ID. Here is an example of what it would look like:

xxxx|xxxx|ORG_ID|DEFAULT_FLAG
xxxx|xxxx|123456| Y
xxxx|xxxx|123456| N
xxxx|xxxx|987654| Y
xxxx|xxxx|567495| Y

In the above table, the second entry for ORG_ID 123456 would need to be rejected if Y was inserted as the DEFAULT_FLAG.

I'm a little new to SQL, so I've done my research of needing to use a constraint and check on the column. I tried writing my own but it did not work, the code is below.

default_flag varchar(1)
constraint one_default Check(ORG_ID AND DEFAULT_FLAG != "Y"),

sql oracle

share|improve this question

edited Mar 26 at 21:36

sdj

asked Mar 26 at 21:26

sdjsdj

622 bronze badges

add a comment | 

1

I am trying to create a new table in SQL Developer that has a four columns. In one column there is a numerical value called ORG_ID, this ORG_ID can be the same across multiple entries in the table. Another column is called DEFAULT_FLAG, this column only contains a Y or N character denoting if it is the default entry for the table for that ORG_ID.

I am trying to create a CHECK in the DEFAULT_FLAG column that makes sure there is only one entry with a Y for all entries with the same ORG_ID. Here is an example of what it would look like:

xxxx|xxxx|ORG_ID|DEFAULT_FLAG
xxxx|xxxx|123456| Y
xxxx|xxxx|123456| N
xxxx|xxxx|987654| Y
xxxx|xxxx|567495| Y

In the above table, the second entry for ORG_ID 123456 would need to be rejected if Y was inserted as the DEFAULT_FLAG.

I'm a little new to SQL, so I've done my research of needing to use a constraint and check on the column. I tried writing my own but it did not work, the code is below.

default_flag varchar(1)
constraint one_default Check(ORG_ID AND DEFAULT_FLAG != "Y"),

sql oracle

share|improve this question

edited Mar 26 at 21:36

sdj

asked Mar 26 at 21:26

sdjsdj

622 bronze badges

add a comment | 

I am trying to create a new table in SQL Developer that has a four columns. In one column there is a numerical value called ORG_ID, this ORG_ID can be the same across multiple entries in the table. Another column is called DEFAULT_FLAG, this column only contains a Y or N character denoting if it is the default entry for the table for that ORG_ID.

I am trying to create a CHECK in the DEFAULT_FLAG column that makes sure there is only one entry with a Y for all entries with the same ORG_ID. Here is an example of what it would look like:

xxxx|xxxx|ORG_ID|DEFAULT_FLAG
xxxx|xxxx|123456| Y
xxxx|xxxx|123456| N
xxxx|xxxx|987654| Y
xxxx|xxxx|567495| Y

In the above table, the second entry for ORG_ID 123456 would need to be rejected if Y was inserted as the DEFAULT_FLAG.

I'm a little new to SQL, so I've done my research of needing to use a constraint and check on the column. I tried writing my own but it did not work, the code is below.

default_flag varchar(1)
constraint one_default Check(ORG_ID AND DEFAULT_FLAG != "Y"),

sql oracle

share|improve this question

edited Mar 26 at 21:36

sdj

asked Mar 26 at 21:26

sdjsdj

622 bronze badges

xxxx|xxxx|ORG_ID|DEFAULT_FLAG
xxxx|xxxx|123456| Y
xxxx|xxxx|123456| N
xxxx|xxxx|987654| Y
xxxx|xxxx|567495| Y

default_flag varchar(1)
constraint one_default Check(ORG_ID AND DEFAULT_FLAG != "Y"),

share|improve this question

edited Mar 26 at 21:36

sdj

asked Mar 26 at 21:26

sdjsdj

622 bronze badges

share|improve this question

edited Mar 26 at 21:36

sdj

asked Mar 26 at 21:26

sdjsdj

622 bronze badges

asked Mar 26 at 21:26

sdjsdj

622 bronze badges

asked Mar 26 at 21:26

sdjsdj

622 bronze badges

3 Answers
3

active

oldest

votes

0

This is too long for a comment.

You are trying to use a check constraint for something it is not designed for. You have an org_id. You should have an organizations table that uses this id as its primary key.

Then, then flag you want to store should be in the organizations table. Voila! The flag is only stored once. You don't need to worry about keeping it in synch between different rows.

share|improve this answer

answered Mar 26 at 21:52

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How would a flag in the organizations table tell you which of the existing table's rows is the default one for an org_id?
  
  – Thorsten Kettner
  Mar 26 at 21:58
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ThorstenKettner . . . The organizations table out have only one row per org_id. It would be the primary key.
  
  – Gordon Linoff
  Mar 27 at 0:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, but the existing table has multiple entries per org_id. One of them per org_id is the default, the others are not. A mere flag in the organizations table cannot tell us which of the existing table's rows are the default rows.
  
  – Thorsten Kettner
  Mar 27 at 7:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ThorstenKettner . . . As I read the question, there is only one default value: "In the above table, the second entry for ORG_ID 123456 would need to be rejected if Y was inserted as the DEFAULT_FLAG.".
  
  – Gordon Linoff
  Mar 27 at 11:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm a little confused as to how storing the flag in the organizations table will help. To elaborate a little on the question, the current table is for a list of warehouses. One organization can have multiple warehouses, but only one warehouse is the default. So are you saying to store the default warehouse in the organization table, rather than keep a flag in the warehouse table for the default warehouse that belongs to an organization?
  
  – sdj
  Mar 27 at 12:47
  

  
  
  
  

 | 
show 1 more comment

0

Create a unique index for all ORG_ID entries with a 'Y', so each ORG_ID can only have one row with a 'Y':

create unique index idx on mytable(case when default_flag = 'Y' then org_id end)

share|improve this answer

answered Mar 26 at 21:52

Thorsten KettnerThorsten Kettner

54.3k33 gold badges2727 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't think this will work, because there will be repeated, non-unique, NULL entries for all the places that default_flag is not 'Y'.
  
  – Andrew Lazarus
  Mar 26 at 21:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Andrew Lazarus: I think the requirement is that there is no more than one default row per org_id. This is what the index ensures. Have I misread the request?
  
  – Thorsten Kettner
  Mar 26 at 21:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What enters into the index for the rows where default_flag is 'N'?
  
  – Andrew Lazarus
  Mar 26 at 22:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Andrew Lazarus: Nothing. Nulls don't make it into the index.
  
  – Thorsten Kettner
  Mar 26 at 22:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Tested. NULLs do go into the index, at least in Postgres, but since it is not the case that NULL = NULL they don't violate uniqueness. (They go into the index for sorts.)
  
  – Andrew Lazarus
  Mar 26 at 22:09
  

  
  
  
  

 | 
show 2 more comments

0

I think a technically-better solution than the one from Thorsten Kettner, but using the same idea, is

CREATE UNIQUE INDEX ON mytable(org_id)
WHERE default_flag = 'Y';

But let me also suggest a table organization_defaults with two columns, one the ID for an organization and the other the ID for mytable is a better approach, as suggested in comments to the OP.

share|improve this answer

answered Mar 26 at 22:01

Andrew LazarusAndrew Lazarus

11.4k22 gold badges2222 silver badges4747 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Does Oracle support partial indexes? That would be news to me. I know them from PostgreSQL, but I've never seen them in Oracle.
  
  – Thorsten Kettner
  Mar 26 at 22:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't know. I've stuck with Postgresql, with a brief forced journey to MySQL. Now learning MonetDB [columnar], which doesn't really support indexes except in name, partial or otherwise. You may guess from this list how much my employers were willing to spend on a database.
  
  – Andrew Lazarus
  Mar 26 at 22:24
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55366447%2fhow-can-i-create-a-check-to-make-sure-only-one-entry-in-the-column-can-have-a-sp%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

This is too long for a comment.

You are trying to use a check constraint for something it is not designed for. You have an org_id. You should have an organizations table that uses this id as its primary key.

Then, then flag you want to store should be in the organizations table. Voila! The flag is only stored once. You don't need to worry about keeping it in synch between different rows.

share|improve this answer

answered Mar 26 at 21:52

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How would a flag in the organizations table tell you which of the existing table's rows is the default one for an org_id?
  
  – Thorsten Kettner
  Mar 26 at 21:58
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ThorstenKettner . . . The organizations table out have only one row per org_id. It would be the primary key.
  
  – Gordon Linoff
  Mar 27 at 0:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, but the existing table has multiple entries per org_id. One of them per org_id is the default, the others are not. A mere flag in the organizations table cannot tell us which of the existing table's rows are the default rows.
  
  – Thorsten Kettner
  Mar 27 at 7:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ThorstenKettner . . . As I read the question, there is only one default value: "In the above table, the second entry for ORG_ID 123456 would need to be rejected if Y was inserted as the DEFAULT_FLAG.".
  
  – Gordon Linoff
  Mar 27 at 11:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm a little confused as to how storing the flag in the organizations table will help. To elaborate a little on the question, the current table is for a list of warehouses. One organization can have multiple warehouses, but only one warehouse is the default. So are you saying to store the default warehouse in the organization table, rather than keep a flag in the warehouse table for the default warehouse that belongs to an organization?
  
  – sdj
  Mar 27 at 12:47
  

  
  
  
  

 | 
show 1 more comment

0

This is too long for a comment.

You are trying to use a check constraint for something it is not designed for. You have an org_id. You should have an organizations table that uses this id as its primary key.

Then, then flag you want to store should be in the organizations table. Voila! The flag is only stored once. You don't need to worry about keeping it in synch between different rows.

share|improve this answer

answered Mar 26 at 21:52

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  How would a flag in the organizations table tell you which of the existing table's rows is the default one for an org_id?
  
  – Thorsten Kettner
  Mar 26 at 21:58
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ThorstenKettner . . . The organizations table out have only one row per org_id. It would be the primary key.
  
  – Gordon Linoff
  Mar 27 at 0:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Yes, but the existing table has multiple entries per org_id. One of them per org_id is the default, the others are not. A mere flag in the organizations table cannot tell us which of the existing table's rows are the default rows.
  
  – Thorsten Kettner
  Mar 27 at 7:40
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @ThorstenKettner . . . As I read the question, there is only one default value: "In the above table, the second entry for ORG_ID 123456 would need to be rejected if Y was inserted as the DEFAULT_FLAG.".
  
  – Gordon Linoff
  Mar 27 at 11:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I'm a little confused as to how storing the flag in the organizations table will help. To elaborate a little on the question, the current table is for a list of warehouses. One organization can have multiple warehouses, but only one warehouse is the default. So are you saying to store the default warehouse in the organization table, rather than keep a flag in the warehouse table for the default warehouse that belongs to an organization?
  
  – sdj
  Mar 27 at 12:47
  

  
  
  
  

 | 
show 1 more comment

This is too long for a comment.

You are trying to use a check constraint for something it is not designed for. You have an org_id. You should have an organizations table that uses this id as its primary key.

Then, then flag you want to store should be in the organizations table. Voila! The flag is only stored once. You don't need to worry about keeping it in synch between different rows.

share|improve this answer

answered Mar 26 at 21:52

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

share|improve this answer

answered Mar 26 at 21:52

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

answered Mar 26 at 21:52

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

answered Mar 26 at 21:52

Gordon LinoffGordon Linoff

840k3838 gold badges343343 silver badges449449 bronze badges

0

Create a unique index for all ORG_ID entries with a 'Y', so each ORG_ID can only have one row with a 'Y':

create unique index idx on mytable(case when default_flag = 'Y' then org_id end)

share|improve this answer

answered Mar 26 at 21:52

Thorsten KettnerThorsten Kettner

54.3k33 gold badges2727 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't think this will work, because there will be repeated, non-unique, NULL entries for all the places that default_flag is not 'Y'.
  
  – Andrew Lazarus
  Mar 26 at 21:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Andrew Lazarus: I think the requirement is that there is no more than one default row per org_id. This is what the index ensures. Have I misread the request?
  
  – Thorsten Kettner
  Mar 26 at 21:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What enters into the index for the rows where default_flag is 'N'?
  
  – Andrew Lazarus
  Mar 26 at 22:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Andrew Lazarus: Nothing. Nulls don't make it into the index.
  
  – Thorsten Kettner
  Mar 26 at 22:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Tested. NULLs do go into the index, at least in Postgres, but since it is not the case that NULL = NULL they don't violate uniqueness. (They go into the index for sorts.)
  
  – Andrew Lazarus
  Mar 26 at 22:09
  

  
  
  
  

 | 
show 2 more comments

0

Create a unique index for all ORG_ID entries with a 'Y', so each ORG_ID can only have one row with a 'Y':

create unique index idx on mytable(case when default_flag = 'Y' then org_id end)

share|improve this answer

answered Mar 26 at 21:52

Thorsten KettnerThorsten Kettner

54.3k33 gold badges2727 silver badges4646 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't think this will work, because there will be repeated, non-unique, NULL entries for all the places that default_flag is not 'Y'.
  
  – Andrew Lazarus
  Mar 26 at 21:57
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Andrew Lazarus: I think the requirement is that there is no more than one default row per org_id. This is what the index ensures. Have I misread the request?
  
  – Thorsten Kettner
  Mar 26 at 21:59
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What enters into the index for the rows where default_flag is 'N'?
  
  – Andrew Lazarus
  Mar 26 at 22:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Andrew Lazarus: Nothing. Nulls don't make it into the index.
  
  – Thorsten Kettner
  Mar 26 at 22:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Tested. NULLs do go into the index, at least in Postgres, but since it is not the case that NULL = NULL they don't violate uniqueness. (They go into the index for sorts.)
  
  – Andrew Lazarus
  Mar 26 at 22:09
  

  
  
  
  

 | 
show 2 more comments

Create a unique index for all ORG_ID entries with a 'Y', so each ORG_ID can only have one row with a 'Y':

create unique index idx on mytable(case when default_flag = 'Y' then org_id end)

share|improve this answer

answered Mar 26 at 21:52

Thorsten KettnerThorsten Kettner

54.3k33 gold badges2727 silver badges4646 bronze badges

create unique index idx on mytable(case when default_flag = 'Y' then org_id end)

share|improve this answer

answered Mar 26 at 21:52

Thorsten KettnerThorsten Kettner

54.3k33 gold badges2727 silver badges4646 bronze badges

answered Mar 26 at 21:52

Thorsten KettnerThorsten Kettner

54.3k33 gold badges2727 silver badges4646 bronze badges

answered Mar 26 at 21:52

Thorsten KettnerThorsten Kettner

54.3k33 gold badges2727 silver badges4646 bronze badges

0

I think a technically-better solution than the one from Thorsten Kettner, but using the same idea, is

CREATE UNIQUE INDEX ON mytable(org_id)
WHERE default_flag = 'Y';

But let me also suggest a table organization_defaults with two columns, one the ID for an organization and the other the ID for mytable is a better approach, as suggested in comments to the OP.

share|improve this answer

answered Mar 26 at 22:01

Andrew LazarusAndrew Lazarus

11.4k22 gold badges2222 silver badges4747 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Does Oracle support partial indexes? That would be news to me. I know them from PostgreSQL, but I've never seen them in Oracle.
  
  – Thorsten Kettner
  Mar 26 at 22:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't know. I've stuck with Postgresql, with a brief forced journey to MySQL. Now learning MonetDB [columnar], which doesn't really support indexes except in name, partial or otherwise. You may guess from this list how much my employers were willing to spend on a database.
  
  – Andrew Lazarus
  Mar 26 at 22:24
  

  
  
  
  

add a comment | 

0

I think a technically-better solution than the one from Thorsten Kettner, but using the same idea, is

CREATE UNIQUE INDEX ON mytable(org_id)
WHERE default_flag = 'Y';

But let me also suggest a table organization_defaults with two columns, one the ID for an organization and the other the ID for mytable is a better approach, as suggested in comments to the OP.

share|improve this answer

answered Mar 26 at 22:01

Andrew LazarusAndrew Lazarus

11.4k22 gold badges2222 silver badges4747 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Does Oracle support partial indexes? That would be news to me. I know them from PostgreSQL, but I've never seen them in Oracle.
  
  – Thorsten Kettner
  Mar 26 at 22:22
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't know. I've stuck with Postgresql, with a brief forced journey to MySQL. Now learning MonetDB [columnar], which doesn't really support indexes except in name, partial or otherwise. You may guess from this list how much my employers were willing to spend on a database.
  
  – Andrew Lazarus
  Mar 26 at 22:24
  

  
  
  
  

add a comment | 

I think a technically-better solution than the one from Thorsten Kettner, but using the same idea, is

CREATE UNIQUE INDEX ON mytable(org_id)
WHERE default_flag = 'Y';

But let me also suggest a table organization_defaults with two columns, one the ID for an organization and the other the ID for mytable is a better approach, as suggested in comments to the OP.

share|improve this answer

answered Mar 26 at 22:01

Andrew LazarusAndrew Lazarus

11.4k22 gold badges2222 silver badges4747 bronze badges

CREATE UNIQUE INDEX ON mytable(org_id)
WHERE default_flag = 'Y';

share|improve this answer

answered Mar 26 at 22:01

Andrew LazarusAndrew Lazarus

11.4k22 gold badges2222 silver badges4747 bronze badges

answered Mar 26 at 22:01

Andrew LazarusAndrew Lazarus

11.4k22 gold badges2222 silver badges4747 bronze badges

answered Mar 26 at 22:01

Andrew LazarusAndrew Lazarus

11.4k22 gold badges2222 silver badges4747 bronze badges