1

This is how I create the table:

CREATE  TABLE IF NOT EXISTS division (
DIV_ID INTEGER PRIMARY KEY ,
DIV_CODE VARCHAR(10) ,
DIV_NAME VARCHAR(20) ,
GR_ID INTEGER ,
UNIQUE (DIV_CODE, DIV_NAME, GR_ID) ,
CONSTRAINT FK1 FOREIGN KEY (GR_ID) REFERENCES grade(GR_ID) )

If a row exists where DIV_CODE="1", DIV_NAME="1" and GR_ID=1, and I try inserting another row with values "1", "1" and 1, then I get a SQL integrity error, which is expected.

If a row exists where DIV_CODE="1", DIV_NAME="1" and GR_ID=null, and I try inserting another row with vales "1", "1" and null, then the new row is inserted. I now have 2 rows with the same values for each column. Can I setup the table differently to get a similar integrity error for this scenario?

Thanks

EDIT removed NOT NULL from GR_ID definition.

spyder man
  • 81
  • 7
  • 1
    Consider always naming your constraints. It can be painful in some upgrade scenarios if you use the server supplied names. – StingyJack Jul 24 '15 at 16:49
  • 1
    Which DBMS are you using? Different vendors behave differently –  Mar 01 '16 at 14:54
  • @a_horse_with_no_name This is in HSQLDB. – spyder man Mar 03 '16 at 10:00
  • @StingyJack 2 years later I see the wisdom in your comment :) – spyder man Jun 14 '17 at 08:47
  • 5yrs later...different project, different table, same problem, any takers? @a_horse_with_no_name I'm now on mySQL. – spyder man Aug 21 '21 at 03:51
  • Make up your mind, please tag appropriately, which DBMS? Also please do not add EDITs, just edit. Also please clarify via edits, not comments. – philipxy Aug 21 '21 at 03:59
  • https://stackoverflow.com/q/56093677/3404097 + UNIQUE. https://stackoverflow.com/q/42064759/3404097. – philipxy Aug 21 '21 at 05:34
  • Does this answer your question? [How to do unique constraint works with NULL value in MySQL](https://stackoverflow.com/questions/42064759/how-to-do-unique-constraint-works-with-null-value-in-mysql) – philipxy Aug 21 '21 at 05:37
  • Please reaserch before considering posting a question & reflect your research. "A generated column can be part of a foreign key or unique constraints or a column of an index. This capability is the main reason for using generated columns. A generated column may contain a formula that computes a value based on the values of other columns. Fast searches of the computed value can be performed when an index is declared on the generated column. Or the computed values can be declared to be unique, using a UNIQUE constraint on the table." – philipxy Aug 21 '21 at 06:16

0 Answers0