Something like this: declare @sql nvarchar(max) = ( select concat(' insert into drugs(',STRING_AGG(cast(name as nvarchar(max)), ', ') within group (order by column_id),') select ',STRING_AGG(cast(name as nvarchar(max)), ', ') within group (order by column_id),' from drugs') from sys.columns c where c.object_id = object_id('drugs') and c.is_identity = 0 ) print(@sql) exec (@sql)

Why do we actually need a unique key when we have a primary key? Short answer -- You don't. Long answer: In MySQL, the PRIMARY KEY is a UNIQUE key is an INDEX. There is only one PRIMARY KEY; its main function is to uniquely identify each row. A UNIQUE key is allowed to contain a column that is NULLable. UNIQUE is a uniqueness constraint and an Index. Any flavor of an index may include multiple columns; the order of the columns in the definition matters. (The order of conditions in a WHERE clause does not matter.) A FOREIGN KEY is a CONSTRAINT and it implicitly creates an INDEX if there is not already one that works for the constraint. Each of these pairs is redundant; Drop the second: PRIMARY KEY (a) UNIQUE (a) INDEX(c,d) INDEX(c) UNIQUE(e) UNIQUE(e,f) UNIQUE(h) UNIQUE(g,h) -- or consider making changing to INDEX(g,h) If you build a table without a PRIMARY KEY but with a UNIQUE key, consider changing "unique" to "primary key". It is very rare for a table to need 3 UNIQUE keys (including the PK); rethink the schema. Some programmers always have an AUTO_INCREMENT (usually named id) as the PRIMARY KEY. But this is not always necessary, and it sometimes interferes with performance. Caveat: Some of the above statements may not apply to RDBMSs other than MySQL and MariaDB. Caveat: Index-prefixing, Partitioning, and a few other obscure things are not covered above. Two common cases for a PK + a UNIQUE: Lookup table (for 'normalizing'): The table has an id (auto_increment, PK) and a string (Unique). Many-to-mapping table: Two columns, each being an id into some other table. The PK would be the pair of columns in some order; the UNIQUE would be the pair in the other order. (Technically, a plain INDEX suffices for that second index.)

You are essentially asking if an entity can have relationships with more than one other entity, and the answer is obviously yes. Foreign keys are just physical manifestations of those relationships in SQL databases. Take the Customer entity as an example: clearly it has a relationship to the Account entity, but also to Credit Report, Customer Service Call, and whatever else you can correlate to Customer. All these will require foreign keys to enforce referential integrity.

It's not common for the primary key to match between two different tables (though there's some cases for it). Instead one table should have a second field that is the foreign key reference to the primary key of the other table, to relate the two tables together. Or sometimes both tables will relate by foreign key fields in both tables. Without a bit of more detail, I would guess your use case falls into the latter scenario. I would envision you have a Contracts table whose primary key field would be ContractId, Budgets table whose primary key field is BudgetId, and an Invoices table whose primary key field is InvoiceId. Both the Budgets and Invoices table would have a foreign key referencing field called ContractId as well which would reference the primary key ContractId field from the Contracts table. In the above scenario, this would relate all Invoices you receive for a given Contract to the correlating Budget of that contract. This assumes that your Invoices and Budgets aren't one-to-one with each other per se (e.g. a Contract can have one big Budget but is broken out into multiple Invoices). This is usually the common way budgeting and invoicing works in practice. But if your use case truly has a one-to-one relationship between Budgets and Invoices then all you need to do is have a foreign key referencing field called BudgetId in your Invoices table, which references the BudgetId field from your Budgets table. And you would set the Invoices.BudgetId to the correct value when the Invoice record is created.

So you have one table with a composite Primary Key, each element of which references the Primary Key of another table. Without specifics, let's take a simplistic example of a Student taking a Class taught by a specific Teacher. Each Entity (Student, Class, Teacher) can exist on its own (i.e. has its own table) and the association of the three is a student actually taking a class (OK, I'm ignoring dates in all this!). create tableA ( id ... primary key ( id ) ) ; create tableB ( id ... primary key ( id ) ) ; create tableC ( id ... primary key ( id ) ) ; create tableD ( a_id ... , b_id ... , c_id ... , foreign key ( a_id ) references tableA ( id ) , foreign key ( b_id ) references tableB ( id ) , foreign key ( c_id ) references tableC ( id ) );

I agree with Charlieface in regards to being able to use the same Primary Key field in both tables, since this is a one-to-one relationship. But I would also like to mention, if you had a need to create a separate Primary Key field for your Profile table, and you used a secondary field as the Foreign Key reference to your Person table, that doesn't necessarily mean it'll "scan the whole table to get a matching" when you JOIN by that Foreign Key field. It could (and probably would) use an index seek operation actually, and be just as performant (so long as the secondary field has a nonclustered index on it.). This is because SQL Server doesn't care if you're using the clustered indexed fields or a nonclustered indexed set of fields to JOIN by. Rather it determines which type of operation to use based on the https://en.m.wikipedia.org/wiki/Cardinality_(SQL_statements) of the values for the fields in your JOIN's ON clause. In this context cardinality refers to the number of rows your predicate returns relative to the total number of rows in their respective tables. The cardinality will be the same in your case whether you use the Primary Key field of your Profile table as the same field as your Person table or if you used a second field as such instead.

No, if the actions on the server side are not validated, the failure is there, exposing a primary key can facilitate, but it is not a faultAbout this facility... As you are talking about primary key, I will consider an application using SQL, where ID is generated sequentially, i.e. in one or two attempts you easily find a bank line. Having this exposed column makes it easy to find a specific line. But if there is no validation you probably get the list of all rows of all bank tables (or at least most)

A primary key is the data you need to control access to the tables lines. It must be unique and therefore cannot have null value (theoretically it could have a null key, but it doesn't make much sense). It gives the identity to the line, that's why you can trust it will always be used for direct access to the desired line. A sequential numeric identifier is common for this. See more in https://pt.stackoverflow.com/q/277091/101 .A foreign key is used for relationship with another table. In most cases it will be the primary key of another table. Therefore it can be null (no relationship) or duplicate (more than one item in a table relates to the same item from another table).In general modeling depends on experience. And experience depends on learning ability. Experience at all is the time that the person does that or how many times he has done. This would even be quantitative experience, which has no value at all, the experience that is worth is qualitative, the one you do once, someone or the consequences show you with the time that was wrong and should do better. More and more we see people choosing, for all their lives, just follow what others do, see superficially how they do, there the person never becomes experienced. Even to acquire experience it is necessary to acquire experience. You have to break the vicious circle to start evolving.The primary key with this basic knowledge (including link above) already gives to understand the need of it and in most cases the choice is simple and obvious, it is a sequential numerical substitute key.The foreigner is more complicated, not whenever he is novice clearly sees what he needs to relate a data to another, but also it is not so difficult. Think of it as a word you need to know to look for in a dictionary. The other related table is your dictionary for this case.

At first, as rLinhares spoke, it is not a PK, so you should send it to the bank.a simple sub-query solves the problem:insert into pedidos (id_pedido,id_empresa) values ((select coalesce(max(id_pedido),0) + 1 from pedidos where id_empresa = 1 ), 1) returning id_pedido; The coalesce, serves for the first request, which is when there is none, it must return 0.Notice that the value of the id_company is used twice.Fleeing the question a little bit, and getting ahead, every request must have items, in that case I used the returning which will be the FK in the item table (should be a FK composed with company, order, item) and to maintain integrity, this all should be within a transaction.

If I understood right you want to know how and if it is possible to relate two columns of a table with another column of another table, not allowing the deletion of records already populated between these "relations".In your attempt you are declaring foreign key in the same instruction. But you must declare your new foreign key as follows:Example: ALTER TABLE Luta ADD CONSTRAINT id_fk_fighter1 FOREIGN KEY (lutador1) REFERENCES lutador (id), ADD CONSTRAINT id_fk_fighter2 FOREIGN KEY (lutador2) REFERENCES lutador (id); With this the "fight" that in your case is the Jogo will have references to fighter 1 and fighter 2.All "fighters" who have a marked fight cannot be excluded.

Run this query to remove the desired column:ALTER TABLE `tabela` DROP `coluna`; I see it's beginner in _, but is it really necessary to remove the primary key from your table? Review your case well, it is necessary to differentiate the records, thus having something that makes them really unique, extremely important and necessary to UPDATES and DELETES, each case is a case, but before removing it, see if it really is necessary as I have already said.And if you just want to make one SELECT so that this column does not appear, it is simple, just send one: SELECT nome, profissao, nascimento, sexo, peso, altura, nacionalidade FROM tabela WHERE...Lastly, to just remove the primary key function you should run these two querys://Você deve primeiro remover a propriedade de AUTO_INCREMENT e depois //remover a chave primária ALTER TABLE tabela MODIFY coluna INT NOT NULL; ALTER TABLE tabela DROP PRIMARY KEY; In order for her to be a primary key, just run:ALTER TABLE tabela MODIFY coluna INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

1) The only restriction for foreign keys is that they should point to a single key.Note that every PK, by definition, is a unique key.Think of the following: the foreign key indicates who the father record is, from which the child record depends and without it would be orphaned. Besides the child registration cannot have two parents (point!)2) A key is a field that identifies the registry, as a name of a person. A unique key is a field that uniquely identifies a registry, such as a CPF.(3) Any key can be composed by more than one column. There will depend much on schema but in general a key is something that identifies the registry.4) A field that will be used as a key should never be changeable. That is: it should never be a field whose value can be changed, for example your CPF, imagine that your CPF could change all the time, how could you be identified for that?(5) A quantity field is not a good candidate to be used as a key, it is a changeable field (relatively) and does not identify the registry. Actually it's just an attribute so don't use it as a key. If you do many research by filtering through this field it may be interesting to index it to improve performance but not elevate it to a key.Edit@Maniero Implementation constraint will depend on the SGBD, examples: http://technet.microsoft.com/pt-br/library/ms175464(v=sql.105).aspx and http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html .and the reference on the concept of FK in http://en.wikipedia.org/wiki/Foreign_key But even if the SGBD allows this is a breach of relational integrity. Why? Well if you change the id of the parent registration, without changing the child records they will become orphans. If a child can have "n parents" then it is better to reverse the hierarchy and make the record "ex-father" reference the record "ex-son". If the relationship is N to M would then be better to create a relationship table with FKs for "fathers" and "children".It may even be that there is some need somewhere to create a FK for a key not unique but the few times I saw such a thing were in serious modeling errors.

Try this:CREATE TABLE CONTACORRENTE ( Agencia INT NOT NULL, Numero INT NOT NULL, CONSTRAINT [PK_CONTACORRENTE] PRIMARY KEY ( Agencia,Numero ) )

To reference a composite primary key would be like this: FOREIGN KEY (ax,bx,cx) REFERENCES tabela_origem(a,b,c)

Short version - No, it's not recommended.Long version - I would understand your question as follows:The benefit of owning a database without process fireworks versus the cost of maintaining a natural primary key results positively?We will then go to a quick enumeration of positive points of each option:Natural Primary KeyReal representation of data scopeDo not saturate the scope with an artificial identifier In this implementation, your data model is as close as possible to the universe represented by the scope. Crossing external database data is facilitated by the fact that your data is used as a natural identifier (e.g. CPF or identity card.)IDs, GUIDs and derivativesDetaches the process of the data representedProviding its own mechanisms of bonding and guaranteeing oneness Prevents blocking by data repetitionAllows the presence of partial dataSafety by occlusion of natural dataImmutable data prevents fragmentation of indicesDecreasing import errors and data mergeA data model is designed to reproduce the behavior of the data present in the universe defined by the scope. However, being an abstract model, he must provide his own mechanisms to allow this representation.If you use a natural model, you are delegating responsibility on oneness. If this uniqueness is violated, your model will be negatively impacted. For example, two people with the same CPF, or a CPF that was erroneously typed and that by chance is already present in the database.In situations where the data are incomplete and the natural identifier fields are not yet present, a model of these would still allow the creation of records containing a partial representation.

In some situations, yes.The PRIMARY KEY of a rowid table (if there is one) is usually not the true primary key for the table, in the sense that it is not the unique key used by the underlying B-tree storage engine. The exception to this rule is when the rowid table declares an INTEGER PRIMARY KEY. In the exception, the INTEGER PRIMARY KEY becomes an alias for the rowid. https://www.sqlite.org/rowidtable.html .The PRIMARY key of a rowid table (if there is one) is usually not the true primary key to the table, insofar as it is not the unique key used by the underlying B-tree storage mechanism. The exception to this rule is when the rowid table declares an INTEGER PRIMARY KEY. In the exception, INTEGER PRIMARY KEY becomes an alias for the rowid.So your modeling can make it true or not. What is recommended to do so most of the time. https://sqlite.org/autoinc.html .

You want to know if you better use one https://en.wikipedia.org/wiki/Natural_key or https://en.wikipedia.org/wiki/Surrogate_key (surrogate).Just leave the tuition field as the primary key.That's a natural key. Do you control this number? Will he never be changed? Isn't there the slightest chance of the same object in the database one day need to have another unique identification? Is she short enough? Doesn't she give any room for mistakes? Will she always be unique? Does she really identify what the object is?Most of the data in the world do not meet all these criteria, so opt for a substitute key. If you answer all this, you can use it smoothly. But be careful, a lot of people think you're picking up, and one day you find out you're not answering, it was circumstantial.Leave the field plate with an index unique and create an id field with auto increment to be the primary key.That's a substitute key. It is interesting in many cases to keep an internal control in the system independent of the number the user reads. I can't say it's the best in your case. If you can do well the previous item then you don't have to do this, you're just wasting space and performance.Leave the enrollment field as the primary key and create a field id with index auto incrementTo be auxiliary.I don't think it's any advantage of that.Unlike what many people think there is always a primary key in every table. Even when the database does not require you to create one (it creates without you knowing). Also unlike popular belief, the primary key is always an index. Data is always classified in the database by the primary key. Secondary indices usually refer to the primary index.

The key in yellow No to know if relations were created correctlyand to know if it was actually created the relationships just click on Relation view: and the two relations must appear (fictitious example) as this example: so it is correct, being they as primary keys of this tables with relationships in other tables.

What is the advantage of using hexadecimal values instead of Whole?Hexadecimal is a slightly shorter representation. For example, the following two values are identical:999999999 DEX 3B9AC9FF HEX You also have a better view of bit distribution, since each hexadecimal position equals 4 bits: 3 B 9 A C 9 F F HEX 0011 1011 1001 1010 1100 1001 1111 1111 BIN What type of column is appropriate in this case: string (varchar), binary, othersUsually numerical, but it can be binary. Hexa is just a representation; it is the case of the GUID type, that SQL Server stores as a varbinary(16).For an example of this type of conversion, see this answer in Stack Overflow: https://stackoverflow.com/questions/10078209/sql-server-2008-convert-from-guid-to-bigint-and-back

As stated in the comments, the correct in your situation is to use a unique index.Create a UNIQUE INDEX in postgresql:CREATE UNIQUE INDEX tabela_unique_idx ON public.tabela USING btree ("col1", "col2", "col3"); Reference: https://www.postgresql.org/docs/9.4/static/indexes-unique.html