February 2, 2017 at 3:05 am
Hello ALL!
I have this table
CREATE TABLE [dbo].[students](
[id] [int] NOT NULL,
[studentusa] [int] NULL,
[studentuk] [int] NULL,
[courseid] [int] NOT NULL
)
I dont want to have a situation that both columns [studentusa] and [studentuk] will be null in the same raw.
i want to create a constraint that only one of this columns [studentusa] and [studentuk] will be null.
Can you please advise?
February 2, 2017 at 3:27 am
89netanel - Thursday, February 2, 2017 3:05 AMHello ALL!I have this table
CREATE TABLE [dbo].[students](
[id] [int] NOT NULL,
[studentusa] [int] NULL,
[studentuk] [int] NULL,
[courseid] [int] NOT NULL
)I dont want to have a situation that both columns [studentusa] and [studentuk] will be null in the same raw.
i want to create a constraint that only one of this columns [studentusa] and [studentuk] will be null.
Can you please advise?
This looks like a homework question, so I'm not going to write the code for you but here is something to think about.
If you do it with this structure, what happens if you then need to cater for students from France, Germany or any country other the UK or USA? You're setting yourself up for a lot of problems when enquiring upon this table - you'll need a query that does one thing to check UK students and something else for USA students. Do you intend to add a new column every time another country is required?
Surely one column that refers to a table of countries would be more flexible.
Have a go and if you need any further help show us what you've tried or ask if you need more explanation.
February 2, 2017 at 6:02 am
89netanel - Thursday, February 2, 2017 3:05 AMHello ALL!I have this table
CREATE TABLE [dbo].[students](
[id] [int] NOT NULL,
[studentusa] [int] NULL,
[studentuk] [int] NULL,
[courseid] [int] NOT NULL
)I dont want to have a situation that both columns [studentusa] and [studentuk] will be null in the same raw.
i want to create a constraint that only one of this columns [studentusa] and [studentuk] will be null.
Can you please advise?
You need a CHECK constraint. Take a look here for more details.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 2, 2017 at 12:53 pm
Phil Parkin - Thursday, February 2, 2017 6:02 AM89netanel - Thursday, February 2, 2017 3:05 AMHello ALL!I have this table
CREATE TABLE [dbo].[students](
[id] [int] NOT NULL,
[studentusa] [int] NULL,
[studentuk] [int] NULL,
[courseid] [int] NOT NULL
)I dont want to have a situation that both columns [studentusa] and [studentuk] will be null in the same raw.
i want to create a constraint that only one of this columns [studentusa] and [studentuk] will be null.
Can you please advise?You need a CHECK constraint. Take a look here for more details.
Yes
i would like to use a check constraint
but how can i do that?
If i try:
check studentusa is not null AND studentusa is not null
it is wrong constraint
also
check studentusa is not null OR studentusa is not null
how can i create good check constraint?
February 2, 2017 at 1:19 pm
89netanel - Thursday, February 2, 2017 12:53 PMPhil Parkin - Thursday, February 2, 2017 6:02 AM89netanel - Thursday, February 2, 2017 3:05 AMHello ALL!I have this table
CREATE TABLE [dbo].[students](
[id] [int] NOT NULL,
[studentusa] [int] NULL,
[studentuk] [int] NULL,
[courseid] [int] NOT NULL
)I dont want to have a situation that both columns [studentusa] and [studentuk] will be null in the same raw.
i want to create a constraint that only one of this columns [studentusa] and [studentuk] will be null.
Can you please advise?You need a CHECK constraint. Take a look here for more details.
Yes
i would like to use a check constraint
but how can i do that?
If i try:
check studentusa is not null AND studentusa is not null
it is wrong constraint
also
check studentusa is not null OR studentusa is not nullhow can i create good check constraint?
How about
check ((a is null and b is not null) or (a is not null and b is null))
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 10, 2017 at 12:51 pm
89netanel - Thursday, February 2, 2017 3:05 AMHello ALL!I have this table
CREATE TABLE [dbo].[students](
[id] [int] NOT NULL,
[studentusa] [int] NULL,
[studentuk] [int] NULL,
[courseid] [int] NOT NULL
)I dont want to have a situation that both columns [studentusa] and [studentuk] will be null in the same raw.
i want to create a constraint that only one of this columns [studentusa] and [studentuk] will be null.
Can you please advise?
While you do need a CHECK constraint to achieve that goal, you may be creating a nightmare. It would make a great deal more sense to have a CountryID column with a foreign key reference to a Country table that has all the valid countries that you want. That way, writing a query against the table to seek out only US students or only UK students will be the same query except for the value for the country name that you want to test for. You don't have to spell out the country name in the country table, as abbreviations can work, and if you really need the full names, you could have a separate additional field in that country table with the full name in it.
If you continue down your current road, every time you add a country you have to change all your queries significantly, plus add a column, which then induces the need for a change to the CHECK constraint. Add just a couple of countries and you'll get sick and tired of the changes and ongoing maintenance. With the country table, you can support having another country just by inserting the necessary values in the country table, and nothing else has to change except the value supplied to your WHERE clause for any query that wants to see students associated with the new country. Believe me, that's a SIGNIFICANTLY EASIER road to go down.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 15, 2017 at 3:32 pm
Your design is fundamentally wrong. First of all, there is no such thing as a magic, universal generic "id" in RDBMS; it has to be an identifier of something in particular. And on top of that it cannot be numeric because you do not do any math with it. Also, this non-table has no key. Remember, that is the very definition of a table.
You have committed the design flaw of attributes splitting. You put a country code into account, so those columns are not true scalar values.
Do a little research and find the ISO 3166 country codes. They will be a column in a correctly designed normalize table.
CREATE TABLE Students
(student_cnt INTEGER NOT NULL
CHECK (student_cnt >= 0),
student_country_code CHAR(3) NOT NULL,
REFERENCES ISO_Country_Codes (country_code),
course_id CHAR(5) NOT NULL,
PRIMARY KEY (student_country_code, course_id)
);
>> I want to create a constraint that only one of this columns [studentusa] and [studentuk] will be null. <<
Why? Do you just like wasting space and creating overly complex constraints that hide information? If you use the country code, did you notice how easy it is to expand your design when you get foreign students from other countries?
Most of the work in SQL is done in the DDL, and not with fancy constraints or the DML.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 16, 2017 at 4:54 pm
89netanel - Thursday, February 2, 2017 3:05 AM>> I have this table <<
Your design is fundamentally wrong. First of all, there is no such thing as a magic, universal generic "id" in RDBMS; it has to be an identifier of something in particular. And on top of that it cannot be numeric because you do not do any math with it. Also, this non-table has no key. Remember, that is the very definition of a table.
You have committed the design flaw of attributes splitting. You put a country code into account, so those columns are not true scalar values.
Do a little research and find the ISO 3166 country codes. They will be a column in a correctly designed normalize table.
CREATE TABLE Students
(student_cnt INTEGER NOT NULL
CHECK (student_cnt >= 0),
student_country_code CHAR(3) NOT NULL,
REFERENCES ISO_Country_Codes (country_code),
course_id CHAR(5) NOT NULL,
PRIMARY KEY (student_country_code, course_id)
);
>> I want to create a constraint that only one of this columns [studentusa] and [studentuk] will be null. <<
Why? Do you just like wasting space and creating overly complex constraints that hide information? If you use the country code, did you notice how easy it is to expand your design when you get foreign students from other countries?
Most of the work in SQL is done in the DDL, and not with fancy constraints or the DML.
So country code and course id represent a unique key? So we're limited to a single student from the same country taking a given course? And, btw, never again for a future offering of the course. Does that really make any sense at all?
I have no problem with an iso country code, but a smallint representing that country code would work just as well, and be more efficient. Besides, who knows, I suspect country codes may actually change if countries split / combine / get taken over / etc. in the future.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 17, 2017 at 7:21 am
89netanel - Thursday, February 2, 2017 3:05 AM
So country code and course id represent a unique key? So we're limited to a single student from the same country taking a given course? And, btw, never again for a future offering of the course. Does that really make any sense at all?
My guess, given no specs , is the purpose of this table is to summarize students courses in countries without any regard individuals.
I have no problem with an iso country code, but a smallint representing that country code would work just as well, and be more efficient. Besides, who knows, I suspect country codes may actually change if countries split / combine / get taken over / etc. in the future.
But this is why a small integer is a horrible choice. You do not do any math on it, do you? So should never be a numeric. But more than that inventing your own country codes is like inventing your own system of weights and measures. You cannot exchange information with any other database. When countries split combined change or whatever, you have to do all of that work yourself. Why not let ISO do all that work for you, just like they did with the metric system. 🙂
Please post DDL and follow ANSI/ISO standards when asking for help.
February 17, 2017 at 7:58 am
jcelko212 32090 - Wednesday, February 15, 2017 3:32 PM89netanel - Thursday, February 2, 2017 3:05 AMSo country code and course id represent a unique key? So we're limited to a single student from the same country taking a given course? And, btw, never again for a future offering of the course. Does that really make any sense at all?
My guess, given no specs , is the purpose of this table is to summarize students courses in countries without any regard individuals.
I have no problem with an iso country code, but a smallint representing that country code would work just as well, and be more efficient. Besides, who knows, I suspect country codes may actually change if countries split / combine / get taken over / etc. in the future.
But this is why a small integer is a horrible choice. You do not do any math on it, do you? So should never be a numeric. But more than that inventing your own country codes is like inventing your own system of weights and measures. You cannot exchange information with any other database. When countries split combined change or whatever, you have to do all of that work yourself. Why not let ISO do all that work for you, just like they did with the metric system. 🙂
Well there is an ISO standard for 3 digit country codes and some systems do use that so a small int would be a perfectly reasonable choice. Personally I prefer 3 character country codes but to each their own.
February 17, 2017 at 9:02 am
jcelko212 32090 - Wednesday, February 15, 2017 3:32 PM89netanel - Thursday, February 2, 2017 3:05 AMSo country code and course id represent a unique key? So we're limited to a single student from the same country taking a given course? And, btw, never again for a future offering of the course. Does that really make any sense at all?
My guess, given no specs , is the purpose of this table is to summarize students courses in countries without any regard individuals.
I have no problem with an iso country code, but a smallint representing that country code would work just as well, and be more efficient. Besides, who knows, I suspect country codes may actually change if countries split / combine / get taken over / etc. in the future.
But this is why a small integer is a horrible choice. You do not do any math on it, do you? So should never be a numeric. But more than that inventing your own country codes is like inventing your own system of weights and measures. You cannot exchange information with any other database. When countries split combined change or whatever, you have to do all of that work yourself. Why not let ISO do all that work for you, just like they did with the metric system. 🙂
No, I don't do any math on it. And I'm using the standard number codes, not inventing my own. But I get automatic domain checking by using smallint, i.e., I *know* the value is numeric. And they process much more efficiently.
Of course I can exchange info: I simply convert the smallint to the 3-char country if/when needed, for export or for internal use. I'm not saying any user ever sees that number, it's purely an internal code. Using the number should reduce maintenance too, since country names change more often than country boundaries.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply