CODEWITHSUNDEEP
sql
Mike
Mike

Reputation: 133

Foreign key Check constraints in table attribute

I am trying to set an attribute in an SQL table that is made up of a partial id that is designated by user input and part foreign key. For example: The employee will have a 8 character long id, the first 4 digits is the part the user will input and the last 4 characters will reference the branch table to use the branch id.

It looks like I need to use a CHECK constraint in order to do this, but I am unsure as to how to enforce the second part of the attribute value to ensure it is in the other table.

I am hoping thq question is phrased correctly as I am still learning SQL.

Upvotes: 0

Views: 589

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95512

It looks like I need to use a CHECK constraint in order to do this, but I am unsure as to how to enforce the second part of the attribute value to ensure it is in the other table.

In order to "look" into the other table, you'd need to use a SELECT statement within the CHECK constraint. As far as I know, most SQL dbms won't let you do that. (Failing that, standard SQL would use an assertion; I'm not sure any SQL dbms supports assertions yet.)

Instead, use two columns--one for the four characters the user supplies, and one for the last four characters representing the branch. You can (and should) use

  • a CHECK constraint on the column that stores user-supplied values to guarantee it's four characters and not fewer,
  • a CHECK constraint on the column branches.branch_id to guarantee that it's four characters and not fewer, and
  • a foreign key constraint on branch_id, referencing the table branches. Think about whether this should cascade updates and deletes.

The behavioral guarantees that the foreign key constraint gives you are important. They guarantee that whatever value you use for branch_id in your new table will exist in the table of branches, and that if a used branch_id ever changes, that change will also be recorded in your new table. (If you cascade updates.) And you don't get these guarantees if you bundle the two values into a single column.

the last 4 characters will reference the branch table to use the branch id.

You can copy the branch id that way, but you can't reference it. Reference is a technical term in database design; it has to do with foreign key references.

Upvotes: 1

Related Questions